Join Dennis Taylor for an in-depth discussion in this video Displaying and highlighting formulas, part of Excel 2013: Advanced Formulas and Functions.
If you're trying to make sense out of a worksheet that has formulas in it. It's going to be really handy if you can see all of the formulas at once. Now, we can certainly see that there's a formula in cell b7. Simply click on the cell, we see that in the formula bar. Of course, we can double click a cell too to see a formula within a cell. But we might want to see all the formulas at once. There's no standard way to do this, but there is a key stroke shortcut and also a menu command that will allow you to get to this capability quickly. In cell C1 I've depicted what's on one of the keys typically found in the upper left corner of most keyboards. The symbol on top is called tilde.
The one below it is usually referred to as accent grave from the French. But as a keystroke shortcut don't use the shift key but do use the control key and press that key on your keyboard, Ctrl+` I'll call it. And what happens? All column widths have been doubled and wherever there's a formula we see the formula. For example in cell E5. Also in cell D13. If you press Ctrl+` again, we're back to our normal displays. There are times and I use this frequently when I'm doing troubleshooting on certain worksheets.
Ctrl+` doubles the width of columns as it exposes formulas or it takes us back to normal. And the reason this feature doubles the width of columns is to give us a better opportunity to see the formula. Still, it isn't foolproof, though. If you notice that in cell D13, which is currently selected, we still can't see the entire formula here. Which you might also want to consider doing in these situations. Particularly if you know the worksheet is valuable and you're going to be working with it a bit or if you want to absorb its content faster. What we might do is click in the upper left corner and then double click any column boundary.
This will readjust all column widths and then we could print the worksheet in its current form. And when you do that it would be a good idea to also display the column letters and the row numbers as well, then use that as a source of documentation. So if we press Ctrl+` , we'll go back to normal and since we did readjust the column widths when the columns were wider, we'll want to do this again, clicking in the upper left-hand corner. It's probably still selected anyway. Then double-click any column boundary between the letters. Now we're back to so called normal display.
We also find this feature in the menu, but it's easy to overlook and they do refer to the other symbol that's on that same key, that so called accent groove. So from the ribbon, go to the Formulas tab and you will see the choice called Show Formulas. Notice that the pop up tip below it does refer to the other symbol. And you could easily overlook that. It doesn't refer to tilde, it refers to the other one, and no matter how you view this keyboard keystroke shortcut though, remember this is Ctrl+` show formulas or the keystroke shortcut. Now a companion but certainly a different feature to what we've just seen is the idea that you might want to highlight all the cells in this worksheet that have formulas.
The key first step here is to make sure you've got only one cell selected. If you have two or three or four cells selected, for example, if we were about to use this feature, it will only search within the selected cells. So somewhat counter-intuitively, we will click on one cell and then from the Home tab in the ribbon the extreme right button, the Find and Select button, the binoculars. Click it and choose Formulas. And all cells that have formulas in this worksheet are currently highlighted. Now before clicking anywhere what you might want to consider doing is applying a color to all these so you remember which cells have formulas. We can do that simply from the Home tab, the Fill Color bucket right here. Give it a color that's light enough for the text to show through it. I'll just use this tan color right here.
And as we zoom back a little bit, we know at a glance where the formula cells are, the ones that have this tan color. Now, the feature's not dynamic if we were to write a new formula here it's not going to turn tan. Or if we erase this formula here, it would continue to stay tan, so from time to time, you will want to run the feature again, to make sure that it's up to date. A companion to this is the idea that you might want to highlight all of the cells that have pure numbers in them, not formulas but just pure numbers.
Here too begin with the active cell in a single location. And then on the home tab, choose Find and Select, then go to Special. And in the go to special dialog box, choose Constants, but then uncheck the boxes for Text, Logicals and Errors. Click OK and now we've selected these. And similarly as we did with formulas we might want to choose a contrasting color here, I'll use a light blue. There we are. And so in this worksheet the light blue cells are those that contain pure numbers.
The tan cells are those that contain formulas. So it really helps you figure out what is going on in a worksheet. And the more you work with these the two features and its predecessor where we were able to highlight the formulas. You will have a much better capability for understanding and working with sheets that have formulas.
- Displaying and highlighting formulas
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Checking for errors with formulas