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
We have successfully inserted column numbers at the top row of our worksheet.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment