How to use the A1 and R1C1 cell reference styles in a formula. - Tutorial

Presented below are the differrent cell reference notations and how they are used in formulas. The A1 or R1C1 reference styles are the notation used to identify cell positions in worksheet formulas.

Build Excel Formula

What is an A1 Style Cell Reference in an Excel Formula?

Excel allows an individual to use two styles of cell references > to identify a cell’s position in a formula >, A1 and R1C1 reference styles. These styles are controlled by adjusting the controlling options of Microsoft Excel.

The A1 reference style is the default reference style for Excel. To tell if the A1 reference style is in use, look at the column headers of a worksheet. If letters appear for the column headings, the reference style for Excel is currently A1.

In this mode, cells are referenced in formulas using a letter for the column and a number for the row. The picture below demonstrates this concept with a simple formula.

A1 Reference in an Excel Formula

Scroll < > picture if hidden.

The example above demonstrates what A1 reference style looks like in Excel. Notice the column headers are letters. This is the most common way to build formulas in Excel. Note that cell references appear in the formula with a letter and a number.

What is an R1C1 style cell reference in an Excel formula?

To tell if the R1C1 reference style is in use, look at the column headers of a worksheet. If numbers appear for the column headings, the reference style for Excel is currently R1C1. In this mode, cells are referenced by relative nomenclature R[-1]C[-1] or in absolute nomenclature R1C1. The letters R and C are used in both modes to represent row / column positions. The picture below demonstrates this concept with a simple formula where the cells are referenced in R1C1 absolute nomenclature.

R1C1 Reference in an Excel Formula

Scroll < > picture if hidden.

The example above demonstrates what R1C1 mode looks like in Excel Notice the column headers are numbers. Generally you do not build formulas in this mode, the author does not.

How to switch between A1 and R1C1 cell reference modes on the worksheet?

Whatever reference style mode Excel is in, all cell references in formulas must be in that reference style form when typed. If using the mouse to select a cell reference, Excel will choose the proper syntax.

If you do not use the proper syntax, an error will be generated when the formula is entered. The A1 reference style is the easiest to use in Excel. It is recommended that this style is used when constructing formulas.

To switch between the reference modes in Excel, proceed to the Ribbon and select File / Options. On the Excel Options screen, select the Formulas tab. On that tab, check or uncheck the R1C1 reference style box. Note that when you toggle this box, the reference notation in the formulas will toggle.

When dealing with Excel and its many features and quirks, always recheck everything if switching.

Switch Between A1 and R1C1

Scroll < > picture if hidden.

The example above demonstrates the Excel Options dialog box and how to toggle reference style mode. This is the control panel for how Excel operates. Be sure to check out the Advanced tab and General tabs as they contain a lot of Excel functionality control.

Additional Tutorial Topics:

05/08
< Back
Next >
Need Help? Please call us at 1.805.498.7162

Copyright © 2002-2023

EMAGENIT All Rights Reserved