It is true you can “easily” fill in data by dragging a value down over the blank cells. But easy does not mean quick; dragging each individual value down take a long time.
A faster method is to use the special powers of Ctrl G. As explained in the Wonders of Ctrl G article, Ctrl G is Excel’s “warp drive” Go To keyboard short cut. In this example you would do the following:
Note: You can download the sample spreadsheet from the TechMentor website.
| Action | Comments | |
| 1 | Press Ctrl Home | This selects cell A1. |
| 2 | Press Ctrl Shift 8 (on the keyboard) or Press Ctrl * (on the number pad) |
This selects all the data in one contiguous region. |
| 3 | Press Ctrl G | This displays the Go To dialog box. |
| 4 | Click the Special button | This displays the Go To Special dialog box. |
| 5 | Select the Blanks option and click OK. |
This select all the blank cells. |
| 6 | Press =[Up Arrow] (Do NOT press enter) |
The equal sign begins a formula, and the up arrow refers to the cell above the current cell. |
| 7 | Press Ctrl Enter | This puts the formula in ALL the selected cells. Enter by itself puts the formula in only the active cell. Ctrl Enter puts the formula in ALL the selected cells. |
Good news and bad news
Now we have some good news, and some bad news. The good news is the blanks have been filled in. The bad news is, if the spreadsheet is ever sorted, those cells may change their values because their formulas would be referencing different cells.
Convert the formulas into values
The safe thing to do is convert those formulas into values. We can do this with Excel’s Paste Special feature. However, we must be careful to not convert the Extended Price formula in column I. To do this correctly we will again use the powers of Ctrl G.
| Action | Comments | |
| 1 | Press Ctrl Home | This selects cell A1. |
| 2 | Press Ctrl G | This displays the Go To dialog box. |
| 3 | Type A:D and click OK | This selects columns A through D. |
| 4 | Press Ctrl C | This copies the data. |
| 5 | Press Ctrl Alt V (Windows) Press Ctrl Cmd V (Mac) |
This displays the Paste Special dialog box. |
| 6 | Select Values and click OK | This pastes the displayed values. |
| 7 | Press Ctrl Home | This returns the cursor to cell A1. |
Want to learn more tips and tricks for Excel? Please contact TechMentors and we can talk about the value of setting up a virtual or at-your-location training for you and your company.
