Don’t use + to start an Excel formula

We have previously shown why, especially in South Africa, you shouldn’t start an Excel formula with a +. In a recent course we found another reason that you don’t use + to start an Excel formula. As a general rule just don’t do it. Starting with a + instead of an = was to help people who worked on other types of spreadsheets. It is not the preferred method and it causes a number of strange problems.

Excel changes the formula when started with a plus (+)

A recent finding was that it sometimes autocompletes some of your formula. So below the images is just before we clicked the enter key. Note that we started with a + (no = sign) and then it includes a *12/365*

dont start a formula with plus

When we click enter, Excel does the calculation for 12/365 and replaces it with the answer.

dont start a formula with plus

Although this is not a train smash, it is much easier to see what we are trying to do with 12/365 rather then an obscure decimal number.

This error is not consistent and cannot always be repeated, but when it does happen it always starts with a plus.

Don’t start any Excel formula with a plus sign. If you can’t help yourself, put an = sign first then type a +.

South Africa specific problem with starting a formula with a +

To show you how unreliable starting with a + is, in South Africa there is a specific issue related to South Africa’s currency which is Rands, represented by a R in front of the number.

So below, I want cell Q1 to look at the contents of cell R1 and I have done it by typing +R1 (no equals sign). When I click enter I expect to see 10 000 being the number in cell R1

dont start a formula with plus

When you click enter (this probably only happens on a computer with South African regional settings) instead of showing 10 000 it shows a 1. I think this is because in South Africa R1 means one rand and because you started with a + instead of an = sign, Excel has assumed you are entering a currency and not a formula.

dont start a formula with plus

Again, don’t start a formula with a plus sign. It may work in most cases but it is not the correct way, and if things are going to go wrong it will happen here.

Plus converts your formula’s to R1C1 format!

Another problem with starting with a plus (in case we haven’t been clear- don’t start formulas with a plus, only an equals sign), is that under certain circumstances it changes your formula into R1C1 text! To see how this happens look at the Excel changes formula to R1C1 format post.