This tip was originally published in April 2010 but continues to be one of our most popular.
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:
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.
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.
Select Blanks.
When you click OK, Excel will select all the blank cells in A2:A11.
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.
Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range.
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.
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.
