If your Microsoft Office Suite updated to the latest version [for me this appeared on Version 2006 (Build 13001.20384 Click-to-Run); you can find your version under File -> Account -> About Excel section], you may have noticed a difference on Columns having Numbers instead of Letters.
Microsoft has changed the default referencing style for Excel in their latest updates. This new style Microsoft refers to as “R1C1 referencing style” (R1C1 stands for Row 1 Column 1). The main area this impacts by changing the default referencing style is when dealing with Excel Formulas. Below is an example of the same two formulas, one is the old way and the other the new way.
Old Excel Formulas:
= SUM(AD2:AD251)
= SUM(Q2:Q251)/(SUM(I2:I251) + SUM(K2:K251) + SUM(N2:N251))
New Excel Formulas:
= SUM(R[-251]C:R[-2]C)
= SUM(R[-251]C[-14]:R[-2]C[-14])/(SUM(R[-251]C[-22]:R[-2]C[-22]) + SUM(R[-251]C[-20]:R[-2]C[-20]) + SUM(R[-251]C[-17]:R[-2]C[-17]))
The first formula is a basic summarization of a column and the second is the formula for calculating Sell Thru %. The new way references the rows and columns by the relative position of the current cell instead of specifying the Column and Row Numbers (kind of like a bottom-up approach instead of the top-down). If you always use the formula wizards in Excel (like Auto Sum button) and never write your own formulas, you might not even know the difference. But for someone like me that writes my own formulas to do calculations, pivots, data validation, etc.; the new way takes some getting used too.
I’ve tested this new formula across existing documents that have formulas in them. Excel automatically converted it to the new style and ran without issue. So there shouldn’t be any issue with using the new style with your older documents.
How Do You Change the Formula Style
The good news is you can change the default formula-style the way you prefer in Excel.
In Excel go to: File -> Options -> Formulas Tab -> Working with formulas section -> Un-check R1C1 reference style then click Ok at the bottom and everything goes back to the way it was.
Below is a screenshot for a visual reference of where to change this setting and what to look for on your own computer if you prefer the old formula style.
We are always looking for ways to improve our products to help you run your business more efficiently. To learn more about how our experienced team can grow your business, or to schedule a free demo Contact Us.
