We found a worrying bug in Excel 2013 when inserting rows into an existing worksheet.
While investigating the Excel ‘coding error’ in an economics paper we uncovered a worrying bug in Excel.
It occurs in our testing of Excel 2013 in the cell ranges of formulas like SUM and AVERAGE at the bottom of columns of values.
When the columns contain a mix of numbers and text values (like ‘n/a’ for a value that’s not available) then the cell ranges at the bottom aren’t consistent when you add a row to the list.
To add trouble the Audit features in Excel, that are supposed to detect these kinds of inconsistent cell ranges doesn’t always work.
So far we’ve tested Excel 2013 and Excel 2010, we’ll look at earlier versions soon.
The bug
Let’s start with a simplified version of the worksheet that started all the trouble.
Note that column B and E have text cells with ‘n.a.’ instead of numeric values. That was done in the original worksheet and is a common way to deal with ‘known unknowns’ (as Donald Rumsfeld would say). The text cells will affect the results of SUM or AVERAGE but that’s not the concern here. The bug is in the cell ranges (e.g B2:B5, C2:C5 etc) in row 6.
In row 7 we’re showing the formulas used in row 6 using the very handy FORMULATEXT() feature in Excel 2013.
If you insert a row at the bottom of the list the cell ranges in the AVERAGE row become inconsistent:
Columns B and E are unchanged at ranges 2-5 while columns C and D have been correctly updated to include rows 2-6.
We’re not sure why Excel gets this wrong but it’s probably something to do with the text cells in the columns. Columns C and D that are updated have all numeric cells.
Here’s the bug in action:
Where’s the auditing?
The sharp eyed among you may have already noticed Excel’s other bug which compounds the problem.
The Auditing features in recent versions of Excel should detect and highlight the inconsistent cell ranges. But it doesn’t always work, as in the example above.
Most likely the Audit feature doesn’t kick in because there’s two adjacent cells (C7 and D7) with matching ranges. Excel doesn’t look ‘wider’ to see that the row of cells have inconsistent ranges.
Microsoft will probably use one of their standard excuses here; “by design”. Meaning that the problem is working the way Microsoft intended, not the way that customer expect.
Sometimes it does work
Excel can get it right. If you insert a row in the middle of the table (as you might to maintain some order to the list) then the formulas in row 7 are all updated correctly.
Microsoft, as usual, will argue that this is an unlikely situation but it’s not really. Adding a row to an existing list happens quite often and it’s also fairly common to have unknown values (only in a perfect world is there a reliable statistic for all situations).
