Use Column Numbers in Excel Instead of Alphabets (How To)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

In Excel, columns have alphabets as their identity, but you can change it to a number. That means for column A you can use column 1. In this tutorial, we will learn two different ways that you can use.

  • Create Header Row with Column Number
  • Change Cell Reference to R1C1 to Apply for Column Numbers
use-column-numbers

Change Column Alphabets to Number (R1C1)

  1. First, click on the File Tab.
    file-tab
  2. After that, go to “Options”.
    go-to-options
  3. Next, click on the “Formulas” and from there, tick mark the “R1C1 reference style”.
    tick-r1c1-reference-style
  4. In the end, click OK to apply the settings.
    apply-settings

When you do this, it changes the reference style for the entire Excel application. That means now you need need to refer to a column using a number, not by the alphabet.

reference-style-for-entire-excel-app

You see in the above example where I am referring to cell A1 it shows R[-1]C[-2] instead of the A1. Well, this means you are referring to the one row and two columns backward.

You can learn more about it from this complete guide on the R1C1 reference style.

Create a Header with Column Numbers

This method could be used in different situations, but as effective as the first one.

  1. First, edit cell A1 and enter “=COLUMN()” in it.
    header-with-column-numbers
  2. After that, hit enter to get the number of the current column.
    get-the-number-of-current-column
  3. Now, use the fill handle and extend the formula to the column you want to have column numbers.
    fill-handle-to-extend-formula
  4. In the end, you can convert all these formulas into values by using the keyboard shortcut (Ctrl + C to copy and then press Alt → H → V → V to paste the values).

This method works better when you don’t want to change the cell reference but want column number only.

Last Updated: October 30, 2023