Some worksheets have blank cells in a column, instead of repeating the headings in every row. That white space makes it easier to read the report, but will cause problems if you need to sort or filter the data.
Here’s how you can fill in those blank cells, to avoid messing up your data. Then, use an easy conditional formatting trick, to hide the duplicate headings.
Fill in Blank Cells
In this screen shot there’s a list with blank cells at the left.
On the right, those blank cells were filled, using the closest heading from above each blank cell.
- With the list on the left, if you filter for Central region, only the Binder item would be in the filter results.
- With the list on the right, if you filter for Central region, three rows would be in the filter results – Binder, Pen, Pen Set.
Fill in the Blank Cells
This short video shows the steps to fill the blank cells manually.
There are written steps below the video, and more details, on my Contextures website,
Fill in the Blanks
Here are the written steps to fill in the blank cells in a worksheet column.
First, select the cells that you want to fill in:
- Select the column that contains the blank cells.
- On the Ribbon’s Home tab, click Find & Select, then click Go To Special
- Select Blanks, then click OK
Next, create a simple formula:
- Type an equal sign, then tap the Up arrow key, to select the cell above
- That creates a relative reference to the cell
- Press Ctrl+Enter, to enter that formula in all the selected cells.
The formula has a relative reference, so it adjusts in each cell, to refer to the cell directly above it.
Finally, change the formulas to values:
- Next, select the entire column, and copy it
- Right-click on the selected column, and click the Paste Values command
Use Macros to Fill Blank Cells
If you frequently have to fill blank cells, you might like to use a macro, instead of doing all the steps manually.
There are four macros on the Fill Blank Cells page, on my Contextures site. There are slight differences among the macros, so read the notes to see which one might work best for you.
You can copy the macro code from that page, or download the sample file that has test data, and all four of the Fill Blank Cells macros.
Here’s the code for the first Fill Blank Cells macro, written by Dave Peterson, way back the 2004!
And here’s a quick summary of how this macro fills the blank cells:
- uses a formula to fill the cells
- pastes the results as values.
- uses the .SpecialCells(xlCellTypeLastCell) method to find the last row
- fills blanks from row 2 to the last row
'====================
Sub FillColBlanks() 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above 'https://www.contextures.com/xldataentry02.html Dim wks As Worksheet Dim rng As Range Dim LastRow As Long Dim col As Long Set wks = ActiveSheet With wks col = activecell.column 'or 'col = .range("b1").column Set rng = .UsedRange 'try to reset the lastcell LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set rng = Nothing On Error Resume Next Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ .Cells.SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rng Is Nothing Then MsgBox "No blanks found" Exit Sub Else rng.FormulaR1C1 = "=R[-1]C" End If 'replace formulas with values With .Cells(1, col).EntireColumn .Value = .Value End With End With End Sub
'====================
Hide Duplicate Headings
After you fill in the blank cells with values from above, you can use a conditional formatting trick, to hide the duplicate headings.
This will make the list easier to read, just like it was with the blank cells.
This video shows you the steps, and the written instructions are on the Conditional Formatting Examples page.
_________________________
How to Fill Blank Cells in Excel With Value from Above
_________________________