Software

Quickly fill blank cells in Excel

Blank cells can spell trouble. Here's an easy way to fill in those blanks and protect the validity of your data.
One of the first things you should do when importing foreign data into Excel or upon receiving a legacy workbook is to check for blank cells and fill them in, where appropriate. Of course, some cells are blank on purpose and should remain that way. But blanks can be troublesome, if not downright destructive. The sheet below is a good example of foreign data that as is, doesn't lend itself perfectly to a spreadsheet. In a report, repeating the company name from record to record might be distracting to the reader. Whereas in a sheet, a simple sort by a field other than the company name field would quickly orphan data -- there's no way to attach a record to its company. My best advice is to fill these types of blanks right away. You might consider typing the entries, but there's a quicker way:

  1. First, select the range that contains blanks you need to fill. Don't select the column header cell -- just the range that contains actual data. Using the example sheet above, the range is A2:A11.
  2. Select Go To from the Edit menu or press [Ctrl]+G and then click the Special button. In Excel 2007, choose Go To Special from the Find and Select dropdown list in the Editing group on the Home tab.
  3. Select Blanks.

  1. When you click OK, Excel will select all the blank cells in A2:A11.
  1. In the first selected blank cell (A3) enter an equal sign and point to the cell above. The cell is already selected, you don't have to actually click A3.

  1. Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range.

  1. At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To maintain order, replace the formulas with their results (the repeated literal values). Select the range (A2:A11) and choose Copy from the Edit menu. In Excel 2007, click Copy in the Clipboard group on the Home tab.
  2. Select Paste Special from the Edit menu. Then, select Values and click OK. In Excel 2007, choose Paste Values from the Paste drop-down list in the Clipboard group on the home tab. You just replaced the formulas with literal values.

  • If the range contains formulas before the process, be careful: You don't want to overwrite those formulas, just the formulas you added to fill the blank cells. This technique won't work with every sheet that contains blank cells. In this case, this quick technique repeats existing data.
  • About

    Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

    14 comments
    durs
    durs

    Can this be done in the opposite direction?  That means the blank cells are above the values being copying from instead of below the values being copying from in this illustrated example.


    anyandallart
    anyandallart

    Susan you rock!!!!! thanks so much for this.

    vidanuevatx
    vidanuevatx

    In reply to the user "postulation" - While the blank cells are still selected, before you enter data into them, right-click on one of them. Choose "Format Cells." On the Number tab, change Text to General. Then click OK. Then put your = and click the cell above your first cell. CTRL+ENTER, and you should have your data.

    postulation
    postulation

    I follow this exactly but instead of copying down the information above it, I just get "A2" in all the cells below.  I checked and the cells are not locked or protected.  Not sure why the =A2 is being interpreted as text.

    Thansk,

    neil.bacon
    neil.bacon

    I always appreciate these posts. There are occasional gems. This is one of them.

    Tink!
    Tink!

    I wasn't aware of the ability to select all blanks so that is a definite handy thing to keep in mind. Elsewise however, I find it alot faster to just use CTRL-D to fill in repeated values. (The keyboard shortcut for Edit, Fill, Down.)

    Carunchie
    Carunchie

    If PivotTables could fill in the blanks itself for columns that would be better. This tip is still a lot better than the nested if statements I use to recreate the column to fill in blanks. And another great tip. Didn't realize the functionality about [Ctrl]+[Enter]. Certainly will save me from some extra copy-paste steps.

    Ground Floor
    Ground Floor

    I can definately use this tip and appreciate quick "how-to" posts like this one.

    Editor's Picks