Microsoft Office Tutorials and References
In Depth Information
Tip 70: Filling the Gaps in a Report
Filling the Gaps in a Report
When you import data, you can sometimes end up with a worksheet that looks something like the
one shown in Figure 70-1. 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 70-1: This report contains gaps in the Sales Rep column.
If your list is small, you can enter the missing cell values manually or by using a series of Home➜
Editing➜Fill➜Down commands (or its Ctrl+D shortcut). But if you have a large list that’s in this
format, you need a better way of filling in those cell values. Here’s how:
1. Select the range that has the gaps (A3:A14, in this example).
2. Choose Home➜Editing➜Find & Select➜Go To Special.
The Go To Special dialog box appears.
3. Select the Blanks option and click OK.
This action selects the blank cells in the original selection.
4. On 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.
5. Reselect the original range and press Ctrl+C to copy the selection.
6. Choose Home➜Clipboard➜Paste➜Paste Values to convert the formulas to values.