Display and highlight all worksheet formulas

- [Voiceover] In this workbook 02-TipsShortcuts, we're looking at a sheet called 'locate formulas'; and that's what we'd like to do, in this particular worksheet. It's a bit of a mess, it's not laid out well. We'd like to know where the formulas are; We'd also like to be able to see the formulas. If you want to see formulas quickly, there's a keystroke shortcut. It's control tilde; now, not everybody's familiar with that word, but in cell C1, I've depicted what's, on many keyboards, typically in the upper left-hand corner, a key that's got the tilde symbol on top, that's that character that appears above the letter N in certain Spanish words.

I believe it's also used in Portuguese above certain vowels. The symbol below it is called the accent grave, that's from the French, it's typically used over the letter E. I think that's the only combination there. Without using the shift key, but definitely using the control key, press control tilde and watch the screen. All columns widths are doubled, but more important than that is the idea that wherever there's a formula, for example in cell B7, we see the formula. And also in cell B19 and cell D13, and so on.

If you press that combination again, control tilde, we're back to normal. Real fast, real easy, doesn't change the content. Identically, same feature is found on the formulas tab in the ribbon, called 'show formulas'. It doesn't give us the keystroke shortcut but we can certainly click that rapidly, or click it again. The active cell can be anywhere in the worksheet when you use the feature, as it is with the keystroke shortcut, control tilde. And by the way, when it is looking like this, if you'd like to print this, you might wanna click in the upper left corner, double-click any column boundary between the letters, and possibly print the worksheet in this format.

If I press command P right now, we'll get a brief look at what the preview is. It isn't looking so good here so we might need to manipulate that a bit, but we certainly could print the worksheet in this layout, use it as a source of documentation. I'm going to press control tilde again, and then readjust the column widths again, clicking the upper left corner, double-clicking any column boundary between the letters. So a handy feature. A command sequence that's really helpful is the idea that we want to highlight just the cells that have formulas. Be sure you click on a single cell before using this feature.

This command sequence is found from the edit menu, top of the screen, 'edit', 'find', 'go to'; this brings us to this dialogue box, and click 'special' and we'll say 'formulas'. Click OK, all of the formula cells are highlighted. Now, if you'd like to remember which ones are highlighted, don't click anywhere within the worksheet, go to the home tab in the ribbon, and consider applying a background color. Be sure and use a light color, because the text you want to be able to see through it. I think I'll use blue here.

So we know, at a glance, where the formula cells are. Now the feature's not dynamic; if I erase this cell here, it stays blue, if I write a new formula over here, it doesn't turn blue. So you might have to repeat the feature at a later time if you want this to be truly up to date. I don't really want to make those changes, I'll press command Z a few times. There we go, we're back to this display. Now, let me press command Z again, to actually un-apply the color, so to speak. Another way to get to that command sequence a bit faster is to begin by pressing the F5 key, the function key F5, which means 'go to'.

Then click 'special', then click 'formulas', then OK. And a third way to get there is by way of another keystroke shortcut, control G, that means 'go to', 'special', 'formulas'. So we've highlighted all the formula cells, and possibly we would apply the color as we showed you earlier. And by the way, a companion technique to this, although not necessary, could be - this time we use F5 or control G - 'special'; this time consider activating 'constants', and then down below, un-check the box for errors, logicals and text, and now we're about to highlight just the number cells, the number cells that do not contain formulas.

Click OK. So possibly we could apply a color to those too, maybe a different color of course, if we were using the formula color application as well. So, two major techniques here for identifying formulas in a worksheet. One actually displays the formulas, remember it's control tilde, and that's a toggle, control tilde takes us back to normal; remember the companion to that; remember the identical features found on the formulas tab in the ribbon, 'show formulas', click it again, back to normal. And the other feature, starting with the edit menu or the keystroke shortcut - 'edit', 'find', 'go to'.

Remember we could press F5, to the same place, 'special', 'formulas', highlight all the formula cells. And possibly we would've had a color as well. So two features that allow us to identify formulas in worksheets.