How to fill empty cells with zeros or values ​​from cells above (below) in Excel

From this article, you will learn how to select all empty cells at once on an Excel sheet and fill them with values ​​from the cells above (below), zeros or any other values.

To fill or not to fill? – This question often arises regarding empty cells in Excel spreadsheets. On the one hand, a table looks neater and more readable when you don’t clutter it with repeated values. On the other hand, empty cells in Excel can lead to problems while sorting, filtering data, or creating a pivot table. In this case, you will have to fill in all the empty cells. There are different ways to solve this problem. I will show you some quick ways to fill blank cells with different values ​​in Excel 2010 and 2013.

So, my answer will be – fill! Let’s see how we can do this.

• How to highlight blank cells in an excel sheet
• Excel formula to fill blank cells with values ​​from cells above (below)
• Fill empty cells with zeros or another given value

How to highlight blank cells in an excel sheet

Before filling empty cells in Excel, they need to be selected. If you have a large table with dozens of empty cells scattered throughout the table, then you will waste forever if you do it manually. Here is a quick way to highlight empty cells:

1. Select the columns or rows where you want to fill in the gaps.
2. Press Ctrl + G or F5to display the dialog box Go To (Transition).
3. Нажмите кнопку Special (Highlight).

Note: If you suddenly forgot the keyboard shortcut, open the tab Home (Home) and in the section Editing (Editing) from the drop-down menu Find & Select (Find and highlight) select command Go To Special (Select a group of cells). The same dialog box will appear on the screen.

3. Team Go To Special (Select a group of cells) allows you to select cells of a certain type, for example, cells containing formulas, notes, constants, empty cells, and so on.
4. Select an option blanks (Empty cells) and click OK.Now only empty cells are selected in the selected range and you are ready for the next step.

Formula to fill empty cells with values ​​from cells above (below)

After you have selected empty cells in the table, you can fill them with values ​​from the cells above, below, or insert a specific value.

If you are going to fill in the gaps with values ​​from the nearest non-blank cell above or below, you will need to enter a very simple formula into one of the empty cells. Then just copy it to all empty cells. How to do it – read on.

1. Select all empty cells.
2. Press F2 or simply 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 cell is active C4.
3. Type an equal sign (=).
4. Specify a cell that is above or below by clicking the up or down arrow, or simply by clicking on it.Formula (=C3) shows that in the cell C4 value will appear from the cell C3.
5. Press Ctrl + Enterto copy the formula to all selected cells.

Excellent! Now each selected cell contains a link to the cell above it.

Note: Don’t forget that all cells that were empty now contain formulas. If you want to keep the order in the table, then it is better to replace these formulas with values. Otherwise, you can get confused when sorting or when entering new data.

Fill empty cells with zeros or another given value

What if you need to fill all empty cells in your table with zeros or other numeric or text values? The following shows two ways to solve this problem.

1 method

1. Select all empty cells
2. Press F2to enter a value in the active cell.
3. Enter the desired number or text.
4. Press Ctrl + Enter.

In a few seconds, you filled all the empty cells with the desired value.

2 method

1. Select the range containing empty cells.
2. Press Ctrl + Hto display the dialog box Find & Replace (Find and replace).
3. Click the tab Replace (Replace).
4. Leave a field Find what (Find) blank and enter the desired value in the field Replace with (Replaced by).
5. Нажмите кнопку Replace All (Replace all).

Whichever method you choose, processing the entire Excel spreadsheet will take no more than a minute!

Now you know the tricks on how to fill empty cells with different values ​​in Excel 2013. I’m sure it will not be difficult for you to do this either with a simple formula or with the tool Find & Replace (Find and replace).