Our sample data is arranged in rows, but we need it to be arranged in columns like a traditional table.
The idea was to write the formulas as traditional cell-to-cell links (i.e. =B2) but use a bit of fixed text in place of the equals sign, such as “lg” – or your initials. (After we fill it down we would replace the “lg” with an equals sign to put everything back the way Excel expects it for proper cell-to-cell links.)
After we create a pattern for Excel to follow, we fill the pattern down.
The number sequences would continue when filling in a left-to-right direction, but it does not work when filling in a downward direction.
This is because when transposing from vertical to horizontal, we need the numbers in the cell references to change, and Excel is smart enough to figure that out.
When filling in a downward direction, we need the letters that reference the columns to change. Excel cannot distinguish our placeholder text (“lg”) from the column letter reference so it just repeats the original set of references.