Excel Detective Work

Doing a bit of Detective Work is sometimes necessary in Excel.  For instance, you may have inherited an Excel workbook from a former coworker, wish to investigate the techniques used by a current colleague, or merely refresh your memory on how you created a workbook in the now fuzzy past.

Whereas there are specialized commercial tools for doing hardcore fraud auditing on Excel worksheets, most of us simply need a Couple of Techniques that will provide a glimpse into what is going on in the background of our spreadsheets:

1)  Are there Formulas Employed in My Workbook?

Rather than manually checking each cell or range to reveal the formula in the Formula Bar, (as used to be the case in some of the older versions of Excel), you can reveal all of this information in your entire worksheet with the Keyboard Shortcut:

Ctrl + ~

By selecting any cell within your worksheet and then using this simple keyboard combination, All of the formulas in All of your cells will be revealed.  You can then proceed to investigate further to see if there are any flaws in their construction.

2)  In-Depth Review of Precedents and Dependents

If you need an Omnipotent View of what your data is doing, where it is going, and where it is coming from, try using the tools in the Formula Auditing group on the Formulas tab. The tools include Trace Precedents, Trace Dependents, Show Formulas, Error Checking and more.

One of the great advantages of using the Formula Auditing tools is the fact that the information is returned in Easily-Understood Graphics that help you make Quick Sense of what is going on in your workbook.  Once armed with this information, you can proceed to work the magic that has earned you the Office Excel Guru alias.

Some simply applied tools to help you do your Sherlock Holmes work in Excel.  And it’s all quite Elementary, my dear Watson…