Master efficient data selection in Excel using the Go To Special feature, streamlining common tasks such as isolating hard-coded values and handling blank cells. Learn practical shortcuts to improve accuracy and productivity in spreadsheet management.
Key Insights
- Use Go To Special in Excel to quickly identify and format hard-coded values separately from formulas, helping users organize financial models clearly and accurately.
- Leverage Go To Special's ability to select and delete blank cells instantly, significantly streamlining data-cleaning tasks compared to manual cell deletion methods.
- Combine Ctrl-G (GoTo) and Alt-S shortcuts or the faster Alt-Semicolon shortcut to select visible cells only, enabling precise copying and pasting without unintentionally including hidden data.
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.
So you can use Go To Special to perform several time-saving tasks. 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 and Select and you'll choose Go To Special.
That takes you directly there. 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 GoToDialog 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 wanted to do this just using 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 is maybe easier to remember, is simply pressing CTRL G as in GoTo.
If I press CTRL 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. In Task 1, I'm taking a look at a table and in this table, I have formulas and I also have text. 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 CTRL G, ALT S, and in this case, we're looking for constants, so I'll press Type the letter O. When I press ENTER, the only thing I'm selecting are hard-coded numbers that are written into the cells. I have not selected any formulas. People who use this type of table and financial modeling like to separate the hard-coded values from the formulas.
So, the way I could do that is simply apply a formatting to the cells I currently have selected. So, I'll choose the color blue and now anything that is a hard-coded 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 hard-coded 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 hard-coded numbers and formatted in blue, everything else is a formula. We're not including the date because we didn't select the date, but those are hard-coded values as well.
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 yet 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 CTRL 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, and 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 CTRL-. 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've just deleted all the blanks, and now I have just the values.
Okay, 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, and what I'm going to do is show you how to do it incorrectly. I'm going to just select the information and press CTRL C. I'll go over here, and then I'll press CTRL 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 CTRL Z to undo, and I'm going to start over. I'll press Escape.
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 CTRL G, ALT S, and look for an option for Visible Cells Only, and 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 CTRL 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 CTRL V. I only paste what I see and not what I don't see, so 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'll press Escape. There's another quick keyboard shortcut I can show you that does the same thing that I would do by using Go To Special. I'm going to select a range, and if I press ALT-Semicolon, that also only selects the visible cells.
I can press CTRL C. I'll head over to where I want to paste, and then simply press CTRL V, and there are my values. So in this section, we looked at how to use Go To Special to select cells that are hard-coded values, select blanks, and also select only the visible cells when we need to perform some operations within a selection.