How to fill empty cells with 0, with value above/below in Excel

How to select empty cells in Excel worksheets

Before filling in blanks in Excel, you need to select them. If you have a large table with dozens of blank blocks scattered throughout the table, it will take you ages to do it manually. Here is a quick trick for selecting empty cells.

1. Pick the columns or rows where you want to fill in blanks.
   
2. Press Ctrl + G or F5 to display the Go To dialog box.
3. Click on the Special button.
   

   Note. If you happen to forget the keyboard shortcuts, go to the Editing group on the HOME tab and choose the Go To Special command from the Find & Select drop-down menu. The same dialog window will appear on the screen.

   

   The Go To Special command allows you to select certain types of cells such as ones containing formulas, comments, constants, blanks and so on.

4. Select the Blanks radio button and click OK.
   

Now only the empty cells from the selected range are highlighted and ready for the next step.

Excel formula to fill in blank cells with value above / below

After you select the empty cells in your table, you can fill them with the value from the cell above or below or insert specific content.

If you're going to fill blanks with the value from the first populated cell above or below, you need to enter a very simple formula into one of the empty cells. Then just copy it across all other blank cells. Go ahead and read below how to do it.

1. Leave all the unfilled cells selected.
2. Press F2 or just place the cursor in the Formula bar to start entering the formula in the active cell.

   As you can see in the screenshot above, the active cell is C4.

3. Enter the equal sign (=).
4. Point to the cell above or below with the up or down arrow key or just click on it.
   

   The formula (=C3) shows that cell C4 will get the value from cell C3.

5. Press Ctrl + Enter to copy the formula to all the selected cells.
   

Here you are! Now each selected cell has a reference to the cell over it.

Use the Fill Blank Cells add-in by Ablebits

If you don't want to deal with formulas every time you fill in blanks with cell above or below, you can use a very helpful add-in for Excel created by Ablebits developers. The Fill Blank Cells utility automatically copies the value from the first populated cell downwards or upwards. Keep on reading and find out how it works.

1. Download the add-in and install it on your computer.

   After the installation the new Ablebits Utilities tab appears in your Excel.

2. Select the range in your table where you need to fill empty cells.
3. Click the Fill Blank Cells icon on the Ablebits Utilities tab.
   

   The add-in window displays on the screen with all the selected columns checked.
   

4. Uncheck the columns that don't have empty cells.
5. Select the action from the drop-down list in the bottom-right corner of the window.

   If you want to fill the blanks with the value from the cell above, choose the Fill cells downwards option. If you want to copy the content from the cell below, then select Fill cells upwards.

6. Press Fill.
   

Done! :)

Besides filling empty cells, this tool will also split merged cells if there are any in your worksheet and indicate table headers.

Check it out! Download the fully-functional trial version of the Fill Blank Cells add-in and see how it can save you much time and effort.

Fill empty cells with 0 or another specific value

What if you need to fill all the blanks in your table with zero, or any other number or a specific text? Here are two ways to solve this problem.