Relative Column Numbers in Excel: How to Get Them

To get the relative column number of a cell in Excel, use the COLUMN() function combined with a reference to the first column of your range. For example, =COLUMN(B2)-COLUMN($A$1)+1 returns 2 for cell B2, and this formula can be copied across columns to return 3, 4, 5, and so on. This article explains the formula, how it works, and how to apply it to real-world tasks like numbering columns in a dynamic range or creating formulas that adjust automatically.

Formula

The relative column number is calculated as:

Relative Column Number = COLUMN(cell) – COLUMN(first_cell) + 1

• cell – any cell in the range you want to evaluate (e.g., B2).
• first_cell – the first column of your range (e.g., A1 if the range starts in column A).
• COLUMN() – an Excel function that returns the absolute column number of a reference (A=1, B=2, …).

The +1 adjusts the result so that the first column in your range becomes 1, not 0.

Worked Example

Using the default values from the calculator: cell = B2 and first_cell = A1.

1. Find the absolute column number of B2: COLUMN(B2) = 2.
2. Find the absolute column number of A1: COLUMN(A1) = 1.
3. Subtract: 2 – 1 = 1.
4. Add 1: 1 + 1 = 2.

The relative column number of B2 when the range starts at A1 is 2. If you copy the formula to C2, it returns 3, and so on.

Why Use Relative Column Numbers?

Relative column numbers are useful when you need a column index that resets to 1 at the start of a specific range, rather than using the absolute column number (where column A is always 1, B is 2, etc.). This is common in:

• Creating dynamic formulas that reference a table’s first column.
• Numbering columns in a report that starts at column C, for example.
• Building formulas that need to count how many columns a cell is from a reference point.

For instance, if you have a data table starting at column C, you might want to know that column D is the 2nd column of that table. The formula =COLUMN(D1)-COLUMN($C$1)+1 gives 2.

How to Use the COLUMN Function

The COLUMN function is simple:

• =COLUMN() with no argument returns the column number of the cell containing the formula.
• =COLUMN(A1) returns 1.
• =COLUMN($E$5) returns 5.

To get a relative number, you subtract the column number of the first cell in your range and add 1. Use absolute references (like $A$1) for the first cell if you plan to copy the formula across columns.

Common Mistakes and Pitfalls

• Forgetting the +1 – Without it, the first column returns 0, which is not what you want.
• Using a relative reference for the first cell – If you copy the formula and the first cell reference moves, your results will be wrong. Use absolute references like $A$1.
• Confusing relative column number with absolute – The relative number depends on your chosen starting point, not the worksheet’s column A.
• Using COLUMN() on a range – COLUMN(A1:C1) returns an array {1,2,3}. In a single cell, it may spill or return only the first value, depending on your Excel version.

Alternatives and Related Functions

If you need the relative column number of a cell in a structured Excel Table (created with Ctrl+T), you can use the COLUMN() function with a reference to the table’s first column. For example, if your table is named Table1 and starts at column C, the formula =COLUMN([@Column1])-COLUMN(Table1[[#Headers],[Column1]])+1 gives the relative column number within the table.

Other related functions include:

• ROW() – returns the row number, which can be used similarly for relative row numbers.
• COLUMNS(range) – returns the total number of columns in a range.
• MATCH() – can find the position of a column header, which is often more useful than a numeric column number.

Frequently Asked Questions

How do I get the column number of a cell without using a formula?

You can use the CELL function: =CELL("col", A1) returns the column number of A1. However, this gives the absolute column number, not relative. To get a relative number, you still need to subtract the starting column’s number.

Does the formula work if I insert or delete columns?

Yes, because Excel automatically adjusts cell references. If you insert a column before the range, the absolute references like $A$1 will shift, but the relative formula will still return the correct relative number for the new layout. Just make sure the first cell reference points to the actual first column of your range after the change.

Can I use this in conditional formatting?

Absolutely. For example, to highlight every other column in a range starting at column C, use a formula like =MOD(COLUMN()-COLUMN($C$1)+1,2)=0 in the conditional formatting rule. This will apply to columns that have an even relative number (2nd, 4th, etc.).

Why does my formula return 0 for the first column?

That happens when you forget to add 1. The formula =COLUMN(B2)-COLUMN($A$1) returns 1 for B2, but for A2 it returns 0. Add +1 to the end to make the first column return 1.

Is there a way to get the relative column number in Google Sheets?

Yes, the same formula works in Google Sheets. The COLUMN function behaves identically, so you can use =COLUMN(B2)-COLUMN($A$1)+1 to get the relative column number.

Related

• Set Google as Search Engine in Safari (Mac & iPhone)
• Compare Data Center Proposals: A Community Guide
• Zoom Stuck on Connecting on Mac? Fixes That Work

in ref | Cell reference | - | B2 | 0
in first | First column cell | - | A1 | 0
out rel | Relative column number | - | COLUMN(ref)-COLUMN(first)+1 | 0