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