Microsoft Office Tutorials and References
In Depth Information
Filling the Gaps in a Report
Filling the Gaps in a Report
When you import data, you can end up with a worksheet that looks something like the one
shown in Figure 166-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 166-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
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
Find & Select
Go to Special to display the Go To Special
dialog box.
3. In the Go To Special dialog box, 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
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 166-2. Now it’s a more traditional list, and you
can do whatever you like with it — including sorting.
Search JabSto ::

Custom Search