• Technology
  • CFO Magazine

Total Trouble

The older generation of Excel users remembers how potentially dangerous this Microsoft snag can be.

There’s a generation gap among Microsoft Excel users, and the younger generation is increasingly falling into a trap. It seems that the nearly instantaneous totaling function — known as automatic calculation mode — that’s used to tally columns of numbers is being mysteriously turned off. That could lead to chaos if the user is unaware of the change, predicts CFO contributing editor and noted spreadsheet expert Bill Jelen, who runs MrExcel.com.

As Jelen explains, in relatively short spreadsheets — those with, say, 12 or 15 columns or rows — it is easy to see that the auto calc function has been switched to manual because the user can see the cell that displays the total without scrolling down through the workbook. But when spreadsheets expand to more cells than will fit on a single screen, the stagnant totals may not be obvious, especially for users who don’t know they are in manual mode.

Jelen believes the mystery dates back about 15 years. In the 1990s, users were stuffing more data into spreadsheets than ever before, but computing power lagged. As a result, when users updated a spreadsheet cell with a new number, it took 40 to 50 seconds for Excel to recalculate the new totals.

So Microsoft created a manual calculation mode that could be switched on when a user was working with a large spreadsheet. It allowed workers to insert as many changes as needed, then switch back to automatic mode to crunch the totals. At the time, most Excel users knew about turning the manual mode off and on at appropriate times.

Computers then got faster, eliminating, for the most part, the need to switch to manual mode. However, Microsoft also vastly increased the capabilities of Excel, and eventually users caught up, cramming more and more data into its billions of cells. By 2010, computers and networks were once again flagging under the load.

Older users have tended to once again turn manual calculation mode off and on using the “Calculation Options” dropdown on the Formulas tab. But younger users are often not aware that when they open an Excel spreadsheet (or workbook) that is already in manual calculation mode, every other spreadsheet (or workbook) that is open or subsequently opened will automatically switch to manual mode.

Watch the lower left corner of the Excel window, counsels Jelen. If “Calculate” remains displayed, then you are most likely in manual mode. A 2007 post on its support site noted that, “In Microsoft Excel, all currently open documents use the same mode of calculation, regardless of the mode in which they have been saved.” Microsoft also confirmed to CFO that “calculation mode is a global state, so it will affect all open workbooks at the same time.”

Discuss

Your email address will not be published. Required fields are marked *