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!

More Easy Tricks in Excel

How to find cells that contain a formula in Excel
SUM across multiple sheets in Excel
Make SUMIFS and COUNTIFS in Excel more flexible with a Wildcard
The easiest way to reduce file size in Excel: Excel Binary Workbook
An easier way to calculate compound interest in Excel

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

Leave a Reply

Your email address will not be published. Required fields are marked *


nine + = 11