Fill Blank Cells With Value Above or Below the Cell or Zero

Hi Friends,

I hope most of us has faced a typical problem in Microsoft Excel regarding filling the blank cells in any report. Although filling up empty cells with the value above or below the current cell makes Excel reports clumsy but to fill the blank cells with the value above or below is also very useful in matching the data column wise or row wise (using CONCAT and VLOOKUP) and lastly we fill it by Copy & Paste, a tedious and monotonous job.

Today, one of my friends requested me to show the trick to fill the blank cells by the above value or value below the cell.

Today I will show you few tricks to fill the blank cells by the value above the cell or below the cell step by step in any table in Excel and in PivotTables.

To fill the blank cells, we need to first detect and select the empty cells in Excel table then we will fill these cells by the value.

Let suppose we have a table (for this example I have created a small table) as shown below.

How to Fill Blank cells in Excel

Now the task is to fill the blank cells in the column ‘D’ by the value above a blank cell and the second task is to fill the ‘F’ column by the value below the blank cell.

To complete the above task, first we must find and select the blank cell in this table.

The steps to select the blank cells in Excel Table.

1. Select the entire table or one particular column to be filled.

2. Press Ctrl+G or F5 (Function key) which will open a new window 'Go To' as shown below.

Go To Window to Select Blank Cells

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the below picture.

Go To Window to Select Blank Cells

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

Blank Cells are Selected

7. Now the trick is, type ‘=’ (do not select any cell, it is already selected) and press ‘UP’ arrow to select the above cell with value and press Ctrl+Enter.

8. It’s done.

Blank Cells are Filled in Excel

But did you noticed that the second task is not accomplished!!! 

So, what to do?

For the first problem, to fill the blank cells by the value above a cell we should select the ‘D’ column only then apply the above steps it will be done.


For the second task, select the column ‘F’ and press ENTER button unless the cursor moves above the cell which is filled by data and follow the same steps.

Blank Cells are filled using Cell Reference

Note : All the cells we filled are in reference to another cell, therefore, please don’t forget to use paste special to remove the formula.

How to fill blank cells by zero or any other number or text?

To do this follow the below steps.

1. Select the column or row.

2. Press Ctrl+G or F5 (Function key) which will open a new window Go To as shown below.

3. Click on the ‘Special…’ button shown in the above image by red rectangle.

4. Another new window will appear, ‘Go To Special’, select the Blank radio button as show in the above picture.

5. Click OK.

6. You can notice that the blank cells are now selected as shown in the below image.

7. Now Type your TEXT or NUMBER or ZERO and press Ctrl+Enter.

8. And you are done.

Blank Cells Filled with Text or Numbers or Zero

Isn't it awesome!!!

How to fill Blank cells in Pivot Table?

Now let us learn how to fill blank cells in a PivotTable. I have made a Pivot Table from the above table as shown in the below picture. You can notice that there are blanks below the Product Category and Employee Name column, we need to fill these blank cells by the same value in the above cell.

How to Fill Pivot Table Blank cells

Very simple steps are available in Pivot Table, please follow the below steps.

1. Select any blank cell in PivotTable where you want the items to be repeated.

2. Go to Analyze

3. In the Active Field menu, you will see an option, Field Settings.

4. Click this button, show below.

PivotTable Field Settings Option

5. One new window ‘Field Settings’ will appear

6. Select the second Tab, Layout & Print.

7. Tick the Checkbox, Repeat the item labels.

8. Click OK, its’ done.

Fill Blank Cells by repeating data in Pivot Table

Blank cells filled in Pivot Table

I hope this article is informative & you enjoyed this article.
Thank you for reading…