How to get relative column numbers in a range in Excel

This tutorial shows how to get a full set of relative column numbers in a range using an array formula based on the COLUMN function.



On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter

This is a robust formula that will continue to generate relative numbers even when columns are inserted in front of the range.

Also See:   How to create dynamic named range with INDEX in Excel


In the example shown, the array formula in B4:H4 is:


How this formula works

The first COLUMN function generates an array of 7 numbers like this:


The second COLUMN function generates an array with just one item like this:


which is then subtracted from the first array to yield:

Also See:   Check multiple cells are equal in Excel


Finally, 1 is added to get:


With a named range

You can adapt this formula to use with a named range. For example, in the above example, if you created a named range “data” for B4:H4, you can use this formula to generate column numbers:


Leave a Reply

Your email address will not be published. Required fields are marked *