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

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.

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.

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.