Excel Hardcoding Errors- Some more recent examples of excel errors

Excel Hardcoding Errors- Some more recent examples of excel errors

Another example of excel hardcoding errors caused by poor spreadsheet practice is the recent situation with regards Thomas Piketty, an economist who made some claims with regards wealth inequality, especially in the UK.

As described in this article, mistakes included finger trouble and typical formula errors. One of the highlighted errors was the use of hardcoding in some of the cells.

Hardcoding in spreadsheets means that included in a typical formula e.g. =(A1+B2) is a typed in number i.e. = (A1+B1)/2 . Although this may not look series, when you are dealing with a huge spreadsheet with thousands, if not millions of cells this can cause major headaches.

Some of the reasons this is wrong include:

• It is not easy to see what the inputs are if they are hidden in cells
• If you suddenly need to change this input e.g. from a 2 to a 3, you will need to find every cell that has this manual intervention and change it to a 3. Will you find them all? You can do a Find/ Replace but what happens if you use a 2 for something unrelated and it now changes to a 3?
• You didn’t mean to put it in, but were testing the spreadsheet to see what happens if the number is halved. The phone rings or you are distracted and you forget it is there. So your ‘testing’ has now corrupted the spreadsheet
• You included the number this month just to get it to balance. Next month you forgot where that adjustment was and did the same thing in another cell. Before you know it you have multiple cells with hardcoding and unclear reasoning as to why it was there (but I bet you had a good reason at the time!)

As much as possible you should avoid hardcoding. If you have a input, create a separate cell, add colour to the cell to show that it is an input and link all the formula to this cell. If there is a change, you can change the one cell and all your formula will correctly adjust. You can also easily track down which cells use this input.

If you really have to include a hardcoded number (and it does happen no matter what experts say or recommend), consider including a comment in the cell or better a note inside the formula using the N function.

To see what we mean have a look at our video clip on Good Spreadsheet Practice.

If this is a possible issue with your spreadsheets you should consider using a tool like Spreadsheet Professional to find all these Excel hardcoding errors (and others).