At the end of last year I wrote a column with regard to Microsoft’s spreadsheet program Excel. Due to the email response, (which to me at least was unexpected) it has now turned into three columns. I guess there are more of you Excel users out there than I thought.
I have continued to get email questions about Excel, so today I thought I would give you a couple of other tips about auditing cells. These are in response to reader questions. Remember to email questions to me…I thrive on them. (Well, OK, not really thrive, but I do like to get them.)
Tip #1: Sometimes you need to look at all of the formulas on a spreadsheet. You can click on each cell containing a formula and look at the top of the Formula Bar. But that takes a while and what if you miss one? In Excel versions 2007 and newer, the way to see them all at once is to navigate to the Formula Tab on the ribbon, look to the right side and find the "Formula Auditing" commands. Once there, click on the "Show Formulas" button. There all of your formulas are displayed at once. Click it again to turn them off. Now here is a really neat shortcut. Just use the "CTRL + ~" key combo. (Press and hold the Ctrl key and then tap the tilde key.) Works like a charm! You can be located anywhere in a workbook and it works every time for showing and hiding formulas.
While at the "Formula Auditing" command area, let’s look at several more features.
Tip #2: Trace Precedents and Dependents: This may be too deep for you non Excel folks, but read on, you may add a couple of new words to your cyber vocabulary. If you have a simple formula adding 1, 2 and 3 for a total of 6 (I hope you are all with me up to now at least) and something appears wrong you can poke around in the formula and figure it out. Many times it is helpful to be able to view what your formula utilizes to find an answer.
In the formula above you can click on the cell with the answer/formula in it and click "Trace Precedents." Poof, you get a little blue arrow showing you the cells used in the formula (shown on right).
To determine a dependent, click on one of the digits making up the list you are adding, say the "2". Click "Trace Dependents" and you will get an arrow showing which cells in that spreadsheet depend on the value. By using these two tools you can usually figure out why a formula is blowing up on you. To clear the arrows, click the "Remove Arrows" button.
We will finish up formula audits next week and then move on from Excel…unless I hear from you.
Also, if you are a visual learner like me, you may want to visit the DoubleClicks.info site to see screenshots of what we talked about here. (Hey, don’t worry, you are already here…have a great day!)
