Sometimes the data that comes into Excel isn’t laid out very well. There is a technique that allows us to fix this problem.
Some systems don’t populate all the records in all the fields. There is a technique that allows us to fix this problem and it involves filling in blank records in a data table.
Filling in missing records
Figure 1 has an example of the type of data layout that causes problems. The invoice numbers need to be populated in the cells below them. This layout cannot be easily used by formula or Pivot Tables.
1. Select the range involved (A2:A11 in the example) and press the F5 key
2. Click the Special Button
3. In the dialog click the Blanks option - Figure 2
- and click OK
4. We will enter a formula. Note the first selected cell and type = and then use the mouse to select the cell in the row directly above the first selected cell.
5. Hold Ctrl and press Enter. That will enter a relative formula in all the cells. You can’t leave the formula in the cells in case the data gets sorted.
6. The last step is to select the whole range again and copy it and right click it and choose Paste Special and then Values and OK. This over writes the formula with a value. (Watch the video for a shortcut to Paste Special Values)
All the blank cells now contain the correct entries and the data can be used by formula and Pivot Table reports. See Figure 3
Neale Blackwood CPA run A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected]