How to show column number

  • Post author:
  • Post category:Uncategorized

By default, the reference style for the cells in Excel are in A1 format, where columns are labeled with letters and rows with numbers.  

Figure 1.  Name box displaying the cell address “A1”

In the image above, the top left cell has a cell reference of A1, as shown in the name box just above it.  A1 means column A and row 1. The cell to its right is B1, then C1, and so on up to column XFD.

When we enter the formula =CELL(“address”,A1) into cell A1 and copy the formula down to G7, we will be able to display the cell address of cells A1:G7.  

Figure 2.  Column labels are letters in A1 style

Show column number

In order to show column number on the label instead of letters, we change the cell reference style to R1C1 by following these steps:

  • Click File tab > Options
  • In the Excel Options dialog box, select Formulas and check R1C1 reference style
  • Click OK

Figure 3.  Check R1C1 reference style

The column labels will automatically change from letters to numbers.  

Figure 4.  Columns are numbers in R1C1 style

The cell address will also change from A1 to R1C1.  The R1C1 reference style is easier to understand because it gives both the row number and column number of a cell.  R refers to row while C refers to column.  R1C1 means row 1 and column 1, while R7C7 refers to the cell in row 7 column 7.  

Note that R1C1 format does not change the formulas in our cells.  Only the format of referencing cells is changed.

How to number columns

Most of us are not used to the R1C1 reference style.  If we only want to number columns but keep the column labels as letters, we can simply insert numbers on the first row of our worksheet.  

  • Uncheck the R1C1 reference style check box in Excel Options > Formulas  to revert back to A1 style
  • In cell A1, enter the value “1”
  • In cell B1, enter the value “2”
  • Select cells A1 and B1

Figure 5.  Select the cells and click the fill handle

  • Click the Fill Handle, which appears as a plus “+” sign when we hover the cursor over the bottom right corner of cell B2
  • Drag the fill handle to the right up to column G.

Figure 6.  Output: How to number columns