Learn your way around:
Fill Blank Cells
Fill Blank Cells
Instantly populate empty cells with the nearest values
- 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
How to fill blank cells, rows and columns in a table
- Click theFill Blanksbutton 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.Tip: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 onfilling options.
- 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
- 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
- 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(),"","").