88% of spreadsheets have errors
In a recent article on MarketWatch.com (88% of spreadsheets have errors – by Jeremy Olshan http://ow.ly/l4zSZ) it was reported that almost 90% of spreadsheets being used throughout businesses, schools, and in the home, contain errors. Errors which may simply cause one to miscalculate the family budget, or at worst, cause a business to make poor decisions due to erroneous spreadsheet results. A 2008 analysis of multiple studies showed that spreadsheets contained errors in 1% or more of formula cells. And given that there are over 1 billion Office users worldwide, that can add up to quite a few miscalculations.
As a Microsoft Office Master Instructor and consultant, I’ve seen my fair share of problematic spreadsheets. And though there may be many reasons why a formula in a spreadsheet may contain a design flaw, especially in large spreadsheets containing several complex formulas, there are a few very common mistakes that users make that can cause erroneous results – mistakes that can be easily prevented. In fact, for a more comprehensive error checking evaluation of your spreadsheet, you may want to look into a certification program designed to ensure best practices are followed when creating a spreadsheet, (for example, www.spreadsheetsafe.com.) With that said, I’d like to share with you some of the more simple tricks you can employ to avoid or detect common errors that can occur in spreadsheet formulas.
Numbers Formatted as Text
A common issue that comes up in miscalculations is there is a combination of number and text formats combined in the same column. Simply put, summary functions, such as SUM or AVERAGE, will ignore text based data. And thus, if you are summarizing values with these functions, and the values are incorrectly formatted as text, the functions will simply ignore the value when summarizing. In such situations, you’ll need to reformat your data values as numbers to ensure the correct results. Luckily in this case, Excel has a built in feature that will bring such inconsistencies in formatting to your attention… little green triangles.
Possible Formula Error Notification
Possible Formula Error Notifications, better known as Little Green Triangles, are alerts that appear within the upper left corner of the cell, and are used to notify you of possible errors in your formulas when Excel picks up inconsistencies in the data used in formulas or the formulas themselves. When you see these “green triangle” alerts, click the cell to bring up the icon, and then click the icon to read the alert. They can always be ignored or turned off in the options, should you not need to heed the warning, but until then they may spot possible errors in your worksheet’s design and formatting that otherwise would have gone unnoticed. Figure 1 below shows two examples of the Possible Formula Error alert: one for a cell containing a number stored as text, and the other showing a formula that is inconsistent when compared to other formulas of that same row or column.
Using the Trace Precedents Command
Sometimes, if we’re lucky, we may spot a possible error by the results we get. In other words, if the results are obvious Excel errors, such as #Value or #N/A, then it’s easy to see we have a problem. Of if we still get results, but those results are so far off that it is obvious we have miscalculated something in our formula, we know to begin troubleshooting. With that said, however, it may be tricky to determine from which cells a formula gets its values; knowing that can very well lead you to the root of your miscalculation.
To determine where a formula gets its data from, select any cell which contains a formula, and then click the Trace Precedents command button, located in the Formula Auditing group of the Formulas Ribbon.
The Tracer Arrows will lead you up the path of the formula, indicating cells that are being referenced in the formula, contributing to the formula’s (possibly incorrect) results! Figure 2, below, illustrates that cells being used by a formula in cell E5¸which results in “$0.00” due to the incorrectly referencing cell E2 when it should be referencing cell E1, is inaccurate and is called out with the Trace Precedents command arrows.