Cell Alignment isn’t just for the obsessive excel user, since it can singlehandedly make or break the overall presentation of a report. I’ve sat in on year end evaluation meetings before, trust me.
In this video, I will show you how to align cells consistently every time with a few simple excel shortcuts.
Nothing will drive a person crazier than when cells do not line up.
I kid you not. I had a client once whose entire purpose for hiring me was to line up the cells in a report.
And it might seem trivial, but in fact, this can make or break the professionalism of a report. And today I’m going to show you how to do it.
Whether you’re aware of it or not, you are already aligning cells left, right, center, all the time, but you’re probably using the mouse. And today I’m going to show you how to use the keyboard to cut that time down significantly.
It really comes down to a very straightforward series of steps. The whole beginning is to hit ALT + H for Home and A for Align, and then it’s going to be a letter that corresponds to the action itself, so L for Left, C for Center, R for Right. I mean it doesn’t get more straightforward than this. T for Top, M for Middle, B for Bottom. Just go straight with that, and you’ll be on a very good footing.
The whole point is to get to this top ribbon piece with the PC, and then you’re golden. There’s really not too much to remember. It’s all baked into it. If you’re on the Mac, on the other hand, the two that are built in are COMMAND + L for the Left and COMMAND + E for Center. And because C was already taken, you have to use E and think of it as Equidistant from both sides.
Unfortunately, on the Mac, you cannot customize the keyboard shortcuts for the rest of these, for the right, top, middle or bottom, so you’re going to have to use the ribbon on the Mac to actually do it the other way. But otherwise, you’re going to save a considerable amount of time by using these keyboard shortcuts to align your cells.
Let’s illustrate these shortcuts with a few specific exercises. So you’ll have the align left. Again, just make this left section here match the exact format on the right and use the keyboard shortcut to make it happen.
So once you’re here, ALT + H + A + L for the Align Left. And you’ll go through and you’ll actually practice it several times until you get it down cold. We have it for the left, for the center, for the right, top, middle and bottom. And of course, a nice little challenge to get it to match up exactly.
Don’t forget to visit test.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. See you next time. And if there’s just one thing you do today, share the Excel love. It will make you feel all warm and fuzzy on the inside.
Don’t you hate it when you’re typing out a list of numbers and some appear with a decimal, some with no decimal, some with a comma, some with a currency symbol, and all you wanted is to get them all to look the same?
This video is for you because I’m going to teach you exactly how to format your numbers quickly, efficiently, and correctly.
In contrast, this video shows you how to get the most common formats really quickly, for those back-of-the-napkin type calculations that you just need more quickly.
So here is a list of all of the quick formatting shortcuts. The trick to all of this is CTRL + SHIFT + Something, and that something is going to be on that entire, basically, half of that row on the keyboard on the top, starting with that Grave symbol, which you may not have used ever before, all the way through 1 through 6.
So if you do CTRL + SHIFT and one of those, you’ll get the format that corresponds to it. So CTRL + SHIFT + ` (Grave) will get you general. G, grave, general. Get it? It’s all related. Okay.
Exclamation point is because you’re going to do the number, right? Think of it as like the decimal point that appears when you do it. The @ is like, “What time is the party at?” So I want to see the time. That’s how that’s working.
The hashtag here, the little date thing, I always think of, you know, being stranded on a deserted island and counting down the days with little tick marks. So that makes me think of the day format.
Currency is straightforward because it’s a dollar sign. Percentage is a percentage. This one is a little caret, or caret, depending on how you want to pronounce it, and it’s going to be raising the scientific roof because basically it’s scientific notation. So the numbers are too big or too small, actually, and you need to show it in a scientific format.
That’s essentially it, and it’s the same exact shortcut for the Mac. It’s still CTRL + SHIFT, and then that other additional number or the Grave symbol on the left of the 1.
Pull up a chair because there’s a bunch of shortcuts to work through here, right? We’ve got the general. So you want to make the entire left section over here match the format on the right. And all you have to do is turn these cells into the correct format, CTRL + SHIFT + ` (Grave), and it will go ahead and match it to that spot.
Go through each of these using the shortcut that we talked about. There might be some subliminal messaging here, which is not so subliminal anymore because I talked about it, but you get the idea. Go through each of these, and then go through the challenge one where every single one of these should match in a very precise way. And really just practice as much as you need to until you get it memorized.
Don’t forget to visit test.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. See you next time. And tonight, before putting your kids to sleep, instead of reading them a bedtime story, share the Excel love. Trust me. Knocks them right out.
Some people are extremely particular about the way their spreadsheets look. Clearly, I’m one of them, and in this video, I’m going to teach you how to be very precise and comprehensive with your formatting, which is great for presenting to your boss, investors, or that guy across the hall you’re trying to impress.
Sure, you can use the “Home” tab on the ribbon for many of the most common formatting options, but if you really want to start to get things to the next level and be really precise and comprehensive with all your formatting options, the one place to go is Format Cells, and the way to get there is with CTRL + 1 on the PC or COMMAND + 1 on the Mac.
This is one of those shortcuts that will open up an entire world of different options for you. So, we’re going to explore in-depth, but really, the only main shortcut is the Format Cells for CTRL + 1. The way to remember this is, the one formatting shortcut to control them all, all right? Think of The Lord of the Rings when you think about this.
So once you have this window here, CTRL + 1, there’s a lot of different tabs up top, and you want to be able to navigate quickly between the different sections. The way to navigate is to go CTRL + PGDN to move to the tab on the right, CTRL + PGUP to go to the tab on the left. If you hit TAB, you’re going to move to the next section or area on this popup. TAB again, TAB again. If you want to go in the reverse direction, you’ll do SHIFT + TAB. And again, this is true for any popup that comes up on your screen on your computer, which is really cool.
Another way to navigate is to go ahead and hit the Spacebar when you’re on a check mark. You can check it by hitting SPACE, and you can uncheck it by hitting SPACE again. Alternatively, you can also do things like hit ENTER to hit the OK button or basically make it save and take effect, or hit ESC to cancel out and essentially close the window without saving your changes.
You can also do things like use the ALT and then the letter to actually activate that specific area. So, ALT + W, in this case, will get me to Wrap Text. ALT + W again will undo it. If I want to move to this box here, ALT + H. You can do a lot of these types of things, just pay attention. This is for the PC. The ALT key is a nice accelerator option.
The memory tricks for this are to control your pages or they won’t pay their tab. Think of pages as little rambunctious, obnoxious kids. They’re running around in the restaurant and not paying their bill, so control your pages, or they won’t pay their tab. That’s how you remember that piece. If you want space for dessert, nope. Check, please. The space, dessert, you know, check, it’s all related. In my mind, it was. Hopefully, that helps you.
Fortunately, on the Mac, it is actually very much the same kind of thing with very little, minute differences. It’s the same concept. CTRL + FN + DOWN, which is the same as PGDN. So again, it’s pretty much the same thing as we’ve done on the PC.
There are three main tabs on this window that are important, so that we’re going to cover. There’s the “Number,” “Alignment,” and “Border,” and let’s go through each one.
On the “Number” tab, you have a lot of different types of data, different categories for what the data could actually be. So, you’ll start by selecting that on the side, and then you get a whole host of options. Again, explore each of these on your own time, and really get a flavor of it, but things like General, Number, Currency, Accounting, these are all very, very important, and if you get really fancy, you can do all sorts of custom stuff, but most people don’t ever have to do that. So, don’t sweat it.
Let’s go to the “Alignment” tab and see that there’s a lot you can do here. You can orient your text. You can do things like “Wrap Text” or “Shrink to Fit.” You can even do some merging across, we’ll talk about that a little bit later. And so, that’s a great way to take full advantage of how your text, or your numbers, or whatever your data is, is aligned exactly the way you want to.
Finally, there’s the “Border” tab here, which is kind of incredible because it lets you choose, first, your style, what kind of paintbrush are you going to use, essentially. Well, I want this solid line or this thicker line, this thicker brush, for example. You can then choose the color. Most people keep it Automatic, but you can actually choose a different color here, and then, you use this area here as a canvas, and you will paint the borders onto this.
You can use these things on the side to actually be very specific and targeted about it, or you can do it with the mouse on that section itself, which is another approach. You can do None to erase it all quickly, you can do Outline, whole lot of things you can do, again, explore this, get a feel for it. But first, choose your style if it’s not the one that you want, then your color, and then you can go ahead and apply those changes to this canvas here.
Very important note is to select the cells first before you do any of these things. So, for example, I’m going to hit Cancel, I want to choose these cells over here, and then I’m going to hit CTRL + 1 and I’m going to apply a change, for example, a border.
Let’s make it crazy and do a little sideways border, which looks terrible, and then it applies it all the way to it. But the point is, if you just start doing borders all over the place without selecting first, it won’t get you the result that you want.
You better believe it. I’ve got exercises to practice all of these shortcuts and really remember them cold, so let’s go into each one. You’re going to match the format, make this left section look exactly like the right.
Hints, probably do the thing that it talks about at the bottom, right? It’s pretty cool. Select the cells you want, like the Number here, hit CTRL + 1 and go to the “Number” tab, and let’s see. Let’s move this to the side. Well, in fact, I want the number to appear like so, with only one decimal point and a thousand separator, and with the negative to be with red and a parentheses. So, it’s very, very precise and very comprehensive.
So, go through all of those steps to do things like changing the number format, go through the Alignment tab, make it look exactly the same way, and also do things like the border. It gets very specific. One little note for the alignment, do not merge these cells. It says so right here in the hint. It’s much better to, instead, CTRL + 1, if you want to basically get a hint, Alignment, and Horizontal, it is “Center Across Selection.” What that does is it actually keeps your data only on the left side, but it does the nice thing of putting it as if it’s centered across all of them, versus merging, which can lead to a whole bunch of problems later on, which are just annoying and stupid. So, don’t do that.
Don’t forget to visit test.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. And I think it was Gandhi who said it best when he said, “Don’t forget to share the Excel love.” Wise words.
One major advantage that Excel has over paper & pen is that you can literally shift EVERYTHING over and make space for more information without disturbing your existing work. You can also clean things up by removing unused information (no more erasing feverishly!).
In this video, I will teach you how to insert and delete cells, rows, and columns quickly and correctly.
Ever hand in a report to your boss, only to have her respond with, “Where’s the March data?”
Don’t worry, there’s a super quick fix to this and it involves inserting cells, rows or columns, which is the topic of this video.
This is one of the most powerful shortcuts in Excel because it lets you go back and add new information to an existing project, while keeping the cell references and everything else intact.
On the flip side, when you’re deleting, make sure to do so with caution because you can only undo up to a certain point. And once your information is lost, it’s pretty much gone forever and no amount of kicking or screaming is going to bring it back.
Fortunately, the actual shortcut here is very, very straightforward. To add or insert cells, you’re going to hit CTRL + + (plus). To delete, you’re going to do CTRL + - (minus). That’s really it, and this is one of those rare times where our Mac and PC shortcuts are identical.
The only nuance to this is you’re going to select first, before you actually use this shortcut. So, let me show you what I mean. If you want to take an entire row, you’re going to select the row and then insert with CTRL + + (plus) to get that row inserted. If you want to choose an entire column, you’ll do it this way and then do it afterwards.
If you want to do an entire cell, you’ll get this pop-up window saying, “Do you want to shift the cells right or down?” You’ll never really need to use these two because you can just select the entire row or column first. That saves you some trouble. But again, think about how you want to shift stuff over to the right or down. Sometimes it varies, based on what you’re trying to do.
Here are some exercises that I’ve created specifically to reinforce these shortcuts, and practice as many times as you want. So, to insert all the cells here, you’re going to end up getting the side on the left to match exactly with the side on the right. And the way to do this, in this example, is to insert the cell, so CTRL + + (plus).
Again, notice I’ve selected the cell first. Shift it to the right because I want this number to move over to the right, and hit “Okay.” And I do the same type of thing over, make sure I’m shifting around until it matches exactly.
You’re going to go through insert the cells, you’re going to do delete all the cells. You’re going to insert the rows, delete the rows, insert columns, delete columns. Lots to do, but it’s all under the same umbrella of inserting cells with CTRL + + (plus) and deleting cells with CTRL + - (minus).
Don’t forget to visit test.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. See you next time. And in the words of William Shakespeare, “To err is human, but sharing the Excel love, now, that’s divine.” He’s so right.
I don’t know if you know this, but Excel can actually hold a lot of different types of information, sometimes all within a single cell.
And I’m not just talking about numbers or text. There are things like comments and hyperlinks and other data that’s actually there behind the scenes that may not be visible.
So, what happens when you want to clear away some of it, but not all of it? Well, today I’m going to teach you the shortcut on how to clear cell contents.
Instead of being sloppy about deleting information, there’s actually a way to be very precise and efficient about what specific information you want to get rid of, especially when your spreadsheets start getting more complex.
So, here are a number of different ways to clear contents. The first one is Delete. If you actually hit the DEL key, it will remove all of the contents without touching anything else. I’m going to bring that back with a little undo action.
The other ones are all based on the ALT key as the starting point. So, ALT + H for Home, E for the Clear, and then we have a whole bunch of options.
And the memory tricks for all of these is going to be, “Hey, everyone, clear all,” or, “Hey, everyone, clear formats,” “Hey, everyone, clear messages.” All right? The messages part is referring to the comments. Or, “Hey, everyone, remove hyperlinks.”
So it’s all related to that first letter, but you have to get to it with the ALT + H + E something. So, for a clear all, ALT + H + E + A and it will remove not just that content itself, but all the data along with it. Same idea for formats, for comments, for hyperlinks. We’re going to go through each one.
A couple things to note, the DEL key itself is what you want to use when you have multiple cells selected to clear the contents. Instead of the Backspace, which will only give you this annoying first cell removed, but then the blinking cursor, not what you want. So, people usually make that mistake. Don’t make that mistake, just hit the DEL key instead of the Backspace.
For the Mac, there is the other way of doing this, which is to use the fn + Delete on the keyboard. And for the rest of these, these are all customized, so go ahead and go to the description of this video, click on the link for how to customize your Mac keyboard shortcuts, and go through all those steps to do it in detail.
Something that I cannot stress enough is the importance of selecting the cells first, and then making the change, right? “Select, then effect.” So, don’t just start deleting things, select the cell or cells that you want, and then use the shortcut to have it take effect.
Here are a few exercises I created specifically designed to help you practice and reinforce all the different shortcuts. So, go ahead and make the left section here match exactly as it appears on the right using the keyboard shortcuts that we talked about.
So, in this one, we’re going to use the DEL key, we’re going to have…go ahead and select all of it and clear it out and continue to go through until it’s all matching the same way.
And notice that each one uses its own, go through each of these, and at the end, there is a challenge to use a combination of all the different ways to make it look exactly the same.
Don’t forget to visit test.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, see you next time. And remember, dream big, live your truth, and share the Excel love. I do.
The whole point of headers is to accurately describe the data underneath them. But what good are headers if they disappear every time you scroll down?!?!
Don’t worry, in this video I will teach you exactly how to freeze your headers at the top of the screen, so you always know which data you are looking at.
Have you ever gotten lost in a sea of data with no reference point?
Odds are it’s because you have not frozen your headers. Don’t worry. In this video, I’ll teach you exactly how to do it.
The whole point of freezing cells is to keep certain parts of the screen in place while you’re scrolling somewhere else.
And even though there’s many different ways you can use this, the most common and, I think, one of the most helpful ways to do this is to keep your headers frozen at the top.
So, the way to do this on the PC is with ALT + W + F + F, and it’s all activated through the ribbon. So, ALT + W will get to the “View,” F will get to “Freeze Panes” and F will get you to “Freeze Panes” again. You can safely ignore both of these bottom choices because we’re going to cover only this top choice and it’s based on the current selection.
So, I’ll explain exactly how that works in a second. The key thing to remember here is, “Why is the fudge freezing?” That’s all you have to remember, and then you’ll get the whole freezing panes concept down.
If you’re on a Mac, you have to customize your own keyboard shortcuts. So, go to the description of this video, click on the link, and there’s a separate video that goes through detailed step-by-step instructions on how to customize your own keyboard shortcuts.
I recommend using these two because you can’t use an Undo. You have to use one step for Freeze and, again, a different step for Unfreeze.
Let’s see it in action. And go to this page here and actually select, first, the entire row, so that above that row is where I want the frozen section to be.
So, here’s the setup for it. I do ALT + W + F + F, and now that whole top section is frozen where all the rows 1 through 4 are there. If I want to undo, I can’t do CTRL + Z, I have to back, ALT + W + F. And, again, I hit F again. Notice that it’s changing the name here. It’s “Unfreeze” at this point. So that’s how you freeze the rows.
On the flip side, you can do the columns by choosing the column after the frozen point. So it’s always going to the left of that spot, ALT + W + F + F. And now it’s frozen left-to-right which is, again, pretty helpful if you have things such as names, dates, IDs, something that has to stay visible even when you’re scrolling.
Most people know about these two but what they don’t know about is the third option, So, let me undo it by ALT + W + F + F and choose not a row or a column but a single cell. And this is the point where, at the top left, it’s going to be the frozen point.
So, again, ALT + W + F + F. Not only is it frozen up and down, but it’s also frozen left and right, which is super helpful when you have the headers at the top as well as information on the left that you always need to see.
One caveat here, one thing to avoid is getting lost in your data and saying, “Hey, what happened to my information?” and you go up top and you think you are missing things. You just have to go one down, past that frozen point, to actually pop it back into place and see everything visible again.
So that may happen where, if you’re going down and you go all the way back up, it looks weird. You just have to just go down a little bit more individually, past that frozen point, and everything will pop back into place.
Here are specific exercises I’ve created to help you practice and actually learn all of these shortcuts really, really well. So, go ahead and fill this out.
Freeze the cells along the black border itself. So, again, choose the section underneath, ALT + W + F + F. They’ll be frozen that way for the row. Do the same thing for the column, and the same thing for the rows and columns at the same time. And I recommend using this pretty much all the time because you’ll always have information at the top that you kind of always want to see.
Don’t forget to visit test.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. And next time you’re with friends and family, go ahead and share that Excel love. Oh. Oh, they’ll thank you for it.
Fact: When it comes to spreadsheets, size matters.
If the text is too small, make it easier to read by zooming in. On the flip side, if you can’t see everything you need to without scrolling, try zooming out.
Either way, make it easier for your boss, colleagues, and yourself by learning how to zoom in and out quickly and effortlessly by using the shortcuts I’m about to teach you.
How many times have you received a spreadsheet only to have a really hard time reading it because the text was way too small?
It happens to me all the time, and the first thing I do is zoom in to the correct level. That’s exactly what I’m going to teach you in this video.
What’s wrong with this picture?
If you can read any of this, then I’m extremely impressed. This is way too small, and the only thing we can possibly do before doing anything else is zooming in, right?
That is such a key thing that so many of my clients somehow seem to forget, and they make it really difficult for me to actually read what they’re actually using on a daily basis. So don’t ever make it hard for someone to read your spreadsheet.
Always zoom in very, very nice and big, but not too big because then you can’t see everything that you want to see. So that’s pretty much the essence of this video. And the way to do this with the keyboard is, on the PC, it’s ALT + W + Q will get you to this zoom window.
You can use any one of these presets. You can use up and down arrows to select them. You can use the ALT and then one of those numbers or letters that’s corresponding to the underlined section, and it will jump to that spot. And if you want to type in your own number, I like 125, and hit ENTER, it will now jump to that specific zoom.
On a Mac, what you need to do is actually customize your keyboard shortcut. There is a separate video. Check out the description of this video and click on the link for detailed, step by step instructions.
In this case, it is CTRL + SHIFT + Q, and the “Q” looks like a little magnifying glass. So you’re like Sherlock Holmes for a day and you want to say, “Hey, where’s my magnifying glass?” That’s how I actually figure out how to zoom in with that keyboard shortcut.
If you want another way to do this, you can actually press and hold the CTRL key and then scroll up and down to zoom in and out. That’s actually how I’ve been doing it. You may not have even noticed, but that’s how I did it.
Here are a couple exercises to reinforce these shortcuts and help you practice them. So on the tab itself, on the name of it, it tells you how much you need to zoom in to.
Go ahead and use those shortcuts. In this case, on a PC, ALT + W + Q. Hit TAB to move to this next box and then type in 400 and then hit ENTER, and you’re going to get to the right spot.
Again, just follow the prompts on each of these, and you’ll get to the right approach. And that way, you can practice all of these shortcuts to your heart’s content.
Don’t forget to visit test.excelshir.com where you can download these exercises along with other free resources such as keyboard shortcut cheat sheets for both PC and Mac.
Thank you for watching, and I’ll see you next time. And do me a favor. Share that Excel love.
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.
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 test.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.
Picture this: you have a lot of reformatting to do on your spreadsheet and there are pockets of different formatting sprinkled throughout. Finally there’s a shortcut to speed up that process!
In this video, I will teach you how to select multiple cells that aren’t next to each other without touching the mouse.
Have you ever tried to select multiple cells that were not next to each other?
Sure, there’s CTRL + CLICK for the PC. There’s COMMAND + CLICK for the Mac. But in this video, I’m going to teach you the shortcut to select non-adjacent cells using only the keyboard.
If you want to select cells that are not next to each other using the mouse, the way to do it primarily is with the keyboard, pressing and holding the CTRL key and then clicking with the mouse. This is probably what you’ve done thus far. You can even click and drag and get a whole range going. That’s for the PC. For the Mac, it’s the same thing, except instead of CTRL, you’re using COMMAND, keeping it pressed, and then clicking around that way.
However, if you ever want to use the keyboard only, the way to do it is with SHIFT + F8. And the memory trick is, “You should have come along, you funny mate!” Which is terrible, I know. This is a terrible trick, but hey, it actually helps you remember. The should is the Shift, and the “funny mate” sounds like eight, so it’s kind of like, you know, close, I guess.
Either way, the way to do it here is to keep SHIFT + F8 pressed now, and then move with the ARROWS. And what that does is it keeps that selection in place before moving. If I Shift down and to the right, for example, and then hit SHIFT + F8 again, I can safely move without losing that selection.
So that’s the whole trick. Before you move, you want to hit the SHIFT + F8. Otherwise, you will “lose your work,” right? If I hit SHIFT + ARROWS and then I move aside without hitting SHIFT + F8, I start from scratch. So that’s a big tip right there, is not to do that until you’re done and you actually have it all selected that you want.
So there’s one more nuance, which is that if you want to take an entire range, you’ll go ahead and hit SHIFT + F8 once, move aside, as we saw. But if you want to take a single cell, you’ll hit SHIFT + F8twice before moving on. Otherwise, it will not work. So if it’s a single cell, you hit it twice. And if it’s a range of cells, you hit SHIFT + F8 once before you move.
I’ve created a series of exercises for you to practice this shortcut specifically. So the way to do it is to go to the “Non-Adjacent Ranges” tab. You want to match the format. Everything that’s on the left here, you want to make it look identical to the way it is in the example on the right. The only difference here is the bolding. But use the shortcut that we just learned.
So select all of these, hit SHIFT + F8, and then that way, you’ll get it all selected before you even apply the bold. And you would do it all in one swoop, and that’s kind of the whole benefit of this shortcut. That’s for the ranges.
The next tab is going to be the same thing but with individual cells. Notice, you’re going to hit SHIFT + F8 the first time just once, but afterwards, SHIFT + F8twice before you move on. Otherwise, it will not work. And that’s the whole trick there.
Finally, if you’re feeling adventurous, you’ll go to this “Non-Adjacent Challenge” and you’ll do all of these in one selection without using the mouse. And that way, you can practice all of this together.
Don’t forget to visit test.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. See you next time. And if you can only take a stand for one thing in your life, take a stand for sharing the Excel love. That sounds like someone I would stand for.
Has it ever happened to you that you knew a specific phrase or even a specific number but you couldn’t seem to locate it on your spreadsheet?
Stop fumbling around and use this shortcut I’m about to teach you. It’s kind of like having a Google search on your spreadsheet.
You may think you know how to use Find but what I want to show you today is a way to really take advantage of this built-in feature, to locate exactly what you’re looking for without having to fumble around and just struggle.
So what you want to know is how to get to that Find box. That Find pop-up is CTRL + F, you may have already done this before. If I’m looking for an “X” and I hit ENTER right now, it’s going to go to the find next but if I keep going I can actually go to all the instances of it that way. Fundamentally, this is what it’s all about but there’s a lot of different ways to take it to the next level, which I’m about to show you.
So for example, if I go to the options, there’s a whole world of more advanced ways to go about this. You can do things like search for a specific format, we’re not gonna get into the details of it but I recommend that you explore this on your own time.
The cool thing is to search not just within the workbook, or not just within the worksheet, but the entire workbook, so for here, if I keep it within the sheet, it’s going to localize it to this worksheet only. You can search by rows, meaning going left to right and then down, like a book, or you can do it by columns, where it goes first up and down and then to the next column. Very cool way to speed up the search, if you have a lot of stuff.
You can also look within the formulas, within the value, within the comments, again, explore this on your own, match the case. I mean it’s crazy.
Really cool thing to do also, is to do a Find All, what that does is it gives you a list of all the different instances that that character or that string of characters appears. And you can see them all here, if I were to change it now to, say, within the entire workbook and I do Find All again, notice what happens, it gives me all the details. The cover worksheet has an “X” in it, in this cell, like, it literally lists everything out.
So I recommend that you explore this on your own, in detail. You can also go to the replace, and find every time there is an “X” and replace it with a “Y”, stuff like that, so it’s really, really cool.
To navigate this window, this is where it gets also pretty juicy, is to hit the TAB key and you’re going to jump to the next section of this pop-up itself. So TAB will go next, SHIFT + TAB will go to the previous, and if you’re on a checkbox, you want to hit the SPACE to activate or turn off the checkbox itself. ESC will essentially close the window without doing anything, and ENTER will actually save it or make that action take effect, and that’s what you want to keep in mind in terms of navigating this pop-up window.
Now, if you’re on a Mac, it’s a little bit different, I want to show you how this works. Anyone on a Mac can use the COMMAND + F but what that does is it gives you this little search box, and it’s not nearly as powerful as we just saw.
So instead, don’t hit COMMAND + F, resist the temptation, just hit CTRL + F and this is what you get instead, the same kind of options, not as much detail, but you still have more control than you did before. So that’s the nuance for Mac users, do not use COMMAND + F like you’d think to, use CTRL + F and you’ll get this more advantageous view.
I’ve created some exercises to help you reinforce all of these concepts that we learned, all these shortcuts. So again, what you want to do is go to that worksheet and find, here’s the hint, find the numbers. So I want to move the Xs, I’ll go ahead and do CTRL + X on the PC or COMMAND + X on the Mac and now, I’m going to find, CTRL + F, and look for the number one on this sheet only. And hit the next one, first it’s here, next it goes here, there’s one in there but that’s not what I wanted and now it goes to the one, which is somewhere on the sheet. We don’t know where, that’s why we’re using the find option here.
So go ahead and then you found it, close this up, go to the cell and CTRL + V on the PC or COMMAND + V on the Mac to paste, and go ahead and do that for all of the Xs for this exercise.
Don’t forget to visit test.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. And I’ve said it before I’ll say it again, share the Excel love.
