Instantly fill blank cells in Excel with the nearest values

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:

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.

Download XLTools for Excel

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.

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.

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.

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.

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.

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.
2. Choose the filling pattern: Down, Up, To the right, To the left.
3. 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.

   

   
4. Click OK Done, all the gaps are populated with values, cells are unmerged.

   

   

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

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.