Forum Discussion
Spreadsheet integrity review
I have an Excel workbook with 15 sheets, 8334 cells with data, and 5851 formulas.
Objective: Perform an integrity review, i.e. reviewing a spreadsheet to ensure that its data, formulas, and overall structure are accurate, reliable, and free from errors or inconsistencies.
For example, to perform tests such as:
Check Data Integrity
Validate Data Entries to ensure that all data entries are accurate, complete, and in the correct format, e.g. by looking for:
- Inconsistent data types (e.g., mixing text with numbers in a column).
- Duplicate entries or missing data.
- Data that doesn’t make logical sense (e.g., negative values where only positives are expected).
Apply data validation rules to ensure that data inputs are within expected ranges and formats.
Review Formulas & Calculations
Check for Formula Accuracy to verify that all formulas are correct and reference the correct cells, by looking for common errors such as:
- Incorrect cell references (e.g., referencing the wrong sheet or row).
- Use of hard-coded numbers instead of cell references, which can lead to errors when data changes.
- Ensure there are no unintended circular references, as these can create errors or misleading results.
Test Error Handling
- Look for Error Values: Review the spreadsheet for any error messages (e.g., #DIV/0!, #VALUE!, #REF!). Investigate and resolve any errors found.
Is there a way (add-in, tool, macro) that has been purpose-built to perform such tests?
Patrick2788 That was very helpful; it worked. Thank you.
- Patrick2788Silver ContributorI recommend trying Inquire's Workbook Analysis.
Here's some details on how to enable it (It's a Microsoft add-in) :
https://support.microsoft.com/en-us/office/analyze-a-workbook-with-spreadsheet-inquire-5991e8fa-f1c1-401a-ae3f-469384ae3e3b#:~:text=Start%20the%20workbook%20analysis%3A%201%20In%20Excel%2C%20click,range%20of%20cells%2C%20data%20connections%2C%20formulas%2C%20or%20errors.- constantinoskCopper Contributor
Patrick2788 That was very helpful; it worked. Thank you.
- Patrick2788Silver ContributorYou're welcome!