Select Blank Cells
- First, select the range that contains blanks you need to fill. Choose Go To Special from the Find dropdown list in the Editing group on the Home tab. -OR- Press [Ctrl] + G for Go To
- Select Blanks. Quick Tip: Notice the additional options under Go To Special which are helpful with other Excel actions.
- When you click OK, Excel will select all the blank cells in selected range.
Copy Values from Above
- In the first selected blank cell (such as A3) enter an equal sign and point to the cell above. As the cell is already selected, you don’t have to actually click A3. Don’t press [Enter].
- Press [Ctrl] + [Enter] and Excel will copy the respective formula to all blank cells in the selected range. This keyboard shortcut can be used as a quick way of filling a lot of cells or copying a formula into a range when it is entered, rather than copying it separately afterwards.
At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To preserve the displayed data, replace the formulas with their results (the repeated literal values).
Paste Values in Excel
- Select the range and copy using any technique you prefer, for example, the Excel keyboard shortcut [Ctrl] + C for copy.
- Right-click on the selection and choose Paste Values from the shortcut menu or pick Paste Values from the Paste drop-down list in the Clipboard group on the Home tab. You can also find Paste Values from the Paste Special menu option.
- With Paste Value, you just replaced the formulas with literal values.
Be careful if the range contained formulas before the process. 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 but is a fast and easy way to repeat existing data and to quickly change downloaded data so you can get your work done in Excel.