Checking for data accuracy is tricky in Excel. In this blog, I have been presenting some of my tips. This is a great one for more visually-oriented users.
We have a simple inventory model below. Even in this simple model, there are many formulas, dependent cells, and inputs. How is the shrink calculated? Did someone hard-code the receipts in Month 4?
This tool is a great start in looking under the hood at this worksheet. On the home ribbon, go to Find & Select, then Go to Special. You will see the below menu.
This tool will highlight your cursor over any cell in your worksheet that has the prescribed criteria. You can use it to see all the blank cells, all the constant cells, and more. We want to return all the cells that are formulas. This will help us understand which cells are calculations in our model. Select formulas and hit OK.
Excel will have all of the cells highlighted that are formulas. Leave the cursor where it is and highlight them bright yellow using the fill function.
Now you know that all but three cells are formulas in the spreadsheet.
This is a tool I use often when analyzing how an Excel file is built.
Reblogged this on SutoCom Solutions.