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.

filling-gaps-in-a-range

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.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply