Fill Blank Cells in Excel Column

Some Microsoft Excel worksheets contain cells that have been left blank, in order to make the headings and subheadings easier to read. However, if you want to sort or filter the list, you need to fill in the blanks, by copying the value from the first filled cell above the blank. Follow the instructions on this page to fill the blank cells, manually, or with an Excel macro.

Note: To fill blank cells in Pivot Table headings, see how to Repeat Item Labels in Excel 2010 and later.

Fill Blank Cells From Above

In the screenshot below, cells that have been left blank, so the report headings and subheadings are easier to read. If you need to sort or filter this data, you need to fill the blanks cells, by copying the value from the first filled cell above the blank

To fill the blank cells manually, you will select all the blanks, enter a simple formula in each cell, then convert the formulas to values. Watch the video below, to see the steps, and the written steps are below the video.

Video: Fill Blank Cells Manually In this video, watch the steps to manually select and fill blank cells, with the value from the cell above. Then, use a mouse shortcut to change the formulas to values, so you can safely sort and filter the data. Note: There is a video transcript at the end of the page.

How to Fill Blank Cells Manually

To fill the blank cells manually, there are 3 main steps:

-- 1) Select Empty Cells

-- 2) Create Simple Formula

-- 3) Change Formulas to Values

1) Select Empty Cells

The first main step is to select all the blank cells that you want to fill.

To select the empty cells with Excel's built in Go To Special feature, follow these steps:

• Select columns A and B, that contain the blank cells.
• Use the Ribbon commands:
  • On the Excel Ribbon's Home tab, in the Editing group, click Find & Select
  • Click the Go To Special command
• OR, use keyboard shortcuts:
  • Press Ctrl + G to open the Go To window
  • Press Alt + S to open the Go To Special dialog gox

Go To Special Dialog Box

• In the Go To Special dialog box, click Blanks
  • OR, press Alt + K (the underlined letter in the Blanks option)
• Then click OK, to close the Go To Special dialog box

Blank Cells Selected

After you close the Go To Special dialog box:

• blank cells in colums A and B are selected
• the first blank cell, A3, is the active cell
• selected cells are only within the used data range, even though entire columns were selected

2) Create Simple Formula

Next, complete the following steps, to create a simple formula, that will copy the value from the closest heading above each blank cell.

Currently, blank cells in columns A and B are selected, and cell A3 is the active cell.

To build the formula, follow these steps:

• With the blank cells selected, type an equal sign, to start the formula.
• On the keyboard, press the up arrow key
  • This enters a relative reference to the cell above
  • Cell A3 is active, so it refers to cell A2, the cell directly above it

In the screen shot below, you can see the formula in cell A3, and in the formula bar:

• =A2

Enter Formula in All Selected Cells

The final step will enter that formula in all the selected blank cells

• DO NOT tap the Enter key, as you normally would, to complete a formula
• Instead, press Ctrl + Enter
  • Combining the Control and Enter keys will enter the active cell's formula in all the selected cells

Because the formula uses a relative reference, each cell's formula refers to the cell directly above it.

• This screen shot shows the formula in cell B4, which refers to cell B3
• If you check cell B3, you'll see that its formula refers to cell B2

3) Change Formulas to Values

The final step, before you sort or filter the data, is to convert the "blank cell" formulas to values, to lock them in.

• WARNING: If you omit these steps, and you leave the the cell reference formulas in the cells, the formula results will show incorrect data later, if you sort the list.

You can:

• use Ribbon commands to change the formulas to values (steps below)
• OR, use a quick mouse shortcut (video below)

Use Ribbon Commands to Paste Values

Follow these steps to change the "blank cell" formulas to values

• Select the entire columns where you filled in the blanks with formulas -- columns A:B in this example
  • Note: Do not select the entire columns if other cells in those columns contain formulas that you need to keep. You don't want to accidentally change those other formulas to values.
• On the Ribbon's Home tab, click Copy, or use the keyboard shortcut - Ctrl + C
• With the columns still selected, click the drop down arrow on the Paste command
• In the Paste Values section of the drop down menu, click the Values command

Video: Mouse Shortcut Paste As Values

To quickly change formulas into values, you can use this mouse shortcut, shown in the video below. The written instructions are on the Excel Data Entry Tips page.

Values in All Selected Cells

After you complete the above steps, to use the Paste Values command, the blank cells are filled in with values, instead of cell reference formulas.

With cell values "locked" in, you can safely sort or filter the list.

Fill Blank Cells - Macros

If you frequently have to fill blank cells, you may prefer to use a macro. The following Excel VBA code examples fill blank cells in the active column. Each example uses a different method to find the last row, and to fill blank cells programmatically

Tip: For more information on finding the last row, see Ron de Bruin's page: Find last row, column or last cell. Ron explains the advantages and disadvantages of each method.

-- Macro 1 - Dave Peterson -- Macro 2 - Rick Rothstein -- Macro 3 - Dave Peterson and Ron de Bruin -- Macro 4 - Alex Blakenburg

Fill Blank Cells Macro - Example 1

In this fill blank cells macro, from Dave Peterson:

• 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

You can copy the code below, and paste it into a regular code module, in your own Excel file. Or, download the sample Excel file, and copy the code from there.

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       

Fill Blank Cells Macro - Example 2

This fill blank cells macro, from Rick Rothstein

• uses the .Find method to calculate the last row
• Instead of using a formula to fill from above, each cell gets its value from the cell above the first cell of the Area that it's in, using the Offset property

You can copy the code below, and paste it into your own Excel file. Or, download the sample Excel file, and copy the code from there.

Sub FillColBlanks_Offset()
'by Rick Rothstein  2009-10-24
'fill blank cells in column with value above
'https://www.contextures.com/xldataentry02.html

  Dim Area As Range, LastRow As Long
  On Error Resume Next
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
               SearchDirection:=xlPrevious, _
               LookIn:=xlFormulas).Row
  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
               SpecialCells(xlCellTypeBlanks).Areas
    Area.Value = Area(1).Offset(-1).Value
  Next
End Sub

Fill Blank Cells Macro - Example 3

This example combines Dave Peterson's code (Example 1), with the special cells test from Ron de Bruin.

• In Excel 2007, and earlier versions, there is a problem with special cells if there are more than 8192 different areas in the special cells range.
  • NOTE: This problem was fixed in Excel 2010.
• This code tries to count the areas, and if over the limit, it loops through the range in groups of 8000 rows

You can copy the code below, and paste it into your own Excel file. Or, download the sample Excel file, and copy the code from there.

Sub FillColBlanksSpecial()
'https://www.contextures.com/xldataentry02.html
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above

'2010-10-12 incorporated Ron de Bruin's test 
'  for special cells limit
'https://www.rondebruin.nl/win/s4/win003.htm

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next
 
lRows = 2 'starting row
lLimit = 8000

Set wks = ActiveSheet
With wks
   col = ActiveCell.Column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   
    lCount = .Columns(col).SpecialCells(xlCellTypeBlanks) _
					.Areas(1).Cells.Count
    
    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
        'next line can be deleted
        MsgBox "Over the Special Cells Limit" 
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col), _
                 .Cells(lRows + lLimit, col)) _
                 .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col), _
                 .Cells(LastRow, col)) _
                 .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

End With

End Sub

Fill Blank Cells Macro - Example 4

This macro example is from Alex Blakenburg, who had numbers entered as text in his data, and didn't want them converted to real numbers. Also, his data didn't always start in row 1 on the worksheet.

In this macro, Alex addressed those problems:

• macro starts from the active cell
• stops at the last row in the active column, after resetting the last cell
• uses .FillDown, instead of .Value = .Value
  • using .Value = .Value converts text numbers to real numbers

You can copy the code below, and paste it into your own Excel file. Or, download the sample Excel file, and copy the code from there.

Sub FillSelBlanks_Num()
'from Alex Blakenburg
'use FillDown to prevent text numbers
' from converting to real numbers
'starts from active cell
Dim RowEnd As Long
Dim RowStart As Long
Dim ColStart As Long
Dim RowNo As Long
Dim wks As Worksheet
Set wks = ActiveSheet

' Reset end of sheet and
'   capture end row and column nos
Call ResetLastCell
    
With wks.UsedRange
  RowEnd = .Rows(.Rows.Count).Row
End With

' Start from selected cell
'   check next row is blank, fill down
RowStart = ActiveCell.Row
ColStart = ActiveCell.Column

RowNo = RowStart

' to limit errors due to invoking
'   accidentally, check that next row
'   is blank. If not, exit routine
With wks
  If Not IsEmpty(.Cells(RowStart + 1, _
          ColStart)) _
        And .Cells(RowStart + 1, _
        ColStart).Value <> "" Then
      MsgBox "Expect next row to be blank" _
        & " - exiting routine"
      Exit Sub
  End If

  ' Cycle through rows,
    '  copy group value down

  Do Until RowNo >= RowEnd
    RowNo = RowNo + 1
    If IsEmpty(.Cells(RowNo, _
        ColStart).Value) _
      Or .Cells(RowNo, _
        ColStart).Value = "" Then
      ' Use previous row to try to transfer
      '   both the text attribute and value
      ' for Numeric text. Using the variable
      '   converted numeric text to a number
      .Cells(RowNo, ColStart).FillDown
    End If
  Loop
End With

End Sub

'=============================

Sub ResetLastCell()
Dim lLastRow As Long
Dim lLastColumn As Long
Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim rng As Range
Dim wks As Worksheet
Set wks = ActiveSheet

' Find last row,column
'   special cells method

With wks.Range("A1") _
    .SpecialCells(xlCellTypeLastCell)
  lLastRow = .Row
  lLastColumn = .Column
End With

' Find backwards from A1
'   to last non-blank row
With wks.Cells
  lRealLastRow = .Find("*", Range("A1"), _
    xlFormulas, , xlByRows, xlPrevious).Row

  ' Find backwards from A1
    ' to last non-blank column
  lRealLastColumn = .Find("*", Range("A1"), _
    xlFormulas, , xlByColumns, _
      xlPrevious).Column
End With

With wks
  'Delete from row after real last row
  'to last row, per special cells method
  If lRealLastRow < lLastRow Then
    .Range(.Cells(lRealLastRow + 1, 1), _
      .Cells(lLastRow, 1)).EntireRow.Delete
  End If

  'Delete from column after real last column
  'to last column per special cells method
  If lRealLastColumn < lLastColumn Then
      .Range(.Cells(1, lRealLastColumn + 1), _
        .Cells(1, lLastColumn)) _
          .EntireColumn.Delete
  End If

Set rng = .UsedRange 'Resets last cell
End With
    
End Sub

Hide Duplicate Headings

After you fill in the blank cells with values from above, you can use a conditional formattng 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.

Fill Blank Cells Video Transcript

Here is the transcript for the Fill Blank Cells video, at the top of this page.

Introduction

Sometimes in Excel, you'll end up with data like this, possibly exported from another system where you've got headings, but blank cells below those headings.

Here we can see region and it's only listed once, and then blank to the end of the region, and the employees in each region. You might have one or multiple employees, but again, blank below those employee names.

This is fine for reading the list, but if you want to work with the data, perhaps filter it or sort things, then you need to fill in these blank cells. Here's a quick way to do that.

Select Blank Cells

First, we'll select columns A and B where there are blanks.

• Then on the Ribbon's Home tab, go to Find & Select, Go To Special.
• In the Go To Special window, click Blanks, and then click OK.

Add a Formula

All the blank cells are selected now and we're going to put in a very simple formula that just says get the value from the cell above.

• Type an equal sign and then press the up arrow on your keyboard. You can see the formula here now has changed to A2.
• To fill that formula into all the selected cells, press the Ctrl key and then tap Enter.

That puts that same formula into all the selected cells.

Change to Values

Now we want to change these formulas to values so that we can move things around without having the values change.

• I'm going to select columns A and B where we filled the blanks and then point to the border of what is selected. You'll see a four-headed arrow there.
• Press the right button on the mouse and drag slightly to the right. Then drag right back to where you started.
• Let go of the right mouse button, and the pop up menu has some choices. Click Copy Here As Values Only.

Now everything in here is a value instead of a formula, and you can sort or filter without any problems.

Get the Sample File

To see the report with blank cells, and test the Fill Blanks macros, you can download the sample Excel workbook file. The file is zipped, and is in xlsm format, and contains macros.

More Data Entry Tutorials

Data Entry Tips

Convert Text to Numbers

Increase Numbers With Paste Special

Fill Blank Cells Excel 2003