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...

No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...
 Event Date: April 24, 2014 In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel. David will introduce the Macro Recorder, which transforms actions...

Upcoming CPE Webinars

Apr 17
In this exciting presentation Excel expert David H. Ringstrom, CPA shares tricks that you can use with pivot tables every day. Remember, either you work Excel, or it works you!
Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.