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 Differences Between Lists

A common task faced by Excel users is to determine whether items in one list are also found in a different list. There ...

Discover More

Understanding Cell Indenting

Formatting a cell could, if you desire, also include the indentation of information within the cell. This tip examines ...

Discover More

Adding Automatic Time Stamps

Your computer knows the current date and time, and Word provides ways you can get that date and time into your document. ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Returning the Minimum of Integers of a Range

If you have a range of numbers that contain both integers and decimal numbers, you may have a need to determine the ...

Discover More

Calculating the Distance between Points

Want to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...

Discover More

Returning a Weight and a Date

If you have two columns containing dates and weights from those dates, you may want to pick a date associated with 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 four more than 7?

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.