Excel bug: Row Insertion and cell ranges

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.

Excel - row insertion bug - original image from Excel bug: Row Insertion and cell ranges at Office-Watch.com

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.

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.

If you insert a row at the bottom of the list the cell ranges in the AVERAGE row become inconsistent:

Excel - row insertion bug - insert at bottom image from Excel bug: Row Insertion and cell ranges at Office-Watch.com

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:

Excel - row insertion bug - animation image from Excel bug: Row Insertion and cell ranges at Office-Watch.com


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.

Excel - row insertion bug - insert in middle image from Excel bug: Row Insertion and cell ranges at Office-Watch.com

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).