0013 Excel Shortcuts: How to Freeze Headers

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.

Download FREE Hands-On Exercises

Full Video Transcript:

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.

0012 Excel Shortcuts: How to Zoom In or Out

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.

Download FREE Hands-On Exercises

Full Video Transcript:

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.

0011 Excel Shortcuts: How to Go To any Cell and Use Special Cells

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.

Download FREE Hands-On Exercises

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 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.

0010 Excel Shortcuts: How to Select Non-Adjacent Cells

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.

Download FREE Hands-On Exercises

Full Video Transcript:

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 + F8 twice 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 + F8 twice 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.

0009 Excel Shortcuts: How to Use Find

Wouldn’t it be nice if you could do a Google search on your spreadsheet?

Well, you can! And in this video I’ll show you how!

Download FREE Hands-On Exercises

Full Video Transcript:

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.

0008 Excel Shortcuts: The Fastest Way to Move Around a Spreadsheet

This is hands down my favorite Excel shortcut, because I use it more times per day than I breath. Ok, I’m exaggerating, but not by that much.

This is a GAME CHANGER for anyone who spends any kind of time with spreadsheets. Without it, I wouldn’t just be slower, I’d be in tears.

In this video, I will show you the fastest way to move around a spreadsheet. Buckle up!

Download FREE Hands-On Exercises

Full Video Transcript:

This is one of the most important shortcuts you will ever learn because without it, you’ll be wasting countless hours, days, even weeks of your life.

In this video, I’m going to teach you the fastest way to move around a spreadsheet.

The way this works is, there’s a couple pieces to it. So, let’s start with an empty sheet, right? If I want to move it to the different cells with the keyboard, I’ll hit the ARROWS. That part’s pretty straightforward.

If I want to select as I go, go, I’ll hit the SHIFT key and then the ARROWS. Now, notice if I go to the right, it expands that way. If I go down, it expands down and I get this little rectangle. Then I go left. Notice how it’s kind of keeping that initial point as the selection start, but it will expand based on how I’m moving with the arrows as well.

So, think of SHIFT as “schlepping.” You’re taking it with you. You’re taking the selection with you as you go, hence schlep. Also sounds a little similar with the first letters, so that’s the idea.

The other piece to this is, if you want to move around really quickly, you can actually use the CTRL + ARROWS. So, notice where I am right now. I’m right over here. If I hit CTRL + LEFT, I will jump or “catapult” to the edge of the data. If I go CTRL + RIGHT, I’m going to catapult to the other edge, right? It’ll go down, left, up, it’s all going on these corners because that’s where the data is.

How, exactly, it works with the edge of the data is not super critical. You’ll get a feel for it as you go through. But basically, anything that has data in it, if it’s text, if it’s a formula, if it’s numbers, it’s all good as long as it’s not empty.

So, CTRL + ARROWS will catapult you to the next edge of the data. The really, really, really amazing part is to combine both things together. So, I’ll start over here. I’ll hit CTRL + SHIFT and then DOWN. I’ll select, as I go, that whole area. And I can expand the selection by doing CTRL + SHIFT to the RIGHT, and notice I got this whole section. So, this is where the real power comes in. You can combine the catapulting and the schlepping together. The CTRL + SHIFT + ARROWS.

Some Shir words of wisdom. What makes this so powerful is that you can actually use this concept when you are writing a formula as well. If I were to do, let’s say, a sum, I can actually do CTRL + SHIFT + DOWN and it’s going to keep that reference for me here, in the selection of those cells, inside of the formula. We’ll get to that a little bit later, but it’s one of those things that applies everywhere. I’m just going to undo that.

I’ve created some exercises for you to practice how to do this. So, if you’re going to start with the empty sheet, you can practice it by using the CTRL and the ARROWS to jump around. If you want to go to this worksheet over here, again, the point is to bold all of the cells and, of course to use the “catapulting” and “schlepping” as you go. So, a lot of these are a little different, but go through them and use the “catapulting,” the “schlepping” and bolding as you go.

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 one quick story. I was watching a documentary the other day, and it turns out The Beatles broke up because they weren’t sharing the Excel love. Teachable moment.

0007 Excel Shortcuts: How to Select Entire Rows or Columns

When it comes to selecting entire rows or columns, there’s only one shortcut you need to know.

In this video, I will teach you the fastest way to select rows or columns, so you can make sweeping changes to your spreadsheet efficiently AND effortlessly.

Download FREE Hands-On Exercises

Full Video Transcript:

You know when you’re on your spreadsheet and you’re really in the zone, and the mood is right and you’re just kind of grooving, but then you have to take your hands off of the keyboard and use the mouse to select the whole row or whole column? Well, nothing kills the mood faster. In my book, at least.

But in today’s video, I’m going to teach you how to use just the keyboard to select an entire row or entire column.

Sure, you can select the entire column by going to the column letter and then clicking, or the entire row by going to the row number and then clicking. But, why would you do that if you can use the keyboard, right?

All you have to do is hit SHIFT + SPACE and you’ll get the entire row selected. If you want to get the entire column, you’ll hit CTRL + SPACE.

Really, really easy to remember because the SPACEBAR is the longest key on the keyboard, which means you’re going to select a lot of cells, regardless. If you use the SHIFT key, it’s a wider, more horizontal, more left-and-right, therefore, it’s going to get you to select the entire row. And the CTRL key is going to be more narrow and more up-and-down, therefore, the entire column.

A couple of Shir words of wisdom. If you actually start on a single cell, you’ll get the entire column if you hit CTRL + SPACE. But if you start with, let’s say, three different cells and then you do CTRL + SPACE, it will expand the selection to include all of those columns. If I’m here right now and I do SHIFT + SPACE, it will expand to all of the rows, in effect selecting all the cells.

I’ve created some specific exercises to reinforce these Excel shortcuts. Go to your rows sheet and actually select the entire row of all the Xs, and move them by hitting CTRL + X on the PC or COMMAND + X on the Mac. Go to the spot you want to go to, and you can paste it.

Now, pasting, you can actually select that one cell at the beginning and it will do the job for you, but I recommend starting a habit now of selecting the same amount of cells as you were selecting before. Meaning, do a SHIFT + SPACE again, and then a CTRL + V to paste it. Both will work, but this is a better habit to start using.

You can also do the same thing with the columns. Select the entire column, CTRL + SPACE, cut it with CTRL + X and then paste it with CTRL + V. But again, select the whole column first, and then paste it. Notice you leave a little mark for where you were.

By the way, one more thing you can do here is you can actually notice that you can select an entire row or series of rows and, in one swoop, apply formatting, let’s say a bold. It’s one of those shortcuts where you can use it with other shortcuts to really maximize your time and be really, really efficient. So, that’s why I love SHIFT + SPACE for the entire row, CTRL + SPACE for the entire column.

Don’t forget to visit test.excelshir.com, where 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 one last word of advice. If you find yourself in a heated argument, stop, take a deep breath and share the Excel love. It’ll defuse everything.

0006 Excel Shortcuts: How to Move to the Beginning and End of Your Spreadsheet

Stop wasting time scrolling with the mouse, and jump instantly to the bottom right or top left of your worksheet using this shortcut duo.

In this video, I’ll show you exactly how to move to the beginning and end of your spreadsheet quickly and precisely.

Download FREE Hands-On Exercises

Full Video Transcript:

Have you ever found yourself scrolling the mouse wheel like your life depended on it, something I like to call “The scroll of shame?” Don’t worry, we’ve all been there.

But in fact, those marathon scrolling sessions are long behind you once you learn this shortcut I’m about to teach you, and it’s actually a shortcut duo that lets you go to the very first and very last cell of your worksheet.

A less common but super valuable shortcut is how to move to the beginning and end of your active worksheet. The way to do it is to hit CTRL + HOME to jump to the top left and CTRL + END to jump to the bottom right.

It all depends on how much data you have in your sheet. So, the way to remember it is you’re catapulting. CTRL sounds like “Catapult,” at least it starts with the same letter, and you’re going to jump really quickly to the beginning, the top left, or the end, the bottom right.

If you’re on a Mac, you’re going to hit CTRL + fn + LEFT, which is really the same as the HOME, and you’re going to hit CTRL + fn + RIGHT to get to the end, to go to the first cell, last cell.

Couple things to keep in mind. If you go to a worksheet that has a lot of data, instead of scrolling around and trying to find the end and kind of struggling to see where you are, hit CTRL + END and you’ll know for sure where the end of your data is, and CTRL + HOME will you bring you back to the top. This is a really great application of when to use this shortcut.

Another example is if you have what seems to be a blank sheet and you do CTRL + END, you might jump very, very far down, and in fact, there might be something as seemingly innocent as a space, but that’s taking up memory on your sheet.

I actually had one client who had tens of thousands, if not hundreds of thousands, of blank cells that were completely not necessary, and the file started out as 70 MB large. That was seven, zero. By removing all those unused cells, we actually cut down the file size by about 95%, so it’s seemingly innocent, but actually can cause a lot of problems.

I’ve created some exercises where you can actually reinforce these shortcuts specifically. So, you’re going to go the X’s over here at the top. You’re going to find the cell that has the first X, hit CTRL + X on the PC or COMMAND + X on the Mac, and now you’re going to use the shortcut CTRL + END to jump to the bottom. Notice you didn’t even have to look for it. It’s there waiting for you because that’s where the end of the sheet is, and you’re going to find that spot and CTRL + V on the PC or COMMAND + V on the Mac to paste.

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 spirit of Oprah Winfrey, share the Excel love. It’ll be your “Aha!” moment.

0005 Excel Shortcuts: How to Switch Between Spreadsheets (Workbooks)

On a scale of 1-10, how annoying is it to switch back and forth between all your open spreadsheets? If you said anything higher than a 1, you’re doing it wrong!

In this video, I’m going to teach you how to switch between open spreadsheets (workbooks) WITHOUT touching the mouse, or minimizing and maximizing windows.

Download FREE Hands-On Exercises

Full Video Transcript:

I know you don’t mean to, but stop futzing around minimizing and maximizing windows and getting all confused when, really, you can keep your workbooks nice and big, and seamlessly switch between them with the shortcut I’m about to teach you.

When you have multiple spreadsheets or workbooks open, it’s really helpful to be able to move between them quickly. So, right now I have A open, but I want to switch between them, so I’m going to hit CTRL + TAB to move to the next one or the next one.

Now, if I keep going with CTRL + TAB, it’s going to cycle through. If I want to go in the reverse direction, I’m going to do CTRL + SHIFT + TAB to go in the opposite direction. Only really useful when you have a bunch open. That’s for PC.

For the Mac, it’s going to be COMMAND + `. What’s a grave, Shir? A grave is that key immediately to the left of the one. It’s got that reverse apostrophe thing going on, and it’s below the little tilde squiggle. So, the grave is the necessary piece. You hit COMMAND + `.

So, now, for the memory tricks. If you’re using the PC, control labs with control tab. Think of your lab as a workbook, right? Your little mad genius workstation here. That’s your lab. You want to control your lab with CTRL + TAB. That’s how you move to the next workbook. If you want to go in reverse, you hit the SHIFT, as well.

With the Mac, you want to window shop with gravitas. You’re window shopping because you’re switching between the different windows, and gravitas because you’re really serious and because you’re using the grave key. It’s pretty straightforward.

For this exercise that I created specifically for this purpose, you’re going to go ahead and start at the beginning. Move to the X that you want to move, hit CTRL + X on the PC or COMMAND + X on the Mac, do your shifting between the workbooks and actually find the spot, and then CTRL + V with the PC or COMMAND + V with the Mac to paste it in. And that is how you actually manage to practice all those things that we talked about.

Some Shir words of wisdom for you is to keep your screen very clutter-free. Close all of the workbooks that you’re not using. You might think that that’s pretty obvious, but believe me, over the years, I’ve seen a lot of clutter. Just keep open the workbooks that are relevant to your current task.

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 like my mom always said, “Life is like a box of chocolates when you share the Excel love.”

0004 Excel Shortcuts: How to Switch Between Tabs (Worksheets)

This is, hands down, one of the most useful shortcuts in Microsoft Excel because no matter what industry you’re in or what kind of project you’re working on, you will have to move between the sheets countless times.

In this video, I’m going to show you how to fly effortlessly from one tab (worksheet) to another with Jedi-like precision.

Download FREE Hands-On Exercises

Full Video Transcript:

This is, hands down, one of the most useful shortcuts in Microsoft Excel because no matter what industry you’re in or what kind of project you’re working on, you will have to move between the sheets countless times. And today, I’m going to show you how to fly effortlessly from one sheet to another with Jedi-like precision.

Instead of clicking with the mouse to go back and forth between your worksheets, we’re now going to use the keyboard. On the PC, you’re going to hit CTRL + PGDN to go to the right, you’re going to hit CTRL + PGUP to go to the left, right? So CTRL + PGDN is the next, or to the right, CTRL + PGUP is previous, or to the left.

Same deal on the Mac, it’s CTRL + PGUP / CTRL + PGDN but to get to those keys, you have to hit the FN + DOWN or FN + UP. But again, it’s the same concept, PGUP/PGDN.

That leads us to the most amazing memory trick ever, at least I think so. Control your pages before they wet all the sheets. So, think of pages as like rambunctious little kids that are going around peeing everywhere. It’s gross, but it’ll help you remember, hopefully. That’s the way to remember it. Control your pages before they wet all the sheets.

In this exercise, I created a specific exercise to practice this over and over again. From the start point, you’re going to move all the X’s. You’re going to go to the cell that has the first X, CTRL + X on a PC or COMMAND + X on the Mac to cut it. You’re going to move over with your CTRL + PGDN until you get to the right spot, and CTRL + V to paste. You’re going to keep going back and forth until you could do this for all of the X’s. That’s your 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. See you next time, and one quick thing before you go. I was texting Chewbacca the other day and he was telling me how he was trying to fix the Millennium Falcon, the Hyperdrive wasn’t working. I said, “Listen, Chewie. Just share the Excel love. It’ll all work out.”