Fix Excel When Rows and Columns are Both Numbers – Sheetaki

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.

column heading are numbers


If we try to use the cell reference B2 in a formula, we will find a #NAME? error instead.

original notation does not work


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.

use R1C1 notation to reference cells relative to cells


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:

  1. First, navigate to the File tab.
    check the File tab
  2. Click on the Options button. You can find this in the lower-left corner of your screen.
    click the Options menu
  3. 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.
    fix rows and columns are both numbers
  4. Your spreadsheet should now revert back to the default A1 reference style with columns labeled with letters.
    spreadsheet reverted to default

These are all the steps needed to fix your spreadsheet when rows and columns are both numbers in Excel.



Frequently Asked Questions (FAQ)

  1. 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. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.


Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like