Let’s get this out of the way: Your spreadsheets are full of errors.

In an analysis of multiple studies dating back to 2008, Marketwatch reported last year that almost 90% of Microsoft Excel Spreadsheets contain errors. Even when created with the utmost care, the number and complexity of the formulas contained in our spreadsheets create significant opportunities for bad data. With about 1 Billion users of Microsoft Office users in the world, the absolute number of errors that potentially exist range in the hundreds of millions.

We have some thoughts on how to prevent errors in Excel spreadsheets. Here are three of them:

 

Most Errors are Caused by Bad Calculations: Check Your Formulas

Methods of testing your formulas range from the simple to the absurdly complex. Let’s ignore the stuff on the right side of the spectrum, and stick to what we can do right now. Did you know that highlighting a cell that contains a formula and pressing “Ctrl + [“  will reveal the cells that feed into the total? It’s a simple yet effective way to understand your data sources and identify what you missed, and what might have been double-counted. Simple stuff.

A better way to simplify your formulas and guard against skipping cells is to use Range Names. Range Names group like cells together by some logic you decide. So rather than adding together the monthly sales in cells M5-M100, you can instead identify those cells by a name (say, MONTHSALES). Any data inserted between the first and last rows will be counted, your formulas will be easier to understand without all of those alphanumeric characters, and any errors will be much more obvious.

Finally, use the “Trace Precedents” and “Show Dependents” functions in the Formulas tab for a visual representation of the values used to populate a sum, average, or other formula. This won’t help you is the values themselves are bad, but it’ll help you visualize the flow of data into a destination cell and make any extraneous data obvious.

 

Validate Your Input Values

You can use Excel’s Data Validation function to reduce the likelihood of bad data. This feature allows you to specify the conditions a value needs to meet and reject any values that don’t meet your conditions. If you know, for example, the minimum and maximum values of your data, simply instruct Excel to highlight values that fall outside of that range. To take it a step further, create a drop-down list of values in an adjacent workbook, then use those to populate your active one. You’ll limit your potential for error by choosing from an already known list of values. No fat finger errors here.

 

Automate the Whole Thing

We’re biased, but the easiest and best way to prevent errors in Excel is to automate your work. Automation software like Foxtrot Desktop works within and between your spreadsheets and other sources of data, like websites, to import, manipulate, copy, paste, and format data however you wish. Foxtrot logs critical process information, sorts, and transforms data just like you would – through the presentation layer of the application. Unlike you (no offense), Foxtrot works without errors. Foxtrot is the ideal solution for CPAs or other businesses who use Excel every day for multiple, repetitive tasks.

Just “teach” Foxtrot what you want it to do with our simple drag and drop interface and scripting technology. Ready-made Excel functions add rows, save workbooks, and handle a host of other routine actions.  Then kick back and let Foxtrot handle all of your reporting and data managing for you, while you go enjoy a lemonade (or similar refreshing beverage).

Would you like to see a video of Foxtrot working with Excel? Head on over to our videos page and see how easy it can be to automate Excel without coding or programming.