Instantly fill blank cells in Excel with the nearest values

How to fill blank cells in Excel with XLTools add-in

After pasting a pivot table or importing a listing with multilevel subheadings to Excel, you often end up with blank cells in the table. These gaps complicate sorting, filtering or calculations in a worksheet. Filling missing data manually, using formulas or a macro takes time and may lead to mistakes.

The Fill Blank Cells feature allows you to populate empty cells and instantly complete a table:

  • Quickly fill blank cells in a column, row, range or an entire table
  • Fill blanks with nearest values above, below, to the left or to right
  • Automatically unmerge and fill cells if necessary

Before you begin, add Fill Blank Cells to Excel

Fill Blank Cells is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

How to fill blank cells down

  1. Select the range with empty cells you want to fill.
  2. Click the Fill Blanks Cells button on XLTools ribbon Select Fill cells down from the drop-down list Done, all the gaps are populated with values.

This operation will fill empty cells down with the nearest values from above. In other words, it will copy values in the cells above and paste into the blank cells below. This filling pattern is the most common.

How to fill blank cells down with the nearest values

How to fill blank cells up

  1. Select the range with empty cells you want to fill.
  2. Click the Fill Blanks Cells button on XLTools ribbon Select Fill cells up from the drop-down list Done, all the gaps are populated with values.

This operation will fill blank cells up with the nearest values from below. In other words, it will copy values in the cells below and paste into the blank cells above.

How to fill blank cells up with the nearest values

How to fill blank cells to the right

  1. Select the range with empty cells you want to fill.
  2. Click the Fill Blanks Cells button on XLTools ribbon Select Fill cells to the right from the drop-down list Done, all the gaps are populated with values.

This operation will fill blank cells to the right with the nearest values from the left. In other words, copy values in the cells on the left and paste into the blank cells to the right.

How to fill blank cells to the right with the nearest values

How to fill blank cells to the left

  1. Select the range with empty cells you want to fill.
  2. Click the Fill Blanks Cells button on XLTools ribbon Select Fill cells to the left from the drop-down list Done, all the gaps are populated with values.

This operation will fill blank cells to the right with the nearest values from the left. In other words, copy values in the cells on the left and paste into the blank cells to the right.

How to fill blank cells to the left with the nearest values

Set options for filling blank cells

If your table contains merged cells, you can fill blank cells and unmerge the table at the same time.

  1. Click the Fill Blank Cells button on XLTools ribbon Select Set options… from the drop-down list A dialogue box will open.

  2. Select the range with empty cells you want to fill.

    Tip: click on any cell in a table, and the entire table will be automatically selected.
  3. Choose the filling pattern: Down, Up, To the right, To the left.

  4. Check the Unmerge cells option.

    When this option is checked, all merged cells in the selected range are automatically split and filled with corresponding values.

    How to set options for filling blank cells in a table
  5. Click OK Done, all the gaps are populated with values, cells are unmerged.

    How to fill blank cells and unmerge cells in a table

If blank cells persist, and what is a truly blank cell

Fill Blank Cells feature accounts only for truly blank cells. If you run the operation, but the blank cells persist, most likely that the cells are not truly blank.

A truly blank (empty) cell is a cell that contains no entries at all. A cell may look empty, when in fact it’s not, for example:

  • The cell contains a space, a line break, an apostrophe, a non-printing character, or something else invisible to the eye
  • Cell’s contents are hidden
  • The cell contains a zero-length string: =""
  • The cell contains a formula returning an empty string: =IF(),"",""
  • The cell contains a reference to an empty cell: =A1 where A1 is empty

Excel treats these cells differently than truly blank cells. You can check your data using =ISBLANK function and fix inconsistencies where necessary.

Validate truly blank cells with ISBLANK function

How Fill Blank Cells processes data

Note: the operation cannot be undone, so we recommend you save or back up your spreadsheet before the operation.

The Fill Blank Cells feature copies the value from the cell nearest to the blank cells according to the selected pattern: down, up, to the right, to the left. It fills all the blanks until it comes to the next filled cell. Then it copies this next value and fills blanks further on.

The values from filled cells to the blank cells are copied exactly:

  • If the value is a number or text string, the blank cells will be populated with the same number or text string.
  • If the value is a formula, a function or a reference, the blank cells will be populated with the same exact formula, function, or reference. Cell references will not change.
  • If the filled cell is locked or hidden, the value will be copied into the blank cells anyway.

Note, the tool is not applicable directly to PivotTable, same as any other processing operations. You can fill blanks in your source data, or copy and paste the PivotTable – and only then apply the Fill Blank Cells feature.

Any questions or suggestions?
Pat CrawleyIt would be really useful if a range of numbers, ie. cheque numbers, could be populated by sequential numbers.
July 27, 2017 at 18:05
Maria BalobanovaHi Pat! Thank you, that's a great suggestion! If you have more - please share, we are filling up the pipeline for the next releases.
July 28, 2017 at 09:57
How can we help?