Instantly populate empty cells 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.

Fill Blanks add-in allows you to fill empty cells and instantly complete a table:

  • Quickly fill blank cells in a column, row, range or an entire table
  • Fill blanks with values above, below, on the left or right of the blank cells
  • Automatically unmerge 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.

– free trial gives you 14 days of full access to all features.

How to fill blank cells, rows and columns in a table

  1. Click theFill Blanksbutton on XLTools ribbon A dialogue box will appear.
  2. Select a table, a column, a row or a range with empty cells that you want to fill.
    Tip:click any single cell in a table, and the entire table will be automatically selected.
  3. Choose a filling pattern: downwards, upwards, rightwards or leftwards. More onfilling options.
  4. Check theUnmerge cellsoption, if necessary.

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

  5. Click OK Done. All the gaps in the table are populated with values.
Tip:Fill Blanks add-in remembers your previous settings, so you can easily repeat the operation on the next range.
Note:the add-in is not applicable directly to PivotTable. You can fill blanks in your source data, or copy and paste the PivotTable – and only then apply Fill Blanks.

Which values are used to fill blank cells

You can select one of the filling patterns to populate blank cells:

  • Filldownwards:

    Fill blank cells from the top downwards, i.e. copy values of the cells above into the blank cells below. It is the most frequently used option.

  • Fillupwards:

    Fill blank cells from the bottom upwards, i.e. copy values of the cells below into the blank cells above.

  • Fillrightwards:

    Fill blank cells from the left rightwards, i.e. copy values of the left hand cells into the blank cells to the right.

  • Fillleftwards:

    Fill blank cells from the right leftwards, i.e. copy values of the right hand cells into the blank cells to the left.

The add-in copies repeating values into the blank cells until it comes to the next filled cell, which values will be used to fill blank cells further on.

The Fill Blanks add-in copies the values from filled cells to the blank cells exactly:

  • If the value is a number or text string, Fill Blanks will populate blank cells with the same number/text string.
  • If the value is a formula, a function or a reference, Fill Blanks will insert the same exact formula/function/reference into the blank cells (references will not change).
  • If the filled cell is locked or hidden, Fill Blanks will copy the value of the locked/hidden cell into the blank cells anyway.
Note:if blank cells or an entire worksheet are protected, first you will need to unprotect the sheet (and enter a password, if necessary).

If blank cells persist, and what is a truly blank (empty) cell

Fill Blanks add-in accounts only for truly blank (empty) cells. If you run the add-in, but the gaps persist, it is 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, e.g.:

  • A cell looks blank – but it contains a space, a line break, an apostrophe, a non-printing character, something invisible to the eye.
  • A cell looks blank – but cells contents are actually hidden.
  • A cell looks blank – but it contains a zero-length string (="") or a reference to an empty cell (=A1where A1 is empty ), or a formula returning an empty string (=IF(),"","").

Excel treats these cells differently than truly blank cells. Before you run the Fill Blanks add-in, we recommend that you check your data using=ISBLANKfunction and fix inconsistencies where necessary.

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 22: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 13:57
How can we help?