Thursday, March 22, 2012
Have you ever Inherited an Excel workbook that simply didn’t make sense? As we discussed in this blog a couple of years ago, problems inevitably happen in Excel. One of the most common issues is, of course, the use of formulas (and, let’s face it, the peculiar way some users set up his or her workbooks).
Troubleshooting is, without question, a Valuable Skill in Excel!
An easy way to identify the cells with formulas (and display them) is to hold down the Ctrl button and press ~. When you press this keyboard combination, all of your formulas will appear in their cells (pressing the combination again will bring back your original view). That is a good start for determining what is going on “behind the scenes”.
Of course the foregoing does not solve all of your investigative challenges. The Vexing Problem can be trying to determine from where a formula is drawing its information. Excel contains an Elegant Tool for clicking on the formula cell and tracing its Precedents. For Excel 2003, go to Tools / Formula Auditing. For Excel 2007 and 2010, go to Formulas / Formula Auditing.
Clicking on Trace Precedents will give you a Graphical Illustration with Arrows that show you from where your information is being derived.
Ctrl + ~ and Trace Precedents are two excellent tools that can help you figure out those cryptic Excel workbooks that you inherit from others. Such a good thing…