- Let's face it, This worksheet is a bit of a mess.
Let's imagine that someone was working on it,
had to leave the organization,
it's kind of fallen into your lap.
You're trying to figure out what's going on.
Where are the formulas?
There are two basic tips that you can use at anytime
when you're working with a worksheet in Excel.
If you'd like to see all of the formulas at once,
simply press a keystroke shortcut,
or use a command available
on the Formulas tab up in the ribbon.
Let's talk about the command first.
On that formulas tab,
there's a group to the right called Formula Auditing.
Show Formulas, and notice a pop-up below
tells you the keystroke shortcut.
We'll get to that momentarily.
Show Formulas, what happens?
All column widths have been doubled,
but more important and that is,
wherever there's a formula, we see it.
For example, in cell B7,
and in B19, and a bunch of others as well too.
There's also one in D13, and even though the
column width has been doubled,
and that gives us a better opportunity to see most formulas,
we're not seeing all of it here.
But at least we have a sense of what's
going on in this worksheet.
You might scroll around and look at some of the others here.
Sometimes this is going to be a bit overwhelming.
That command, Show Formulas, is what we
sometimes refer to as a "toggle command".
Let's click it again, Show Formulas,
we're right back to normal.
So at different times you can use that,
it does no damage whatsoever
or alter, in any way, the content.
It simply doubles the width of columns and it
exposes formulas and it displays those,
instead of the results.
Now, in the upper left corner of this
worksheet in cell A1,
is a depiction of the key that we're talking about
that you can use.
And I always ask people in live sessions,
"What do you call this key?"
And it doesn't really have a standard name.
The symbol on top is often called tilda,
in Spanish, above the letter N in certain words,
and I believe it's used in Portuguese and
perhaps some other languages as well.
The keystroke shortcut does not involve the shift key.
It's the Ctrl key with this particular key on your
keyboard, I'll call it tilda.
So what happens when we press Ctrl-tilda?
It's faster than using the command,
but it's equivalent to that Show Formulas button that we saw
earlier up on the Formulas tab.
I use this all the time when I'm working with worksheets,
even when I'm familiar with the data, pretty much,
it's just a quick reminder for the formulas.
Now, it doesn't highlight them, and so you do have to
look around for them.
A companion to this, but a completely different feature,
is based on a command found on the
Home tab in the ribbon.
Be sure to click on a single cell before using this feature.
From time to time in Excel,
you may be familiar with certain commands,
where if you've got some cells highlighted,
and you activate the command,
Excel only looks within that range.
In a counter-intuitive way, if you click on a single cell,
often Excel will look throughout the worksheet.
And that's the way this particular feature works.
So after selecting a single cell,
and it can be any cell in the worksheet,
then on the Home tab,
off to the right, Find and Select.
Click the option Formulas,
all formula cells are highlighted.
And as a follow-up to this,
you might want to consider adding color.
If you do add color to the background here,
make sure it's a light color so you can
see the text through it. So I can scroll up and down,
as long as I haven't clicked on any cell,
these remain highlighted. These are cells with formulas.
So over in the Font group on the Home tab,
we can click the drop arrow for the bucket here.
Choose a light color, and of course we get a preview too,
and we can use any one we want.
Of course, I'll just use this color here.
As a reminder as to which cells have formulas.
Now at certain points, I might delete a cell.
For example, if I delete cell G5,
it retains that color.
If I add a new formula,
it doesn't automatically have a color background.
So from time to time, if it's a very active worksheet,
you might want to eliminate all the color,
reapply the feature again, and then add color.
So once again, a quick reminder here,
anytime you're trying to
track down formulas within a worksheet,
remember that keystroke shortcut is Ctrl-tilda,
or by way of the Formulas tab, the Show Formulas button.
Both cases, a toggle switch.
Or the feature that allows us to
highlight cells that do have formulas.
As a companion to this, if you were interested in
highlighting cells that just have
numbers that are not formulas,
you could begin the process by clicking a single cell,
going to the Home tab,
back to Find and Select on the right side,
And this time go to Special.
And in the pop-up dialog box, go to Special.
You can choose Constants and then uncheck the box for
text in the box for Logicals, and the one for Errors.
Click Okay, and now,
we are selecting and highlighting only cells that
have pure numbers. So there too,
you might want to apply color.
So we've got different techniques for finding formulas,
and when necessary, even highlighting cells that
have just numbers as well.
(chill outro music)