How to handle tables with empty cells in Excel

Sometimes you receive an Excel report or an export from another system with a lot of empty cells in a category column:

If you want to analyze the data, either with a Pivot table or with formulas such as SUMIFS and VLOOKUP, you need to populate all the empty cells in the category (dimension) columns, in this example column A.

For example, if you want the Total Sales for Northeast, the formula =SUMIFS(E:E,A:A,”Northeast”) will only work if you have the right region in every cell in column A .

This is how you do it:

First, select the range you want to amend. If the dataset is small, you can select it manually with the mouse or with Shift + Arrow, but if you have a large dataset, it’s easier to use the Name Box:

Just type the range in the Name Box in the format FirstCell:LastCell, or in this example A2:A33. The whole range will be selected.

Open the Go To Special dialog box. You find it on the Home tab, under Find & Select, or you can use the shortcut Ctrl + G and click on Special. Choose “Blanks” to select all the blank cells in the selected range.

Now that all the blank cells in the range have been selected, all you have to do is to write a single formula: Type an equal sign and press the Up Arrow key once.

In this example, A3 is the first blank cell in the range, and the formula that you created with the arrow is =A2. Press Ctrl + Enter to populate all the blank cells with this formula. Since this is a relative reference, the formula in A4 will be =A3, the formula in A5 will be =A4, and so on.