Thursday, August 5, 2010

Troubleshooting Formulas


Let’s face it, problems happen in Excel. One of the most common issues, of course, involves the use of formulas. The problem is that (this is particularly true if you were not the original creator of the workbook and have “inherited” it) that you may not know which cells have formulas and which are simply data.

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).

Another vexing problem can be determining exactly where a formula is drawing its information from. Excel contains a nifty (do people still say “nifty”) tool for clicking on the formula cell and tracing its Precedents. For Excel 2003, go to Tools / Formula Auditing. For Excel 2007, go to Formulas / Formula Auditing.


Clicking on Trace Precedents will give you a Graphical Layout with Arrows and Worksheet representations showing you from where your information is being derived.

Ctrl + ~ and Trace Precedents; two excellent tools to help you get your Excel workbook back on track.

No comments: