From the course: Excel 2016: Advanced Formulas and Functions

Displaying and highlighting formulas

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial Buy for my team

Displaying and highlighting formulas

“

- Trying to locate formulas in a worksheet is a critical task, and can be made much simpler by a great keystroke shortcut, Control ~ It's on the, and you're probably saying, what's ~? In cell C1, and I'll zoom in on it by holding down the Control key and using the mouse wheel. On most keyboards, this is found in the upper left-hand corner. The other symbol with it, by the way, is called accent grave from the French. Whatever you want to call that key, it's a great key for a shortcut. Hold down the Control key and press this. I'm going to zoom back first before doing this, because the effect here is to double the width of all columns as we see formulas. I'm pressing Control ~, no shift key involved here. All columns widths are doubled, and there's a formula there inside E5. Here's one in B7. We see these clearly. The reason the columns widths are doubled is to give us a better opportunity to see the formula, because formulas typically are a lot wider than the results. However, down in cell D13 here, once I double click, that's pretty long. It didn't help a whole lot to double the column width, but nonetheless we can see that. This is a handy tool. There is no damage: we can press Control ~ to go back to normal. It's not a bad idea when it's in this form, if you click in the upper left hand corner, double click the column boundary and actually print this. I'm going to press Control F2 for a quick print preview. We might have to work with that a little bit to make it a little bit clearer, but we can certainly print that. Notice also, I've shown the column layers and row numbers. If you're not familiar with how to do that, simply click Page Setup under the Sheet tab here. Show the gridlines, I think that would be helpful. Also, row and column headings. Certainly unnecessary here, but we see our worksheet again, back to normal, Control ~. Another viable technique is to actually highlight the cells that do have formulas. If you've got 3 or 4 cells highlighted, like with certain features in Excel, if you attempt to find the formulas, Excel will only look within those cells. So what you'll need to do is click on a single cell, and then the counter-intuitive way, if we now search for formulas, it will search the entire worksheet. On the Home tab, the far right button, Find and Select. There it is. Let's click formulas, and there we see it. These are cells that have formulas. We're not seeing the actual formulas now. Let me zoom back a little bit, but we see all the cells that do have formulas. Let's apply a color: this is certainly unnecessary. A light color tends to work best. What about that one? I'll readjust the column width also. Click in the upper left corner; double click the column boundary, and so we know at a glance which cells have formulas in them: the ones that have the blue color applied to the background. The feature is not dynamic. If I write a formula here it will not turn blue. If I erase the content of this cell, it will stay blue, so periodically you might want to remove the color and then apply the feature again. Remember, click a single cell, Home tab, far right button, Find and Select, and then Formulas. A companion to this that would take a bit longer and certainly could be useful is the idea of wanting to highlight the cells that just have pure numbers in them. For example, cell B3 is the number 66. B4 is the number .34, and so on. If you'd like to highlight those kinds of cells, click on a single cell in the worksheet, go again to the Find and Select button, far right side of the Home tab in the ribbon. This time, choose Go To Special, and if you only want to highlight the constant numbers, choose, first of all, constants, and then uncheck the box for text, logicals, and errors. We don't want to see those. We simply want to see the number cells that are constants. In other words, not formulas. Those are now highlighted. Let's apply a contrasting background color there too. Again, the background color is not a requirement, but it's going to be helpful. So we see at a glance, if a cell is yellow, it's got a number in it, not a formula. If it's blue, it's got a formula in it. Both of these are handy tools. This, and our prior example, Control ~. Now, going back to Control ~ for the moment too, you can certainly use these two together. I just pressed it and you see what happens. Back and forth, you can certainly use that. Also, Control ~ is represented in the Ribbon menu system. Go to the Formulas tab, off to the right you'll see a button called Show Formulas. By the way, before clicking, notice that the keystroke shortcut refers to the other symbol on the same key. That's certainly not incorrect, but you could easily mistake that symbol for the symbol on most keyboards that's just to the right of the letter l on the keyboards, the apostrophe symbol. Nevertheless, click this. It does the same thing as Control ~, back and forth in the same way. Two methods for using that feature. In both situations here, tracking down formulas on a worksheet is a valuable tool.

Contents