How To Fill Empty Cells In Excel With Values Above

You have a spreadsheet with information missing because it was summarised for viewing as a report, but this format is no good for data analysis. You know you want to fill the empty cells with values, but how do you do it quickly?

Perhaps you copied data from a report somewhere, and some columns were summarised with blank spaces sorta like the spreadsheet below.

As you probably know already, the above layout will not work for any kind of data mining, because columns A and B ("Region" and "Sales Channel") are missing values.

You may know that rows two through five refer to the "South East" region, however from a data (and database perspective) they are just empty, devoid of region information.

So given that this data layout does not work if you wish to use it as part of a vlookup, or if you want to use it within an "Excel Autofilter" or "Excel Advanced Filter".

You will probably set about copying or dragging each of the ranges down to fill all empty cells on each of the columns.

There is however a much cooler way to do this, one that will also give you massive time savings on very large data sets.

The Solution

Highlight The Range To Adjust

First select any part of the range, in this example you could click cell C5 then hold down the "Ctrl" and "*" (asterisk) key. This will select the "current region" around the cursor and the selection should now look like this.

Now press your "F5" key this will result in the "Go To" dialog box appearing.

Then click the "Special…" button on the bottom of the "Go To" dialog box.

This will call up the "Go To Special" dialog box pictured to the right.

On the first column of the "Go To Special" dialog box click the "Blanks" Radio Button.

Then click “OK”.

This will result in only the blank excel cells being highlighted, see below.

Now Fill Empty Cells In Excel Using Equals Up Arrow

Without moving your cursor (you don’t want to lose your selection), type “=” then move the formula cursor up one cell, and hold down the "ctrl" key, and press return at the same time.

So for example if the current cell is "B3", as in the image above, then "B3" will contain the formula "=B2".

In fact as a result of the previous action, every blank highlighted cell will contain a formula referencing the cell above it.

Notice below that every highlighted cell which was previously empty, now contains the value of the cell above it, effectively filling out the blanks.

Convert The Formuli To Values

All that now remains is to convert the formulae that are in the highlighted cells to values.

Simply select column A and column B

Then copy (ctrl+c on the pc).

Then choose Paste Values as in the image below.

Your data range will now be completely filled out with the formulae converted to values.

This protects against your cell values accidently being changed, resulting in incorrect information.

You now have a data range that is more professional and can form the input to other sheets in your spreadsheet, a data range that works perfectly with autofilter, advanced filter and pivot tables.

This methodology can also be implemented elegantly in visual basic for applications (vba).