The risks of Excel’s auto format feature

Everyone loves that way Excel can second guess what you mean when you type something into a cell and it (correctly) changes the format. Type 2 Sept and it will automatically format it as a date. But there are some users for whom this represents a major risk.

As discussed in a recent report from the Washington Post, scientists have problems representing certain genes accurately. When they try and enter the gene name, Excel may convert it into a date.

South African specific Auto format risks

Alphanumerics that start with an R

Closer to home we have a client who needs to enter the name of a product as R001. As we are based in South Africa and the currency is the Rand represented by an R in the front of the number, South African based machines assume you mean R1 (One rand).

Not only does it lose the R in the cell (you see it as the format is set to currency but in the actual cell, Excel only shows a 1), but Excel assumes you were a bit absent minded and gets rid of the 00 in the middle. This makes it very difficult to look up this product.

Refering to cells in column R

Also, if you want to refer to any cell in the R column you need to be careful how you do it. If you start your formula with a + instead of an = it gives the wrong answer.

As shown below all the cells in Q4 to Q7 were entered as +R4 etc (so NOT starting with the = sign). Instead of Excel telling me what is in cell R4, as it would if you referred to any other cell, it changes the cell to the number 4 and formats as the Rand currency.


Auto Complete Risks

Another risk is where Excel auto completes what you type based on what is in the cells above. If you don’t look at what is in the cell when you push enter, it may add some extra text to it.

So as shown below, if in the cells above there is a “Manager- Senior”, but we want to enter Manager, as you start typing Manager, Excel will suggest you use Manager- Senior.

If you type like us and watch the keyboard rather then the screen, you may push enter while not looking at the screen and Excel will assume you wanted the auto complete version.


Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.