Excel Tip – Change behaviour of relative references

When we drag relative formula across from left to right then the result changes relative to the current cell. I.e. Dragging the formula =A1 from left to right will display the result of A1 in the first cell, A2 in the second cell, A3 in the third cell and so on. If we want to change this behaviour so that dragging the formula across gives us say the result of cell A1 3 times before it changes to the next cell, we can use the following formula:

=INDIRECT(ADDRESS(2,1 + ((COLUMN() – 1)/3)))

The 3 highlighted in yellow at the end of the formula determines how long the result stays constant until we move to the next cell so if we want to for instance make the formula return the same value for every two cells containing formula then we can change this number to 2.

Share this tip on