Fill blank cells with zeros or values above/below in Excel

In large Excel tables, it’s common to leave repeated values blank for a cleaner visual layout — for example, omitting duplicate names in consecutive rows or leaving empty cells instead of zeros in numeric columns.

While this might look neat, such data is hard to analyze. If you need to create a PivotTable, sort, or filter the data, you’ll first have to fill in all the empty cells. Let’s look at a few practical ways to do it.

Fill empty cells with the value above using formulas

How can you fill blank cells in Excel with the value from the non-empty cell directly above them?

With standard Excel features, it takes several steps:

1. Apply a filter to the column.
2. In the first empty cell, enter a formula referencing the cell above (e.g., in A3: =A2).
3. Copy the cell with the formula.
4. Filter the column to show only blank cells.
5. Select them and paste the formula (Ctrl + V).
6. Remove the filter.
7. Select the entire column and replace formulas with values.

However, this approach has clear drawbacks:

• It’s a multi-step, repetitive process that wastes time.
• If the column has different formatting, copying formulas may overwrite formatting and require restoration.
• You can’t paste formulas into only filtered blank cells using built-in Excel tools.

Fill empty cells in 2 clicks

The !SEMTools add-in can handle this instantly without changing the formatting of blank cells. The tool is located under “Modify Cells” in the “CHANGE” group.

Fill empty cells with the value below

You can also fill empty cells with the value from below. This is less common in practice but just as quick with !SEMTools.

Fill empty cells with zeros

Replacing empty cells with zeros is straightforward. In Excel, you can use Find & Replace (Ctrl + H), search for blanks, and replace them with 0.

With !SEMTools, it’s even faster — no dialogs, no manual steps:

This post is also available in RU.