Wednesday, February 18, 2015

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

1 comment:

Gareth Hayter said...

My new add-in, FormulaDesk , has quite a few features to make this process even easier and quicker. For example, the enhanced precedents and dependents feature has a live-view, so just clicking around the cells (or navigating by keyboard) displays the precedents and/or dependents of the active cell - no need to keep pressing a button. Plus, it can show you not just direct precedents or dependents, but all. There are other features that also make detective work easier. Please let me know of any suggestions to make it even better.