This guide will explain how you can fix Excel spreadsheets where the rows and columns are both displayed as numbers.
Excel offers two types of cell reference formats: A1 and R1C1. We’ll show you how to toggle between these two.
By default, Microsoft Excel uses the A1 reference style. If you’ve used Excel before, you are already familiar with this format. This reference style refers to columns as letters and refers to rows as numbers.
For example, the cell G6 refers to the cell at the intersection of column D and row 50.
Besides the default format, Excel also allows users to work with the R1C1 reference style, also known as Relative Notation.
This reference style is useful when computing row and column positions when creating macros. In this style, Excel indicates the location with an “R” followed by the cell’s row number and a “C” followed by the cell’s column number.
The R1C1 notation also uses square brackets to indicate relative positions. For example, the formula =R[1]C[1] refers to a cell with one column to the right and one row below the current cell.
If you’ve opened your application and want to revert your reference style from R1C1 to A1, you can find the option to do so in the Options dialog box.
Now that we know more about the R1C1 format, let’s take a look at an actual spreadsheet that uses this reference style.
Example of a Spreadsheet Where Rows and Columns are Both Numbers
In the following section, we will provide a sample spreadsheet where rows and columns are both numbers. We’ll also go in-depth on the formulas and tools used in these examples.
In the example below, we have a table of numerical values. The column headings use numbers instead of letters. For example, what would have been written as cell B2 is now written as cell R2C2.
If we try to use the cell reference B2 in a formula, we will find a #NAME? error instead.
This reference style also treats cell references differently when constructing formulas. For example, if we were to select the cell to the left of cell R2C5, we’ll instead have the cell reference =RC[-1]. This way of naming cells is useful when building Excel macros.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. Note that the R1C1 reference style is only available in the Desktop version of Excel.
If you want to learn how to toggle between the A1 and R1C1 reference style, head over to the next section to read our step-by-step breakdown
How to Fix Rows and Columns are Both Numbers in Excel
This section will guide you through each step needed to fix sheets where the rows and columns are both numbers. You’ll learn how to access the Options dialog box, where you can find the option to toggle between the two available cell reference styles.
Follow these steps to use the A1 reference style in your spreadsheet:
- First, navigate to the File tab.
- Click on the Options button. You can find this in the lower-left corner of your screen.
- An Excel Options dialog box should now appear. Click on the Formulas tab and look for the section labeled ‘Working with formulas’. Click the R1C1 reference style checkbox to uncheck the option. Click the OK button to apply the changes.
- Your spreadsheet should now revert back to the default A1 reference style with columns labeled with letters.
These are all the steps needed to fix your spreadsheet when rows and columns are both numbers in Excel.
Frequently Asked Questions (FAQ)
- Why is the R1C1 reference style used for macros?
Displaying formulas using the R1C1 reference style makes the relationship between cells easier to understand. For example, the formula =R[-1]C[-1]+R[+1]C[+1] provides the relative location of the two cells you want to add together. The numbers inside the square brackets indicate how many rows or columns are between the target cell and the cell containing the formula.
This step-by-step guide should be all you need to fix sheets where the column is labeled as a number. Our guide shows how to access the Options dialog box to uncheck the R1C1 reference style option.
Cell reference styles are just one of many ways to customize your Excel program to fit your workflow. Our website covers hundreds of other Excel methods and functions that you can apply to your own spreadsheets.
We also have plenty of guides that can help you fix various issues you might encounter in Microsoft Excel. For example, you can follow this guide to fix your spreadsheet if your row and column headings are not visible.
With so many other Excel functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to look into the latest Excel guides and tutorials from us.
