skip to Main Content
Quickly Fill In The Blanks

Quickly fill in the blanks

Have you ever had a need to quickly fill in the blanks of a spreadsheet? For example, suppose you need to fill in all the blank regions, store names, and order numbers with the value from the previous cell as shown in the image above.

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.

 ActionComments
1Press Ctrl HomeThis selects cell A1.
2Press Ctrl Shift 8
(on the keyboard)
or
Press Ctrl *
(on the number pad)
This selects all the data in one contiguous region.
3Press Ctrl GThis displays the Go To dialog box.
4Click the Special buttonThis displays the Go To Special dialog box.
5Select the Blanks option and click OK.This select all the blank cells.
6Press = [Up Arrow]
(Do NOT press enter)
The equal sign begins a formula, and the up arrow refers to the cell above the current cell.
7Press Ctrl EnterThis 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 seclected 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.

 ActionComments
1Press Ctrl HomeThis selects cell A1.
2Press Ctrl GThis displays the Go To dialog box.
3Type A:D and click OKThis selects columns A through D.
4Press Ctrl CThis copies the data.
5Press Ctrl Alt V (Windows)
Press Ctrl Cmd V (Mac)
This displays the Paste Special dialog box.
6Select Values and click OKThis pastes the displayed values.
7Press Ctrl HomeThis 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.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top