Blame The Wrench Or Test Your Spreadsheets

Microsoft Excel use is ubiquitous in business, yet spreadsheets are often not treated like custom corporate software that is used to run critical processes. Software written by corporate developers are subject to strict testing procedures and best practices. Spreadsheets should be subject to identical rigours.

Excel spreadsheets are usually developed by junior analysts without formal training in modelling, testing and best practices. In reality, very few businesses may face disasters, but lots of sub-optimal decisions are being taken by organizations who rely on imprefect spreadsheets. Clearly, spreadsheets require more testing.

The Excel Formula Auditor is a game-changing add-in powered by the Ribbon Commander framework. Formulas can read from closed workbooks. Yes, you read right, formulas can be extracted from closed files. Using this unique feature you can analyze:

• Workbooks that are too large to open or recalculate on a PC with limited memory. For example a model that opens only in Excel 64-bit with 16GB RAM, could be analyzed in Office 32-bit version with 4GB RAM.
  
  
• Corrupted workbooks that may open in a particular Excel version, but not in later versions. You won't get any corruption warnings from Excel when reading closed files. Please compare the analysis results before and after the file has been repaired by Excel, as some formulas may have been removed or replaced by #N/A.
  

---

1. Analyze Files: Select one or more files to be analyzed. Multiple files can be shown on the Ribbon

2. Click any file icon to open its command menu

3. Create a comprehensive formula report for this file. Output is written into a new workbook across several tabs.

4. Create a detailed formula error report, if any errors are detected in the summary report.

5. Remove the calculation chain tree from the file. Excel will be forced to make a full calculation at file open. This feature is useful for models with complex dependencies that may take longer to recalculate than compute a new calculation. Please note that file size will be reduced, sometimes up-to 10% of original file size.

Rarely, the calculation chain tree may get corrupted. It is prudent to force Excel to rebuild it, before doing any quality assurance (QA) work.

6. Open file in Excel using the Calculation mode shown in the calculation group (optional)

---

Comprehensive Workbook Report And Error Detection

1. Total of number of formulas counted in cells, conditional formats or defined Names

2. Unique (distinct) number of formulas in A1 notation found in cells, conditional formats or defined Names.

3. Unique number of functions (built-in and UDFs) found in cells, conditional formats or defined Name formulas

4. Unique number of strings found in cells, conditional formats or defined Names. Use this list to proof-read text, correct typos, translate strings or replace hard-coded values with formulas.

5. Unique entire column references used in formulas. Caution: A large number of unnecessary column references may increase Excel's recalculation time substantially.

6. Number of #REF! errors found in formulas, which can be caused by careless precedent formula deletion. Create a formula error report to list errors per worksheet and cell. #REF! errors can be caused by the Solver add-in, which uses hidden named ranges.

Named ranges used by Solver

7. Number of #N/A errors found in formulas. Please note that although the NA() function returns the #N/A value, a valid cell formula will contain the NA() function.

 #N/A formula (not value) errors are caused predominately by file corruption or Excel's failure to correct corrupted formulas during a repair operation. Please read about how to detect corruption and repair workbooks here.

---

Tips For Building Better Models

Here are some tips to help you build robust, simple formulas and spreadsheets that recalculate faster.

• Create simple, easy to audit and maintain formulas by using IFERROR instead of ISERROR: IF(ISERROR(($E69*$F69)),"",($D69*$F69)) can be substituted by IFERROR($E69*$F69),"")
  
  The IFERROR function works faster than ISERROR on large, complex models, since the value statement doesn't have to be evaluated twice. Please read more about functions using the Functions 2013 Ribbon Commander powered free add-in.
  
  
• We have noticed that a lot of users don't add a range_lookup (optional) when using  a structured reference (Excel Table) Table_array
  
  Substitute VLOOKUP($D$3,Table1[#All],E2) for VLOOKUP($D$3,Table1[#All],E2, False) if your lookup range is not sorted or if an exact match if the required result.
  
  
• Check cell and conditional format strings for hard-coded text when building multi-lingual workbooks. The most common culprits are error messages in IFERROR functions. An English error warning may confuse a foreign language user at a time when user attention to a problem is of critical importance.
  

---

download

Download

Click button to download the FREE Excel Formula Auditor Add-in for Excel 2007 or later

VBA Add-in powered by the Ribbon Commander Framework

---

comments powered by Disqus

---

Home  »   Free Add-ins  »   Excel Formula Auditor Add-in