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
Add "Fill Blanks" to Excel 2016, 2013, 2010, 2007
Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).
Learn your way around:
- How to fill blank cells, rows and columns in a table
- Which values are used to fill blank cells
- If blank cells remain, and what is a truly blank (empty) cell
Note: UNDO (undo the last operation) is not applicable. We recommend that you save backup copies of your files or use XLTools Version Control to keep track of changes.
How to fill blank cells, rows and columns in a table
- Click the 'Fill Blanks' button on XLTools ribbon > A dialogue box will appear.
Select a table, a column, a row or a range with empty cells that you want to fill.
Note: click any single cell in a table, and the entire table will be automatically selected.
- Choose a filling pattern: downwards, upwards, rightwards or leftwards. More on filling options.
Check the 'Unmerge cells' option, if necessary.
When this option is checked, all merged cells in the selected range are automatically split and filled with corresponding values.
- 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:
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.
Fill blank cells from the bottom upwards, i.e. copy values of the cells below into the blank cells above.
Fill blank cells from the left rightwards, i.e. copy values of the left hand cells into the blank cells to the right.
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 remain, 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 remain, 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 (=A1, where A1 is empty), or a formula returning an empty string (
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 ISBLANK function and fix inconsistencies where necessary.
Any questions or suggestions? Please leave your comment below.