Auditing an Excel spreadsheet

Finding Unused Input Cells

This is a preview lesson

Purchase this course, or sign in if you’re already enrolled, to take this lesson.

Good spreadsheet practice suggests that if you include a cell as an input cell, it should be used somewhere in the spreadsheet. So if you have a cell with an interest rate of say 9%, it would be very suspicious if no other cell makes use of the input. Either this input has been ignored (in which case why is it still there) or some other cell is actually used to drive these numbers. Worse would be if the number is hardcoded into the formula cells i.e. you have cell with 9% but instead of looking at it, all the dependent cells have their own 9% hardcoded into themselves. Below are ideas on easy ways of finding unused input cells when auditing an Excel Spreadsheet.

Check the Trace Dependants on the input cells

In this case you need to go to each sheet and see if there are any input cells and whether they are used. You can either use the ExcelAnalyzer sheet or you can go to the original sheet and use the Formula Auditing toolbar.

ExcelAnalyzer identifies all number inputs as a light grey. So below, on all the light grey cells we can click on the ‘Trace Inputs’ button and then click on the grey cells one by one. Below, already we should be suspicious as, when tracing the dependants of cell B3 it indicates that the number is used for Product B (row 16). This might work if it is the same price.

Finding Unused Input Cells

The problem is confirmed when we click on B12 though. Note no blue lines which tells me it is not used anywhere. Based on the way the spreadsheet is set up, I would expect row 16 to use this cell. In this case the risk is not in the input cell, but rather the formulas that should be using the input cell.

Finding Unused Input Cells

We find that this risk finds spreadsheet errors very quickly.

0 of 26 lessons complete (0%)
    Your Cart
    Your cart is emptyReturn to Shop