Fill Blank Cells In Excel To Complete a Table

If you’ve imported data into Excel, you might need to clean it up before you can use it.

For example, your imported data might have Region headings in column A, Employee subheadings in column B, and sales data in columns C and D.

FillBlank01

If you want to filter or sort this data, you need to fill in the blanks in columns A and B, by copying each heading into the blank cells below it.

Select the Empty Cells

To select the empty cells, use Excel’s built in feature:

  1. Select columns A and B, that contain the blank cells.
  2. On the Ribbon’s Home tab, click Find & Select
  3. Click Go To Special
    FillBlanks02
  4. In the Go To Special dialog box, click Blanks, then click OK

Enter a Formula

To copy the value from the first heading above each blank cell, you can enter a formula.

  1. With the blank cells selected, type an equal sign, to start the formula.
  2. On the keyboard, press the up arrow. This enters a reference to the cell above – cell A2 in this example.
    FillBlank02
  3. Press the Ctrl key and tap the Enter key, to enter the formula in all the selected cells.

Change the Formulas to Values

Before you sort or filter the data, change the formulas to values. Otherwise, you’ll end up with a mess.

Note: Do this carefully if other cells in the columns contain formulas. You don’t want to accidentally change those formulas to values.

  1. Select the columns where you filled in the blanks with formulas
  2. On the Ribbon’s Home tab, click Copy.
  3. Click Copy
  4. With the columns still selected, click the drop down arrow on the Paste command
  5. Click Paste Values

The blank cells are now filled in with values, and you can safely sort or filter the list.

Fill Blank Cells With a Macro

If filling blanks is something that you do frequently, you can automate the process. There’s sample code, by Dave Peterson, on my website:

Fill Blank Cells Programmatically

Fill Blank Cells in Excel 2003

This short video shows how to fill in the blank cells in Excel 2003.

________________

You may also like...

9 Responses

  1. Bob R says:

    Have used this rarely, but when I have needed it… wow! Thanks for having this on your website. Bob

  2. Thanks Bob, it sure is a handy technique when you’ve got a long report to tidy up.

  3. links for 2009-09-03 | the markfr ditherings says:

    […] Contextures Blog » Fill Blank Cells In Excel To Complete a Table (tags: excel tips howto) […]

  4. Contextures Blog » Hide Duplicate Headings in Excel Report says:

    […] few weeks ago we looked at a way to fill blank cells in an Excel report, so you’d be able to filter or sort a table of data. The downside of that technique is that […]

  5. Rick Rothstein (MVP - Excel) says:

    In your Fill Cells With A Macro section above, you reference some code which you published in your Tips section… I just thought you might find this altenate macro code to be of some interest…

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

    It also does its filling in within the column for the ActiveCell.

  6. Rick Rothstein (MVP - Excel) says:

    This is a follow up to my other post… rather than visiting each blank cell, it should be more efficient to visit each area within the grouping of blanks and handle the entire area all at once. This code will do that…


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

    Note that I changed the LastRow variable’s name to LR in order to make the code lines short enough not to wordwrap onto their next lines.

  7. […] For written instructions please read the article Fill Blank Cells in Excel to Complete a Table […]

  8. cizium says:

    It’s very useful for me . Thank you very much for sharing the tips!!. :)

    Have a nice day to you!

  9. Cindy says:

    I am in awe of the effects EXCEL has and the knowlege of most of the users

Leave a Reply to cizium Cancel reply

Your email address will not be published. Required fields are marked *