Displaying Formulas
The preceding article is good for checking formulas one at a time. Sometimes
you need to look at the whole sheet at one time to find which cells contain
formulas and which contain values. To see all of the formulas on a spreadsheet
go to Tools > Options… View tab as seen below.
Click on the Formulas checkbox shown and click OK.
Be prepared for the sight that awaits! It may not be pretty.
To get this display requires 4 or 5 clicks of a mouse, and 4 or 5 to get
back! It would be nice to have a button on the toolbar to toggle back forth
between formula view and normal view.
We’ll need a macro that turns formula view on or off and then we’ll
assign it to a button.
Go to Tools > Macro > Record New Macro… and fill out the dialog box
as it appears below.
Click OK and you are recording! Now go to Tools > Options… View tab as
you did above and check or uncheck (it doesn’t matter) the Formulas tab and
click OK. Then stop the Macro Recorder.
We have the basics of our ToggleFormulas macro, but it needs a little
tweaking.
Use the <ALT><F11> shortcut key to go into the Visual Basic
Editor. You should see the following code:
ActiveWindow.DisplayFormulas = True
What we want however, is not something that will always turn Formulas on, but
something that will toggle them. That means whatever the value is now, we set
the value to its opposite. Change the code you see to this:
ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas
Save the macro and exit the VB Editor. You should be back in Excel. See the
next page for adding a custom button to your toolbar.
|
|