Get relative column numbers in range

To get a full set of relative column numbers in a range, you can use an array formula based on the COLUMN function. In the example shown, the array formula in B4:H4 is: {=COLUMN(B4:H4)-COLUMN(B4)+1} 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.

April 14, 2025 · 1 min · 84 words · Matthew Price

Table of Contents

SEQUENCE then builds an array of numbers, starting with the number 1.

For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet.

When no reference is provided, COLUMN returns the column number of the cell which contains the formula.

Excel formula: Last row number in range

SEQUENCE Function

The Excel SEQUENCE function generates a list of sequential numbers in an array.

The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.

…

COLUMNS Function

The Excel COLUMNS function returns the count of columns in a given reference.

Excel formula: First column number in range

Excel formula: Last column number in range

Excel formula: Get relative row numbers in range

Excel COLUMN function

Excel SEQUENCE function

Excel COLUMNS function