How to Work with Column Numbers Instead of Letters in Excel

One of Excel’s most useful features is the ability to reference cells by their column letter and row number For example, you can use =A1 to reference the cell located in column A and row 1 This makes it easy to create formulas that work across multiple rows and columns.

However, you may sometimes want to reference cells by their column number instead of letter For example, referencing the first column as =1 instead of =A

Referencing by numbers can make your formulas easier to understand, especially when working with large spreadsheets. It also enables you to loop through columns in VBA code using numbers rather than letters

In this guide, I’ll show you multiple ways to work with column numbers instead of letters in Excel.

How Column Referencing Works in Excel

Before diving into using numbers, let’s first understand how Excel’s default column referencing with letters works.

Excel uses the letters A to Z to label each column in a spreadsheet. After column Z, it starts doubling up letters: AA, AB, AC, and so on.

The letter corresponds to the column number:

• A = Column #1
• B = Column #2
• C = Column #3

And so on down the alphabet.

When creating formulas, you can refer to cells based on this letter and number combination. For example:

• A3 refers to Column A, Row 3
• C5 refers to Column C, Row 5

This type of referencing where the column is a letter and the row is a number is known as the A1 reference style. It’s the default for Excel and makes it easy to reference cells in your formulas.

But again, sometimes numbers are better for columns too. Let’s look at how to do that.

Enable R1C1 Reference Style

The simplest way to use numbers for columns is to switch Excel to the R1C1 reference style.

R1C1 style allows both rows and columns to be referenced by numbers instead of letters.

Here’s how to enable it:

1. Go to the File tab > Options.
2. Click Formulas from the left menu.
3. Under Working with formulas, select R1C1 reference style.
4. Click OK to enable.

Now instead of A1 and C5, your references will be R1C1 and R5C3.

You can immediately start using R1C1 style in your formulas. For example:

=SUM(R[-2]C[3],R[5]C)

This adds the value two rows up in column C and the value five rows down in the same column.

The main downside to R1C1 style is readability. For most users, reading =A1 is more intuitive than =R1C1.

But you can combine R1C1 style with other techniques covered next to get the numbers you want while keeping your references easy to understand.

Reference Columns by Number with INDIRECT

You can convert column letters to numbers using the INDIRECT function while still referencing cells normally.

For example:

=INDIRECT("C"&COLUMN(A1))

This returns the value in column C. By combining COLUMN with INDIRECT, it converts the column number to the letter while you reference it by number.

Let’s break down how it works:

• COLUMN(A1) returns 1 since A is the first column.
• We concatenate that number with a C to make C1.
• INDIRECT then converts C1 to just reference C.

You can drag this across columns to reference by number but keep your formulas easy to read.

This takes more work than just switching to R1C1 but maintains the readability of A1 references.

Use Structured Referencing for Tables

If your data is formatted as an Excel Table, you can use structured referencing to reference columns by number.

For example:

=TableName[[#Headers],[Column1]]

This references Column1 in the table by numeric position rather than letter.

Table column numbers start from the left at 1. So Column 1 is the first column, Column 2 the second, and so on.

To use structured references:

1. Format your data range as a Table.
2. Reference the Table name.
3. Add the column number in brackets.

The main limitation is structured references only work for Tables, not regular cell ranges. But it’s a great way to reference by number when using Tables.

Assign Numbers to Columns with VBA

You can programmatically assign numbers to columns using VBA code. This lets you loop through columns by number even while referencing cells normally with letters.

Here’s an example VBA macro that adds a number to each column header:

Sub AddColumnNumbers()  Dim iCol As Long    For iCol = 1 To ActiveSheet.UsedRange.Columns.Count    ActiveSheet.Cells(1, iCol).Value = iCol  Next iCol  End Sub

This loops through each column and inserts the column number into the first row.

Now you have a numeric reference you can use in VBA, but your formulas can still use the column letters.

Tips for Using Column Numbers

Here are some best practices as you work with column numbers:

• Start column numbers at 1 instead of 0. This matches Excel’s normal A1 column letters.

• Use R1C1 for simplicity in basic formulas with a small range of cells.

• Use INDIRECT to reference with numbers but keep A1 style.

• Take advantage of structured references for Tables.

• Assign numbers to headers with VBA if you need them for code.

• Don’t use numbers in public-facing workbooks. Letters are more intuitive.

And a few common use cases where numbers shine:

• Formulas with many columns are easier to understand with numbers.

• VBA loops through columns easier with numbers.

• Creating dynamic ranges by offsetting numbered columns.

Referencing Cells in Ranges

In addition to referencing entire columns by number, you can also reference specific cells within a range using numbers and R1C1 style.

For example, let’s say you have a range of data that spans C3:F25.

Instead of referencing cells like =C3, you can use:

=R3C1

This breaks down to:

• R3: Row 3
• C1: Column 1 in the range

Some other examples:

• R15C4 would refer to E18
• R5C2 would refer to D7

This makes it easy to loop through the range in code by incrementing row and column numbers rather than letters.

When to Use Column Numbers vs. Letters

Column letters are ingrained in how most Excel users work. But there are certain situations where numbers are preferable:

• Simplifying complex formulas – Formulas with 10+ columns are easier to follow with numbers.

• VBA coding loops – Incrementing column numbers instead of letters simplifies your code.

• Offsetting columns in dynamic ranges – Adding and subtracting numbers is easier than letters.

• Cell references within a range – Numbers can specify rows and columns within your data range.

In most cases, stick with the traditional A1 column letters. But leverage these techniques when column numbers would make your work more efficient.

Next Steps

Understanding how to reference columns numerically is a great skill to add to your Excel toolkit. Here are some next steps to take:

• Start using R1C1 references in your own formulas where relevant.

• Create VBA macros that loop through columns by number.

• Reference column numbers in dynamic ranges and indirect formulas.

• Share this guide with colleagues who want to learn more about column numbers!

Column numbers might take some getting used to, but can help simplify working with large datasets and tables in Excel.