Fill Blank Cells In Excel To Complete Table

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. The video, and written steps below, show how to fill blank cells in Excel to complete a table.

Missing Data in Imported Table

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.

Missing Data in Imported Table
Missing Data in Imported Table

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:

  • Select columns A and B, that contain the blank cells.
  • On the Ribbon’s Home tab, click Find & Select
  • Click Go To Special

FillBlanks02

  • 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.

  • With the blank cells selected, type an equal sign, to start the formula.
  • On the keyboard, press the up arrow. This enters a reference to the cell above – cell A2 in this example.

FillBlank02

  • 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 with a macro.

There’s sample code, by Dave Peterson, on my website:
Fill Blank Cells Programmatically

Video: Fill Blank Cells in Excel

This short video shows how to fill in the blank cells in Excel. Also, see the newer video, in the next section

Update: Video – Fill Blank Cells

Here is a newer version of the Fill Blank Cells Video, that I recorded in 2013.

________________

10 thoughts on “Fill Blank Cells In Excel To Complete Table”

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

  2. […] 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 […]

  3. 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.

  4. 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.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.