Make Your Formulas Increment by Rows When You Copy Across Columns

Excel's automatic incrementing of cell references works well most of the time, but sometimes you might want to override how it works.

You might want to reference a single cell, such as cell A1, and then copy this reference across columns to the right. Naturally, this results in the formula reference changing to =B1, =C1, =D1, etc, which is not the desired result. You want the formula to increment by rows rather than columns-that is, =A1, =A2, =A3, etc.

Unfortunately, there is no option in Excel that lets you do this. However, you can get around this by using the INDIRECT function with the ADDRESS function nested inside.

Perhaps the best way to explain how to create the required function is to use an example with predictable results. In cells A1:A10, enter the numbers 1 through 10 in numerical order. Select cell D1, and in this cell enter the following:

=INDIRECT(ADDRESS(COLUMN(A:A),1))

As soon as you enter this, the number 1 should appear in cell D1. This is because the formula references cell A1.

If you copy this formula across the column to the right, cell E1 will contain the number 2. In other words, although you are copying across columns, the formula reference is incrementing by rows.

This method is especially useful when a spreadsheet has headings going down rows in one column, and you want to create a dynamic reference to these rowheadings across other columns.

If you keep copying this to the right, cell F1 will contain the number 3, cell G1 will contain the number 4, etc. This is a fairly straightforward process if you are referencing only a single cell. Many times, however, you will need to reference a range of cells that is being used in the argument for a function.

We'll use the ever-popular SUM function to demonstrate what we mean. Assume you receive a long list of numbers, and your job is to sum the column of numbers in a running total fashion, like this:

=SUM($A$1:$A$2), =SUM($A$1:$A$3), =SUM($A$1:$A$4)

The problem occurs because the results need to be dynamic and to span across 100 columns on row 1 only, not down 100 rows in another column (as often would be the case).