Converting GL transaction data – populating lines with account numbers

Let’s say you export out transaction detail from the general ledger. You may want to work with it in a pivot table. But the individual lines are missing the account numbers. They might be above or below the data. Here is a trick to populate the lines.

Here is a sample of our data. Highlighted in yellow is where we want our account numbers.

In cell A3 type “=A2”. Now we want to copy this formula but only in the yellow cells. Not on top of the remaining header account numbers.

  • Copy cell A3.
  • Select the range where you want to paste.
  • Press F5 to launch the GoTo box.
  • Click the “Special…”
  • Click “Blanks”
  • Now you selected just the blank cells.
  • Ctrl V (or paste from the menu).

I’ve changed the area from yellow to blue to show how it should look with account numbers populated.