Documenting your spreadsheet to make it easier to review the spreadsheet, but also provide an audit trail of the work done in reviewing the spreadsheet model and the outcomes and fixes to the spreadsheet errors.
If you don’t have a copy of an Excel auditing tool yet, get a free trial copy of ExcelAnalyzer.
Print the summary of the high risk items in the spreadsheet
If you hover over certain cells you are provided with an overview of where all the risks exist. For example below, by hovering over the ‘Hardcoded Formulas’ cell (1), we see a list of the numbers that appear in the spreadsheet (2).
This is useful because you can look through them and decide what to follow up on. Like it or not, sometimes the best thing to do is print it out to make it easier to work with.
To print this out you have a few options being:
- take a screenshot and print it out (useful if a small list), OR
- Right click on the cell, choose EDIT NOTE, highlight the contents, copy and paste into another sheet, OR
- Go into Print Page Setup, choose the Sheet Tab, and specify that ‘Comments and Notes’ must appear at the end of the sheet. When you print the page out, all the Comments and notes will appear at the end of the printing.
Print a listing of all unique formula
You can print out the Formulas report to document every unique formula in the spreadsheet.
In order to do this:
- Go to the Formula Report as shown below (1)
- Make sure that all the filters are off (2)
- Make sure all the groupings are expanded and rows unhidden by clicking on the little 2 (see (3) below)
- Print but make sure you look at the print preview to make sure it fits onto the pages in a readable way.
Print the model flow and interaction of the sheets with each other
You can also print out the ‘2B Model Flow’ report which shows
- all the sheets in the spreadsheet,
- how many links are going into and out of each sheets,
- which sheets are linked together, and
- which are orphans.