Strange Formula Conversions

by Allen Wyatt
(last updated May 11, 2019)

1

Gretchen wonders why Excel keeps converting her formulas into something like " +RC[-1]*R10C21"? It converts most of the formulas, but not all. She notes that when a conversion is made, it is typically to all the formulas in a column or row.

What Gretchen is seeing in her formulas is known as "R1C1 reference style." It is an alternative method of displaying formulas, and even predates the introduction of Excel. The native Excel reference style is known as "A1 reference style." You can do a search on your favorite search engine to find plenty of explanations about the history of the reference styles. If you prefer, you can also look up a previous ExcelTip where I discussed R1C1 reference style and show how to turn it on and off.

The reference style is saved on a workbook-by-workbook basis. There is a catch here, though—the first workbook you open in an Excel session dictates the reference style used throughout the entire session. Thus, if the first workbook you open uses R1C1 format, then Excel assumes you want to use R1C1 format for all other workbooks during your current session. (And the converse is true—if the first workbook you open uses A1 format, then that reference format is used for the entire session.)

This can be a bit confusing, especially when you are routinely working with workbooks created or maintained by others. Let's say that Bob (a guy in your office) prefers to use the R1C1 reference style. So, Bob opens a workbook out on the network and switches to his preferred reference style. He then saves the workbook. If Gretchen then starts Excel by double-clicking on Bob's workbook, then Excel will start with the R1C1 reference style in play, and she will see her formulas displayed using that style. Plus, she will see them displayed in that style for any other workbooks she opens during that Excel session.

There are two possible solutions. First, Gretchen could make sure that she first opens a workbook that was previously saved with the A1 reference style in play. If she does this, then when she opens Bob's workbook—during the same Excel session—then Bob's workbook is displayed using the A1 reference style, as Gretchen prefers.

The second solution is to go ahead and open Bob's workbook. When Gretchen notices that the workbook uses the R1C1 reference style, Gretchen can then go and change the reference style as described in that other ExcelTip I mentioned. The downside to this is that if Gretchen didn't notice the reference style right away and she opened other workbooks after she opened Bob's, then those other workbooks will also be using the R1C1 reference style.

It can all be rather confusing, I know.

What is interesting is Gretchen's statement that "it converts most of the formulas, but not all." If R1C1 is turned on, then all formulas should be converted, not just some. If you turn R1C1 off, then all formulas should revert to the normal A1 reference style. I have not seen such a situation, nor was I able to reproduce the situation on any of my systems. If both reference formats are, indeed, visible in the same workbook, then I can think of only two possibilities. First, the R1C1 formulas may not be actual formulas—they could be simply text in text-formatted cells. Second, the workbook could be experiencing some sort of weird corruption that is symptomatic of a bigger problem for the workbook. (How to deal with corrupted workbooks is beyond the scope of this particular tip.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13628) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Finding Cells Filled with a Particular Color

Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your ...

Discover More

Lines that Don't Change When You Type

Create a form in Word and you will invariably be faced with the need to places fill-in-the-blank lines in the document. ...

Discover More

Specifying the Y Value in X of Y Page Numbering

Page numbers in Excel headers and footers have always been a source of frustration for users. This tip recounts one ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Checking for Proper Entry of Array Formulas

Excel allows you to enter two different types of formulas in a cell: A regular formula or an array formula. If you need ...

Discover More

Determining a Name for a Week Number

You could use Excel to collect data that is useful in your business. For instance, you might use it to collect ...

Discover More

Checking for Either of Two Text Values

Using a formula to find information in a text value is easy. Using a formula to find either of two text values within a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 6?

2019-05-11 06:25:04

Bob Beechey

The only other possibility is the use of INDIRECT that uses "A1" formulas as in =INDIRECT("A1") unless followed by a False parameter when it uses "RC" formulas as in =INDIRECT("RC[-2]",False). This is independent of the worksheets current formula setting.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.