Join Dennis Taylor for an in-depth discussion in this video Display and highlight formulas, part of Excel: Advanced Formulas and Functions.
- [Instructor] In our chapter one file called Tips and Shortcuts, the very first sheet is called Find Formulas. 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 any time when you're working with a worksheet in Excel. If you'd like to see all of the formulas at once, simply press the 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 that is a pop up that tells you the keystroke shortcut, we'll get to that momentarily. Show Formulas, what happens? All column widths have been doubled, but more important than that is, wherever there is 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. I'd 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, 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 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?" That doesn't really have a standard name. The symbol on top is often called tilde, 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, its the Control key with this particular key on your keyboard. I'll call it tilde, so what happens when we press Control + tilde, it's faster than using the command, but it's equivalent to that Show Formula's button that we saw earlier up on the Formula's 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 of where are the formulas. Now, it doesn't highlight them, 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 on 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, then you activate the command, Excel only looks within that range. And 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, that 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. 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, it's Control + tilde, or by way of the Formula's tab, the Show Formula's button. Both cases will toggle switch, or the feature that allows us to highlight cells that do have formulas. As a companion to this, if you're 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, and the box for Logicals, and the one for Errors, click OK, and now, we are selecting and highlighting only cells that have pure numbers. So there too you might want to apply a color. So we've got different techniques for finding formulas, and when necessary, even highlighting cells that have just numbers as well.
- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information