Excel Go to Special

Go To Special and other Excel tricks are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Go to Special

Go to Special does what its name sounds like: It allows you to go to special parts of your Excel spreadsheet, special cells and ranges. You can use Go to Special to perform several time-saving tasks. 

Use Mouse to Find Go to Special 

You have a couple of ways to get to Go to Special. 

If you use your mouse, that's how you can directly get to Go to Special. Simply go to the home tab, you'll head almost all the way over to the right of the ribbon. In the editing group, you'll click the drop-down for Find & Select, and you'll choose Go to Special. That takes you directly there. 

Use Keyboard to Find Go to Special 

Now if you're using keyboard shortcuts, you can get there as well. There's just an extra step involved. One of the keyboard shortcuts you can use is F5. Make sure that if you're using your laptop, press the appropriate function key if you need to. I'll press F5. That opens up the Go To dialog box. That's why I said there's an extra step. I need to actually click on Special to get to Go to Special. 

If I want to do this just using the keyboard, I'll press F5, and I can't just press the letter S that's underlined for Special because that will simply type text in the input box. In order to come out of the input box, I need to press Alt and then press S. Alt S, and then that takes me to the Go to Special dialog box. 

Another method that you can use, which may be easier to remember, is simply pressing Control G, as in Go To. If I press Control G, I get taken to the Go to Special dialog box, and then I will press Alt S. 

This allows me to select certain types of cells like blanks. I could also select formulas. Constants are just numbers or text in cells that are not formulas. I could also select the last cell. I can select cells with conditional formatting and data validation. Those are some of the options I have. 

I'm going to click Cancel, and let's take a look at how you can use this in Task 1. 

Task 1 

In Task 1, I'm taking a look at a table, and in this table I have formulas, and I also have text in the form of numbers. So I just want to select the numbers. I don't want to select the formulas. So I'll use Control G, Alt S. And in this case, we're looking for constants, so I'll type the letter O. When I press enter, the only thing I'm selecting are hardcoded numbers that are written into the cells. I have not selected any formulas. 

People who use this type of table in financial modeling like to separate the hardcoded values from the formulas. So the way I could do that is simply apply formatting to the cells I currently have selected. So I'll choose the color blue. And now anything that is a hardcoded value has blue formatting applied to it, to the font. 

One of the ways you can verify whether or not this actually did what it was supposed to is you can go to the Formulas tab and then you can choose Show Formulas. Show Formulas, will act like an X-ray machine and show you every single formula in the spreadsheet so you can actually see whether or not any of the formulas are colored blue. 

If they are, then the Go to Special dialog box did not select all the hardcoded values and selected some formulas by mistake. But let's click Show Formulas to check. 

And as we look at the information, we'll see. Now this accurately selected all the hardcoded numbers and formatted them blue. Everything else is a formula. We're not including the date because we didn't select the date, but those are hardcoded values as well. 

Task 2 

So now I want to go back and take a look at another way I can use Go to Special. You can use Go to Special to remove blanks. 

Now traditionally, what you might do to remove blanks is select a range of cells, right-click, and then press delete. Then select another range of cells, also right-click, and then choose delete. And then select a third range of cells, right-click and delete. This takes time. 

So let's take a look at how Go to Special will allow you to do this and save some time. 

Now I'll bring up Go to Special. By pressing Control G, Alt S. What I want to select are the blanks. Now each option here has an underlined letter, and I see the underlined letter for blanks is K. If I type the letter K, I am selecting that option when I press enter. I've selected all the blanks. 

Now I can perform my removal operation and remove all the blanks. I could right-click and choose delete, but I'll use a keyboard shortcut. 

In order to remove information, I'll press Control minus. This brings up the delete dialog box, and then I have the opportunity to confirm how the cells should shift and I would like them to shift up. So I'll press enter. I just deleted all the blanks. And now I have just the values. 

Task 3

OK, let's continue further down the sheet, and we have some information here that we need to copy over to this section of our sheet. 

What I'm going to do is show you how to do it incorrectly. I'm going to just select the information and press Control C. I'll go over here, and then I'll press Control V. Now, I shouldn't have selected the column that said Do Not Paste, so that's doing it incorrectly. 

I need to do this again, but I only need to select the cells that I see and not any of the cells I can't see. So I'll press Control Z to undo, and I'm going to start over. I'll press Esc. What I need to do is select only the visible cells. So let me select everything, and before I copy, I'm going to use Control G, Alt S and look for an option for visible cells only. I see it there, so I'm going to select it. I'll click OK. 

Currently what's only being selected are the visible cells. I'll now press Control C, and this little animation lets me know something here is happening that's different than what happened before. If I click on the plus sign, I can actually reveal what's going on. I am no longer selecting the column that I selected before in my initial copy. I'm going to collapse this. 

So what's going to happen if I go over here and paste? Let's check. I'll press Control V. I only paste what I see and not what I don't see. That's also how you can use Go to Special if you have certain information you don't want to copy that is hidden in a grouping. I press escape. 

Another Keyboard Shortcut

There's another quick keyboard shortcut I can show you that that does the same thing that I would do by using Go to Special. I'm going to select the range and if I press Alt semicolon, that also only selects the visible cells. I can press Control C. I'll head over to where I want to paste, and then simply press Control V, and there are my values. 

Recap

So in this section, we looked at how to use Go to Special to select cells that are hardcoded values, select blanks, and also select only the visible cells when we need to perform some operations within a selection.