Technology

# Even Microsoft Needs Help Avoiding Spreadsheet Errors

David Yanofsky was riding the New York City subway when he noticed an ad for the new Surface tablet in the Spring Street station. The ad showed a b...
Bill JelenNovember 7, 2013

David Yanofsky was riding the New York City subway when he noticed an ad for the new Surface tablet in the Spring Street station. The ad showed a budgeting spreadsheet used in planning a trip to Maui running in Excel 2013 on the Surface tablet. The budget has seven line items, including ones for air, hotel, car, surfing and diving. Unfortunately, the total at the bottom was wrong, as Yanofsky’s photo on Quartz.com shows.

How did it happen?

If you were creating an Excel worksheet and had to total five numbers, an Excel rookie might use a formula of =B1+B2+B3+B4+B5 as shown here:

Excel pros would instead use the AutoSum button and end up with =SUM(B1:B5).

Both return an answer of \$9000, so the spreadsheet rookie might argue that the original answer is correct and just as good as using the AutoSum button.

Although both methods return the same answer, the AutoSum is the right way to do this. Excel has smarts built in that make sure the erroneous answer in the ad will not happen if you use AutoSum. When you insert new rows in the budget, even at the end of the budget, the AutoSum formula will automatically adjust. As soon as you type in a number in B6, the Total formula becomes =SUM(B1:B6)

Fill in the number in B7 and the total adjusts again:

These adjustments only happen if you use the SUM version of the formula. If you used the original formula and add new rows, even rows in the middle of the budget, the formula will never adjust, and you will end up with the wrong result shown in the Microsoft ad.

Crazy things happen when marketing people try to use Excel. This is just the latest example, one in which Microsoft’s ad agency produced an interim result that “looked” OK, but could not adapt.

Bill Jelen is a contributing editor and the host of MrExcel.com. He points out that his book, “Don’t Fear the Spreadsheet,” warns against this exact type of formula.