Discovering time-saving tools in the Go To Special feature

- On the Home tab in the ribbon, the far right button in the Editing group is called Find & Select and lots of interesting choices and viable choices here including one called Go To Special. I've depicted the Go To Special dialog box on this worksheet off to the left. A number of these choices here are available by way of keystroke shortcuts and a number of them are also available by some of the choices that we see on Find & Select. So let's take a look at some of these options here. I'm going to go to first worksheet in this workbook and imagine that I'm interested in seeing where the comments are.

Now I can see the red triangles here, imagine the worksheet possibly is much larger. I might want to simply highlight all of these. By going to Find & Select and Go To Special, the first choice says Comments. Click OK. All the comments cells are highlighted. Do recognize when cells are highlighted, one of them appears not to have a gray background. But cell D2 is part of the mix here. If you somehow wanted to note that or keep track of that for a while, you know, apply a color, for example like this and that reminds you which ones are comments.

By the way, there is a keystroke shortcut for this. It's control+shift+o, that's the letter o. That will highlight those cells as well too, highlights all the comments in the current worksheet. When we go back to Find & Select and see Go To Special, this brings up the dialog box again. We can get here slightly faster by way of the F5 key. So if you wanted to use that feature, you can press F5. There's Go To. We can choose Special to get here as well. Let's look at some of the other options the next worksheet over.

If I wanted to highlight just the formula cells here, I could go to Find & Select and I don't really need to go to Go To Special I could simply choose Formulas. That's handy. If I wanted to highlight the constant values, I could go to Find & Select and choose Constants. But this highlights cells that include text and other things that I'm not really interested in. I want to highlight, for example, just the constants that are numbers. So by way of Go To Special, and I'll press F5 this time, Special, we can choose Constants.

But notice this oddity. When I chose that these boxes here that appear to be associated with Formulas, are also associated with Constants. If I want to highlight just the constant numbers, then I'll uncheck the other boxes here, Text, Logicals, and Errors. So I'm about to highlight just the constant numbers, the numbers in the worksheet that are not formulas. Here too, I might want to apply a color. So a curious kind of overlap here between some of the options that we see on Find & Select, some of these choices, and what actually we find inside the Go To Special dialog box.

Now different worksheet here. I might want to highlight the blank cells here. Maybe instead of these being blank, these sales, these empty sales days so to speak, I want to put in No sales. So how can we select just the blank cells? Highlight this area here. Rather than painstakingly going to each one, using the control key to select just the blank cells, let's highlight the entire group here and now select the blanks by way of Go To Special. F5, Special, Blanks, click OK.

So the blank cells only are highlighted. All I need to do is type what I want to go into all of these cells, so I'm going to type No sales, but instead of pressing Enter, I'll press ctrl+enter. That will put the same data in all of these. Now you could imagine a list being much, much larger than this and selecting the blanks there would really be tedious. And we have that feature too that capability of once they are selected, we can type anything we want and press ctrl+enter, same information goes in all those cells. So we were able to select the blanks pretty quickly there with the Go To Special again.

Let's go back to that previous worksheet. I'm interested in finding out which cells depend on B3. In other words, if I were to change B3, which other cells would change? If we go to Find & Select, you'll see some choices out here. None of these quite work, but Go To Special, once again and we could have pressed F5 to get here, check Dependents. Now notice we could show Direct only that would mean let's highlight the cells that have formulas that directly refer to B3, or, if we want to find all dependencies, in other words all levels here, click that one, OK.

So all these cells here have formulas that in one way or another could be affected by changes in B3. They have a dependency on B3. There's a keystroke shortcut that does the same thing here. Clicking on B3 again, ctrl+shift+right bracket, highlights all the cells that are dependent. This does not trace dependencies that might be on other worksheets, in other words, formulas on other worksheets that get data from here. Looking the opposite direction down the right-hand side here we've got a number that's our bottom line total, we want to know where this cell gets its data from.

Here too, Go To Special by way of F5, Special. This time we'll choose Precedence, and we can use the two buttons down here that initially appear only to be related to dependent that's related to precedence as well. Check All levels, click OK, and we see this. So all these gray cells here, the ones currently highlighted if altered can have impact on cell L23. Here too we could have gotten this by way of a keystroke shortcut, ctrl+shift+left bracket.

There we are. Another choice here says Last cell. That too is a keystroke shortcut. On a large worksheet like this one, you might say, "Well, how far down does this go? "How far to the right does this go?" We can press that option by way of F5, Special, Last cell, OK. But it's much easier, and I'll just scroll up to move away from that, it's much easier to press ctrl+end. That's the E-N-D key. That's equivalent to this option here that we just saw, last cell.

Here's a choice, Visible cells only. Let's go to a different worksheet here. I don't want to print this data right here. And actually what I'm interested in doing is copying some data. I don't want to include that so let's just hide these five rows. And what I'd like to do is not include the data from columns C and D, and also column I. So using the control key, I'll select column I as well, right click and HIde.

So I'm about to copy this data. Now Chris McKinney has just left so I don't want to copy the information for Chris. I'm going to right click row 11 and hide that. So here's the data I would like to copy. All the Admin Training people, except for Chris McKinney, but I don't want to include the data from that other group that was in rows two through six. I don't want to include the Social Security and phone number on the other column that I hid as well, the compensation column. Here's what I'd like to copy. So if I press ctrl+c here and go to a new sheet just to the right here and paste this with ctrl+v, I will have gotten data that I didn't want.

Social Security number, phone number, Chris McKinney's in this list probably. There he is right there. And I got information out of here. In other words, I got all kinds of information that I didn't want here. I wanted to pick the visible cells only. So let's go back to the data here. I've got it highlighted, I'll press F5, Special, and Visible cells only. And as I click OK, I'll see a difference on the screen. We see lines around these.

We're picking up or we're about to pick up the data in groups, and when I press ctrl+c to copy, this is even more apparent. In effect, we're picking up the data in nine separate chunks here and not the hidden data at all. And now, as we jump to that other sheet, by way of contrast, I'll paste this in L1 with ctrl+v, we see what happens. Of course, adjust the column widths and so on, get that looking good. We've got just the data we wanted.

It turns out here we have a keystroke shortcut as well. So although the feature is valuable, you will forget the keystroke shortcut from time to time maybe, we can always go back to Go To Special. So, the shortcut is, if you've got the data highlighted and you want to select just the visible cells, it's alt+semicolon. Then we press ctrl+c. If you forget the alt+semicolon at a later time, would you remember that it's under Go To Special? Well, let's hope so. So, Find & Select, Go To Special, Visible cells only.

Again, keystroke shortcut alt+semicolon. As I say this, I'm often wondering why those keystroke shortcuts aren't displayed in here. It would be helpful for some of these. All right, different worksheet here. We can see that there's conditional formatting in column F also in column I. Is there any other conditional formatting here? On the Find & Select button, there is a choice called Conditional Formatting. There it is. This highlights just the cells in this worksheet that have conditional formatting. If we click on a single cell, and now go back to Find & Select, we can choose Conditional Formatting by way of this choice, but by way of Go To Special.

Now notice here when you choose Conditional Formats, the two buttons below that say All and Same are highlighted or at least inviting us to use them. If we choose All, we're in effect doing the same as that last option, just like that. But suppose we're wondering does this cell or any other cell in this worksheet have the same rule as this one, conditional formatting rule? Find & Select, Go To Special. What if we choose Conditional formatting, Same. It's as if we're saying highlight all the cells that have the same conditional formatting as this cell.

Click OK. That does point out, by the way, that cell H1 is not part of the conditional formatting but all the others in column H are. So that's going to be helpful at times. Similarly, with data validation, now this is on a different worksheet, where is data validation here? Find & Select, and here too we have that same dual capability. We could choose Data Validation. This will look throughout the entire worksheet and highlight cells that have data validation rules after having selected a cell. Find & Select, Go To Special, this time Data validation, Same.

And again, what we're saying is highlight all the cells that have the same data validation rule as C4. Click OK. And we see what's happening there. So, lots of choices here. We've seen quite a few options available by way of Go To Special. Remember you can get here slightly faster with the F5 key. A number of these do have keystroke shortcuts. I would hope one of these days that maybe some of these keystroke shortcuts will be listed here. It would help us with some of these. Nevertheless, lots of choices here by way of Go To Special available on the Editing group on the Home tab.