When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding complex formula in Excel spreadsheets that tend to result in errors.
It is useful to have an add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
Finding the complex formula in Excel Spreadsheets
Once you have targeted the risks associated with specific functions and formulas in Excel, it is useful just to look through and see if there are any particularly complex formula.
With the Excel Auditing tool this is easier then doing it manually. As shown below, you can have a quick look over the listing of unique formula and see if you can spot any that stand out. Based on your prior steps you would have already looked at some of them, but keep in mind complexity does not mean a complex function or fancy maths.
Complex to a user can also be having to click 30 or 40 different cells and keeping your concentration enough to click on the correct cells.
With that in mind, some ideas on what formula types are risky, not due to complexity, but due to difficulty for the user to create:
- Formula with several individual cell clicks e.g. =C10 + C101 + C25 + C237 + C359 + C360 + C361 + C400 + C345 + C521 + C300 + C237+ C698 + C701 + C702 + C703 + C704 + C15
- Formula with more than 3 or more pairs of brackets e.g. =IF(F9=”month”,IF(OR(F11=$E$17,F11=$E$18),$E$19,0),$E$19*F7)
- Formulas that jump across sheets especially where there is adding AND subtracting involved.