Avoiding Errors in Excel Spreadsheets

We trust our spreadsheets until they let us down. Then, we faithfully check all the values and formulas to find the error which is easier (or harder) to discover depending on the complexity of the spreadsheet, the importance of the information it contains, or how much time we don’t have to troubleshoot the problem. Undiscovered mistakes lurk in every spreadsheet.

Cautionary tales about misadventures with Excel spreadsheets abound. Whether we find our errors using a computer-assisted audit tool or we check a spreadsheet line for line, we seek perfection.

To help achieve perfection and save time in the future, here are several tips to help avoid errors and time lost in troubleshooting spreadsheets. This list was compiled using Excel 2002.

Level 1

Most spreadsheet errors are a result of not understanding the business issues or other underlying purpose of the spreadsheet. Three questions to ask are:

  • What is the business issue modeled in the spreadsheet?
  • Are there any design details that you may not understand that may help you prevent problems later?
  • Can you explain how the model works to others?

Level 2

The complexity of calculations used in any spreadsheet varies. Understanding source data is also a concern. Three questions identifying errors at this level are:

  • Are the correct formulas and functions being used?
  • Are you using the correct interest or tax rates?
  • Are other source values being called correctly in your calculations?

Level 3

Some companies have a policy of not using built-in functions to limit calculation problems, however, it can be difficult to disable automatic functions, especially when importing data from spreadsheets created by others. There are several financial functions built into Excel 2002 that may not fit your needs. For example, a limitation of the Excel function ACCRINT (accrued interest for a security that pays periodic interest) is that it is designed exclusively using US accounting rules. If you are calculating for a foreign security using this function, you may want to consider building your own calculations to ensure accurate calculations. Three questions to help determine if errors occur within automatic or custom calculations are:

  • Do you understand how the functions you intend to use in your calculations work?
  • Do you need to build your own calculations to ensure an accurate, problem-free spreadsheet?
  • If you built your own calculation, did you test it comprehensively?

Level 4

User error is a possibility even for the best of us. Unintentional formatting can prevent a spreadsheet from calculating correctly because Excel cannot perform math on numbers formatted as text. Protecting your spreadsheet with a password may be helpful in keeping track of any changes to its content. Four questions identifying formatting errors are:

  • Have you formatted any numbers as text?
  • Are any formulas overwritten with a number?
  • Have you password protected the spreadsheet before passing it onto your users?
  • Is the password kept in a place where you can find it again?

We all know that perfection is realistically impossible but it is reasonably closer if you have considered these items. A spreadsheet is only as good as the time taken to check it for errors.

You may like these other stories...

Event Date: August 28, 2014, 2 pm ET Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to...
When you’re running an accounting office, it's easy to become inundated with paper, forms, and email attachments, especially when tax season rolls around. To prevent your office from becoming completely overwhelmed...
It's not a reality—yet—but accounting software is poised to eliminate accountants. We are at a tipping point for many similar professions: online education replacing professors, legal software replacing...

Upcoming CPE Webinars

Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.