Strange Formula Conversions

Written by Allen Wyatt (last updated February 1, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


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, 2021, 2024, and Excel in Microsoft 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

Turning Off Word's Second Guessing with Quote Marks

When you type quote marks in a document, Word normally changes them to Smart Quotes. They look better on a printout, but ...

Discover More

Opening the Document Map by Default

The Document Map can be a handy tool to use when navigating and working with a large document. If you want the Document ...

Discover More

Adding a Background to Your Document

Document backgrounds come in handy if you plan on converting the document to a Web page. Here's how you can add a ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Deriving High and Low Non-Zero Values

When analyzing your numeric data, you may need to figure out the largest and smallest numbers in a set of values. If you ...

Discover More

Checking for Duplicate Rows Based on a Range of Columns

When working with data in Excel, you might want to figure out which rows of data represent duplicates of other rows. If ...

Discover More

Calculating the Median Age of a Group of People

Suppose you have a worksheet that contains a list of ages and then a count of people who correspond with those ages. You ...

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}] (all 7 characters, in the sequence shown) 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 three more than 1?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


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.