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.


And that’s it! That’s how you replace empty cells with the correct values in an Excel table!

Popular posts from this blog

SUM across multiple sheets in Excel

Image
If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it. The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that. A better way to do it is to use this formula: =SUM(Jan:Dec!B2) (and copy down and across) With this formula, Excel looks at all the tabs from Jan through Dec and picks up the value in B2 in all of them. Note that you only need an exclamation mark after the second sheet reference. You have to be careful when you use this approach, though. If you insert or move another sheet between the ones you want to refer to in the formula, Excel assumes that you want to include that too. On the picture below you can see what might happen if you move a phone ...
Read more