My mother always told me “Everyone is special in their own way.” What she didn’t tell me, is that every cell in a spreadsheet is special too.
In this video, I will teach you how to go to a specific cell or range of cells using their special traits. This is perfect for jumping to a specific spot without scrolling, and for making mass changes in an extremely targeted and smart way. Get excited.
Full Video Transcript:
Has your boss ever asked you to change the formulas to gray font and the numbers to blue font?
Well, in this video, I’m going to teach you how to do this in a few seconds instead of a few hours.
This shortcut has 2 components. The first is going to a very specific spot on your sheet if you know exactly where you want to go.
For example, if I want to go to a very, very low point, a high row number, but I don’t want to have to scroll, I would just do
CTRL + G, and I would “Go To” for example, A9999, and I can just hit
ENTER and I go immediately to that spot without any scrolling whatsoever. So, that’s a really simple way to move really quickly in a very targeted way, but the real, real power of this is to use the “Go To” with the “Special” option, all right?
So, when you hit “Special”, you’re going to choose a very special kind of cell, and I recommend you go through each one of these in more detail, but for now, let’s just look at, for example, a constant, and we can choose Only Numbers, Only Text, Only Logical, Only Errors, for example. Let’s choose Only Text.
It will scan through this entire sheet, and it will highlight, even if they’re not next to each other, all the cells that match that criteria, which means in one move now, I can make them all bold, or all italic, or anything. So, this is a very powerful way to do massive edits without having to search for it.
One nuance here is that you can actually choose the whole selection that you want first, and then it will match that criteria only within it. So, I’ll hit the Special with the
ALT + S now to jump right to it, and I’ll go ahead and choose the
ALT plus the letter to actually correspond to the underline here.
So, I want a Constant, so it’s
ALT + O, and I’m going to uncheck the other ones that I don’t want, so
ALT + U,
ALT + G, and
ALT + E to leave only the text. Hit
ENTER, and now only these cells are selected, because I started first with that selection. If you choose nothing, it’ll go and look for the whole sheet as one.
I’ve created a series of exercises for you to practice and reinforce these Excel shortcuts. So, for example, you’re going to go to this worksheet over here and move the X’s by
CTRL + X on the PC,
COMMAND + X on the Mac, and then go to this exact reference by hitting
CTRL + G, and GO35. It will jump to that spot, and you’ll know you’re in the right place because I put a little arrow for you. And
CTRL + V on the PC or
COMMAND + V on the Mac, and then you can actually keep going back and doing that for all of these.
The next exercise is to go to Special Cells. This one’s more involved, but you can totally get the hang of this as soon as you start going. So, make this side over here on the left match the format on the right in this very specific way, and if you want little hints, the legend here explains how it all works. So, anything that’s a text, right? You’re going to select this whole area here, you’re going to do your
CTRL + G, and then your
ALT + S on the PC to choose only the text. You’re going to turn off anything that’s not relevant, and then you’re going to apply the italics and the bold, and notice how it makes it match. So, do that for all of these and make the two images match.
Don’t forget to visit excelshir.com, where you can download these exercises, along with other free resources, such as keyboard shortcut cheat sheets for both PC and Mac.
Thanks for watching, and I’ll see you next time, but let me leave you with this parting thought. If a picture is worth a thousand words, then sharing the Excel love is priceless.