Part IV: Managing and Analyzing Data
Filling gaps in an imported report
When you import data, you can sometimes end up with a worksheet that looks something
like the one shown in Figure 25.24. This type of report formatting is common. As you can
see, an entry in column A applies to several rows of data. If you sort this type of list, the
missing data messes things up, and you can no longer tell who sold what when.
FIGURE 25.24
This report contains gaps in the Sales Rep column.
If the report is small, you can enter the missing cell values manually or by using a series of
Home ➪ Editing ➪ Fill ➪ Down commands (or the Ctrl+D shortcut). But if you have a large
list that’s in this format, here’s a better way:
- Select the range that has the gaps (A3:A14, in this example).
- Choose Home ➪ Editing ➪ Find & Select ➪ Go to Special. The Go to Special dialog
box appears.
- Select the Blanks option and click OK. This action selects the blank cells in the
original selection.
- In the formula bar, type an equal sign (=) followed by the address of the first
cell with an entry in the column (=A3, in this example) and press Ctrl+Enter.
- Reselect the original range and press Ctrl+C to copy the selection.
- Choose Home ➪ Clipboard ➪ Paste ➪ Paste Values to convert the formulas to
values.
After you complete these steps, the gaps are filled in with the correct information, and
your worksheet looks similar to the one shown in Figure 25.25.
