Filling Gaps in a Range

When you import data, you can sometimes end up with a worksheet that looks something like the one shown in the figure. 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.

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. In Cell A4, type =A3 and press Enter.
2. Select A4 and Copy the Cell (CTRL +C).
3. Select the range that has the gaps (A3:A14 in this example).
4. Choose Home ➜ Editing ➜ Find & Select ➜ Go To Special. The Go To Special dialog box appears.
5. Select the Blanks option and click OK. This action selects the blank cells in the original selection.
6. Paste the Data (CTRL +V) and its done.
7. You can Replace the formula by Paste Special ➜ Values

After you complete these steps, the gaps are filled in with the correct information. Now it’s a normal list, and you can do whatever you like with it – including sorting.