How to Fill Blank Cells in Excel With Value from Above

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.

_________________________