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

Clearing All Tabs in a Document

Need to get rid of all the tab stops in a particular document? It's easy to do when you apply the technique outlined in ...

Discover More

Reversing Type

Most text appears black on white, not white on black. If you want to change this so that your type is reversed, here's a ...

Discover More

Forcing a Worksheet to be Protected Again

Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Extracting First and Last Words

When working with text phrases stored in cells, it might be helpful to be able to extract words from the phrase. In this ...

Discover More

Combining Cell Contents

Excel allows you to easily combine text together. Interestingly, it provides two ways you can perform such combinations. ...

Discover More

Formula Shows Instead of Formula Result

When you enter a formula in a cell, you expect Excel to display the result of that formula once you are completed. If ...

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

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.