Software

Two tips for filling in holes in your Excel data

Are you or your users wasting time manually filling in blanks in your Excel sheets? If so, here's a shortcut that's guaranteed to save time.

How do you deal with blank cells in the middle of your Excel data? If your first thought is, “I manually copy or type something into each blank cell,” think again. Excel offers a keyboard shortcut that allows you to copy a formula (or a string) to all the blank cells in a given range. To demonstrate how this shortcut works, we’ll use it to solve two common Excel problems.

Solution 1: Underscoring blanks for proofreaders
If you’re smart, you never, ever turn in a printed or electronic report without proofreading the data. Typically, you print a hard copy and then go over it with a fine-tooth comb, using your red pen to make additions and corrections.

If the data contains blanks that need to be filled in, as the print preview in Figure A illustrates, the proofreader can always write in the white space on the printout. However, if a different person enters the changes made by the proofreader, a document with lots of changes can be confusing.

One way to make those printouts easier to read and to edit is by printing underlines to identify empty or missing entries. Underscores like those shown in Figure B make it easy to tell at a glance where information is missing.

Figure A
When you print this data, the blank cells might be overlooked.


Figure B
Printing underlines to “mark” the blank cells helps make proofreading easier.


Use the [Ctrl][Enter] shortcut
So how do you put underscores in all the blank cells in a range without entering each cell manually? Just follow these steps:
  1. Select the range that contains the blank as well as the non-blank cells.
  2. Choose Edit | Go To.
  3. When the Go To dialog box appears, click the Special button.
  4. Select the Blanks radio button and click OK. When you do, your sheet will look like the one shown in Figure C. At this point, Excel’s Name Box will display the cell address of one of the blank cells in the selected range.
  5. Type 5 or 6 underscores (or more as needed).
  6. To complete the entry, press [Ctrl][Enter], which copies that entry into all the blank cells you have previously selected.

Figure C
Here’s what your spreadsheet looks like after you select the data range and “go to” the blank cells.


Solution 2: Copying entries in a column
Here’s another valuable use for the [Ctrl][Enter] shortcut. Suppose you’re working with the data shown in Figure D, and you want to sort those records by the Client column. Unfortunately, all those “holes” in column A will prevent you from sorting the data correctly, so you first must copy each person’s name down into the appropriate blank cells.

Figure D
You won’t be able to sort this data on column A until you use our trick to copy the names into the blank cells.


Fortunately, you don’t have to manually key or copy those names. Start by selecting all of column A. Open the Edit menu and choose Go To. Like before, click the Special button, select the Blanks option, and click OK.

This time, the “current” cell is A3. So type +A2 as if you were simply going to copy the first name into A3. Then press [Ctrl][Enter], and Excel will copy that formula (and adjust the cell references accordingly) into all the remaining blank cells in column A, as shown in Figure E.

Figure E
After we selected the blank cells in column A, we populated those cells by using [Ctrl][Enter] to copy our simple formula (+A2) into the cells.

Get valuable tips for using Excel, Word, and Access, all delivered straight to your inbox every Wednesday and Friday. Best of all, they’re absolutely free! Sign up for Microsoft Office Suite—one TechMail that will bring you power tips for working with documents, databases, worksheet functions, VBA, and much more.
0 comments

Editor's Picks