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

Transferring Data between Worksheets Using a Macro

Macros can be used for all sorts of data processing needs. One need that is fairly common is the need to move data from ...

Discover More

Rejecting Changes in a Document

When a group of people edits a document with Track Changes turned on, it can be tempting for one of the editors to use ...

Discover More

Tying a Named Range to a Value Instead of a Cell

Named ranges are great, but they don't move when you sort data. If you want them to move, it is helpful to remember that ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Only Showing the Maximum of Multiple Iterations

When you recalculate a worksheet, you can determine the maximum of a range of values. Over time, as those values change, ...

Discover More

Summing Based on Part of the Information in a Cell

Excel provides a variety of tools that allow you to perform operations on your data based upon the characteristics of ...

Discover More

Summing Only the Largest Portion of a Range

Given a range of cells, you may at some time want to calculate the sum of only the largest values in that range. Here is ...

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 six minus 3?

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.