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.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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.
|
|