Instead of formatting the tabs in your spreadsheet one at a time, use the shortcut in this video and save yourself hours of manual, tedious work.

Download FREE Hands-On Exercises

Full Video Transcript:

Ever find yourself doing the same work twice across multiple tabs, also known as worksheets?

If so, you cannot afford to miss this video because I’m going to teach you how to cut that time dramatically down.

This shortcut is extremely powerful because it lets you apply formatting to multiple worksheets at the same time. However, there are a lot of pitfalls that you want to avoid. So, watch closely and don’t make these same mistakes that I made.

The whole trick is to use the SHIFT + CTRL + Page Up or Page Down to select your current sheet that’s active and another one along with it. So, for example, I’ll show you how this works. If I’m on the cover page right now, and I move and select all three of these, additionally. Because I’m here, I’ll hit CTRL + SHIFT + PGDN, and keep going with the PGDN while keeping CTRL + SHIFT pressed to expand that selection across all of these worksheets.

Now, to deselect, you want to actually move one past the selection. So, right here I would hit CTRL + PGDN and now all of them are deselected. That’s how you want to deselect it. You could also use the mouse, but why use the mouse when you can use the keyboard?

Let’s go back and see how this works with the Mac. It’s pretty much the same thing because you have your SHIFT + CTRL + fn + DOWN, which is the same as PGDN, and SHIFT + CTRL + fn + UP, which is the same as PGUP. So really, it’s the same concept, and the whole point is to select multiple sheets at the same time.

The memory trick is, you want to schlep and control your pages before they wet all the sheets. Think of schlepping as carrying or lugging, taking with you, and think of controlling your pages as, like, rambunctious little kids that are peeing everywhere. It’s a little gross but I think it helps you remember.

So, why would we possibly, actually want to use this? Well, there’s a couple key, important things here. The most important thing is, if you have all your sheets in, like a report, for example, that are structured the same way, and you want to make changes to all of them, this is where you would use this shortcut.

So, CTRL + SHIFT + PGDN. And again, we now have these three selected. If I make changes to the cells on this one, then all three of them will get that same change applied. It only works if they’re structured and they’re positioned the same way.

So, why all the warnings here? You don’t want to actually forget that you have these sheets selected together because you might be doing something here and making some changes, and then not realize, “Oh, wait a minute, I had all of them selected. I made some crazy changes. Didn’t mean to do it.” So, my huge word of caution here is, as soon as you’re done making your vast changes across all of them, just deselect them immediately so that you’re not in that position.

But I could go on and on all day about how this stuff works. For now, I want you to practice these exercises to actually internalize and remember these shortcuts. So, go ahead and make the left match with the right. And the key is to select all of the worksheets that are grouped together first, and then match the format by bolding the cells. If you do that correctly, you’ll be able to do it a lot faster. That’s the whole point.

So, go through grouping A. And when you’re done with that, go through grouping B. And when you’re done with that, guess what? there’s a C involved as well. Get everything to match up and then you’ll be all set with the grouping of the multiple tabs 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 one last request, put a smile on someone’s face today after sharing the Excel love. They’ll never stop thanking you. I wouldn’t.

Detailed spreadsheets are great, but sometimes seeing everything all at once can be overwhelming and confusing.

In this video, I will teach you how to group and ungroup rows and columns to only show what’s relevant and important.

Download FREE Hands-On Exercises

Full Video Transcript:

Have you ever looked at a spreadsheet and had the reaction of, whoa, too much information? If so, chances are that there’s information that could have been grouped and hidden from view.

In this video, I’m going to teach you the shortcut on how to group and ungroup rows and columns.

In the previous video, I showed you how to hide and unhide rows and columns. In contrast, this is all about grouping and ungrouping rows and columns which is actually the same thing except for one key difference.

So, I want to go ahead and select a couple of columns and then use my SHIFT + ALT + RIGHT if I’m on a PC or SHIFT + COMMAND + K if I’m on a Mac. What happens is not only am I able to expand or collapse by using the plus or minus, but I can actually interact with this interface in such a way that it’s visible and it’s obvious and it’s clear to the user, which is something that does not happen with hiding and unhiding.

To do the reverse, to actually ungroup the cells that have been grouped already. What you want to be able to do is select those cells, right, that actually have the grouping and then use your SHIFT + ALT + LEFT if you’re on a PC or SHIFT + COMMAND + J if you’re on a Mac and that will ungroup it and then you’ll lose the ability to hit the plus or minus sign.

Fun little memory tricks on how to keep these shortcuts at the top of your mind, if you are on the PC you want to think of shifting your alternative political views to the right. Think of right as conservative closed, hidden, grouped together.

In contrast, the shifting the alternative political views to the left. Left is more liberal, open, exposed, ungrouped. That’s the whole point. So, shift your alternative political views to the right, you get the deal.

On the Mac, the memory tricks are a little bit cuter I think, kumbaya is all that coming together as one group and jerk I don’t like camp songs. You’re isolated, you’re alone, you’re ungrouped. It’s kind of sad actually. But those are the keys associated with it.

One last thing I’ll point out on this page is you can actually use other shortcuts in combination with this such as the CTRL + SPACE or SHIFT + SPACE to select the entire column or row and the alternative is the unhiding columns or unhiding rows as I mentioned earlier.

This is great, Shir, but when would I use it? Well, let me show you some practical applications of it.

Here we have some data broken out by month, quarter and even by year. So, what would be useful is to in fact group, I’m going to hit CTRL + SPACE to select the entire column and shift to the right a couple times and select those cells. Now I’m going to hit SHIFT + ALT + RIGHT to start grouping the months. Notice I’m skipping over the quarter.

I’m going to do the same thing with Q2 or rather the months for Q2 the months for Q3, etc. until I get to the end. Now notice I can use the mouse to go like this and collapse each of these, so the only thing left is the relevant information I want. You’re actually taking up less space on the screen which is super valuable to get more information visible.

This is really applicable to many different scenarios especially when you’re rolling up data to show only high-level stuff. This is great for sales, for I don’t know marketing, pretty much any industry would be useful to have this.

If you don’t like to use the mouse at all like me, then when you’re expanding these cells over here and you don’t want to have to click on the button themselves, you can actually cheat a little bit by using the shortcuts in the previous video by in fact doing CTRL + 0 to hide which has the same effect as hitting that minus, it’s a nice little cheat.

And just like you can hide using CTRL + 0 to actually hide those columns, you can do the reverse by selecting all of them CTRL + SHIFT + 0 to show them which is the same as hitting the plus.

Now I’ve just given you quite a lot to work with but if you want to take this even further, you can actually group within groups. So, what I mean by that is we have all of our months here and all of our quarters but I want to actually group it also by the year.

So, I’ll take all of these, I’ll do my SHIFT + ALT + RIGHT and now I can collapse everything so only the year is showing, or if I go to level 2 only the quarters are showing, or level 3 all of the data is showing.

But enough about me, it’s time to actually have you try this out using some exercises to really internalize these concepts and these shortcuts. So, go ahead and make it all the way to the group columns exercise where you can actually say, “You know what, let’s take all three of these, let’s do my SHIFT + ALT + RIGHT” and because I didn’t actually select the columns first, I now get a pop-up, “What do you really want to do?” I wanted the columns.

So, this is why it’s a great tip to first select the columns or rows depending on what you want to do and then using the SHIFT + ALT + RIGHT. And so now I have that same option and I can collapse it that way, same idea here, let me do my CTRL + SPACE first, SHIFT + ALT + RIGHT and now I can collapse it and it looks identical.

Do this for all of these exercises and when you’re feeling frisky, go ahead and create a little challenge for yourself.

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, neither snow nor rain, nor heat, nor gloom of night should prevent you from sharing the Excel love. I may have borrowed that one.

Ever wish you could temporarily hide certain cells in your spreadsheet, without deleting them?

Well you can, and in this video I’ll show you how with a few easy to remember Excel Shortcuts.

Download FREE Hands-On Exercises

Full Video Transcript:

Have you ever gotten a spreadsheet from a colleague that had way too many columns that you really couldn’t delete, but you wanted to temporarily hide?

In this video, I’m going to teach you how to hide whatever you don’t need and then bring it back in the end to leave your spreadsheet and your brain clutter-free.

Hiding and unhiding rows and columns is one of those things that is super helpful, especially if you’re trying to declutter your sheet temporarily, or even if you really don’t need to show things at all times.

Another way is to have someone give you a sheet that you’re not quite seeing everything and you know there’s something behind the scenes, this is a great time to try unhiding first before, you know, basically yelling at them. That’s how you want to keep in mind how to do this.

Now, to actually do it, you’re going to use the CTRL + 0 to hide your columns, CTRL + 9 to hide your rows. Now, notice it kind of works in pairs. To hide it, you’re using CTRL. To unhide it, you’re going to add the SHIFT key. So, let’s go through these.

If I’m highlighting this cell here and I hit CTRL + 0, it’s going to hide that entire column. If I select numerous cells first and then do CTRL + 0, it’s going to hide them all as one. That’s pretty much how that works.

And if you want to unhide, here’s the trick. You have to overlap the selection so that the hidden columns are included in the selection. Then you do your CTRL + SHIFT + 0 to bring it back to life.

Same exact concept for the hiding of the rows. CTRL + 9 will hide that one. If I do a couple of selected cells first, CTRL + 9 will get all of those hidden, and again, I can now choose a whole bunch of rows that overlap it, CTRL + SHIFT + 9, and they’re back as they were.

One of those rare times where Mac and PC are identical, and really, just enjoy anytime that happens, because it doesn’t happen very often.

And there’s a couple memory tricks, too, to keep these in the top of your head. So, if you want to think of the 0 and the 9 as sharing the keys with the parentheses, it’s like a hidden thought, so therefore you’re hiding certain rows or columns.

And again, CTRL by itself will hide it, but CTRL and SHIFT will reverse that action and actually unhide or bring them back to the way they were.

I’ve created some exercises to help you really internalize and remember these shortcuts, so go through and make the section on the left match the example on the right. And in this case, you’ll have to go ahead and select certain cells, and CTRL + 0 to hide it.

You might select numerous and do it all at once, but the point is to make them match identically. Go through, try these out. You may have to do some unhiding first. And in the challenge, you can make a smiley face, and I’ll be happy.

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 meanwhile, don’t forget to share the Excel love, because it just makes everything better.

Ever find yourself doing the same thing in Excel over and over again? If only there was a single keyboard shortcut to repeat your last action.

Fortunately there is, and it’s the subject of today’s video!

Download FREE Hands-On Exercises

Full Video Transcript:

I’m going to let you in on a little secret. There is a single keyboard shortcut that can be used to repeat the last action, whatever that action may be, and in this video, I’m going to teach you the least known and most helpful formatting shortcut.

F4 is one of my favorite shortcuts, because it’s extremely versatile and can be used to do pretty much anything. Some great examples are to repeat Insert Cells, repeat Highlight Cells, or repeat copying and pasting of formatting.

We’ll go through those in a second, but really, think of it as just the Fantastic Four fixing broken records. Why broken records? Because it’s happening over and over again. And Fantastic Four, F4. I mean, that’s pretty cool.

So, what you want to keep in mind is it’s the same shortcut for both PC and Mac, and it’s one of those rare times, so really enjoy that.

There’s one drawback here, which is that you might have a situation where you can kind of lose your work as you go if you do something in the middle. So, if I’m actually taking this, and let’s say pasting the formatting here all at once, I’m going to now repeat that action over here, over here. And notice how it’s doing this, but if I do something else and I try and repeat now, it won’t do it because I did something recently. So, keep that in mind as you go through.

I’ve created some exercises to practice and really internalize these shortcuts. So, go ahead and make the left section here match with the section on the right. So, use that Insert Cells to CTRL + + (plus) shift cells to the right, and now, instead of doing that same shortcut, just hit your F4, F4. Same idea here. It will remember exactly what you did.

Even cooler is that if you choose it a little differently, and you do CTRL + + (plus) over here, and do Down, it’s going to remember that as opposed to to the right. A nice little nugget for you to enjoy.

Same concept with repeat Highlight Cells. As soon as you highlight it, you can actually repeat that, which was then your last step. So, highlight it again and hit F4, it’ll apply to that new selection. You get the idea. Enjoy and actually practice this until you get it.

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 see you next time. Don’t forget to share the Excel love. You thought I was going to forget, didn’t you?

Instead of hitting the “Panic Button” whenever you make a mistake, use this Excel Shortcut to Undo the last action.

There are a few key things to keep in mind though, so watch this video and learn how to undo and redo like a boss.

Download FREE Hands-On Exercises

Full Video Transcript:

If you’ve ever accidentally deleted something and wanted to bring it back to life, before having a nervous breakdown, use the shortcut I’m about to teach you.

Undo is a spectacular shortcut because it lets you make mistakes and still be able to recover from them, and Redo is the perfect complement to it because it undoes the Undo, which actually sounds really more confusing than it should be, but really, all I’m saying is CTRL + Z will undo and go back in time to the last action, and CTRL + Y will go forwards in time, or redo the last action.

So again, on the PC, it’s as simple as that. CTRL + Z to Undo, CTRL + Y to Redo. Mac, same idea. COMMAND + Z and COMMAND + Y.

Think of it this way. The Z is the last letter of the alphabet, and to undo the last thing that you did. And of course, if you go too far, well, “Y did you undo that? I wanted to keep it in there.” That’s your little memory trick for you.

And why not put these shortcuts to use in this following exercise right here, where you take this format over here and then actually make it look like it does on the side here? All you have to do, really, is clear contents with the little DEL key, and then when you’re done with all of this, you’re going to undo everything and then redo everything, and you’re going to see it play out in magical slow motion. It’s going to be amazing.

This is actually one of those other helpful tips, couple of Shir words of wisdom for you. You can actually go back in time a number of different steps, but don’t ever rely on this, because sometimes it will actually not go back any further if you push the limit here. So instead, save copies, save backups, before you do any kind of major change, anything important.

Also, you want to use the CTRL + Z and CTRL + Y, the Undo and Redo, for a quick visual comparison, like, “What does it look like with it, what does it look like without it?” and this is a great way to kind of visually compare how two things look without having to essentially retype everything every time. So use that, enjoy it, and learn it really well.

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 brilliant words of FDR, “The only thing we have to fear is not sharing the Excel love.” Words to live by.

If you are still re-formatting your spreadsheets from scratch every time, you are throwing away hours of your life needlessly.

Instead, you can copy and paste formatting only, using the shortcut in this video.

Download FREE Hands-On Exercises

Full Video Transcript:

Copying and pasting formatting is one of the most time-saving shortcuts you will ever learn, because it lets you take your previous work and apply it anywhere you want in a snap.

You may not know this, but when you’ve been copying and pasting for all these years, you’re pasting everything that came with it. So, the formulas, the formatting, the other properties behind the scenes that you may not have even seen. The beauty of this shortcut is that you learn how to copy and paste the formatting only, and the key to this is with Paste Special.

So, the only way this works is, first, you’re going to actually take the cell that has the formatting that you want, select it, copy it with CTRL + C on the PC and COMMAND + C on the Mac, and then choose the cell that has…essentially where you want to apply it to, and then you’re going to do a CTRL + ALT + V on the PC, right? You’re doing a regular paste with CTRL + V, but a Paste Special by adding the ALT key in there, which brings you to this Paste Special window.

You can move up and down with the arrows, or better yet, you can actually jump to the specific option you want by going to whatever’s underlined. So, ALT + T for formats, and notice that you can just hit ENTER and apply this formatting to this spot right here, hit ESC to get out of it.

So, the key is to copy first, then choose where you want to apply it to, CTRL + ALT + V for Paste Special, and ALT + T to jump to that spot on the window.

On the Mac, it’s very much the same thing, the shortcut is a tiny bit different. And so what you need to do here is copy with COMMAND + C, choose the cell you want and CTRL + COMMAND + V to get the Paste Special, and this time you’ll hit the fn + T for format.

Basically, you can’t use F because that’s for formulas. So, the T over here is underlined, on the PC at least, and it gives you an indication. So, here you’re just kind of flying blind, but it’s okay, just hit ENTER and it’ll apply it for you, and hit ESC to be out of it. And that’s basically how that works.

Let’s now take this in action and go to some exercise where you can actually drill this in and remember these shortcuts by practicing it over and over again, take the left side over here and make it look like the right.

And in this case, you’re going to take this format over here, you can copy a bunch at once like so and Paste Special the format only. And then you’ll have exactly the right look.

Notice there’s some border issues going on, so you can’t do it all at once, you may have to do it in sections. But I’ll let you figure that one out because that’s even more fun that way.

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 I believe it was Tony Robbins who said, “The path to enlightenment is through sharing the Excel love.” I’m a big fan.

In Microsoft Word pressing ENTER will give you a new line. However, in Microsoft Excel, you need to use a specific shortcut, which is the topic of this video.

Download FREE Hands-On Exercises

Full Video Transcript:

Ever try to add a new line inside of a cell only to hit ENTER and have it move down one cell? Super annoying.

But in this video, I’ll teach to how to bypass that habit that Excel has and add that new line inside the cell.

Adding a new line inside of a cell is great when you want to cut off specific words inside of like a header and you want to force it to look a certain way. And every time you’ve tried to do it with hitting ENTER and it not working, it’s because you’re not using this shortcut properly.

So instead, you’re going to be writing out your cell. I’m going to actually double-click on the cell to edit it, or I can use this additional peripheral helpful shortcut of F2 to actually edit the cell contents and have the cursor go at the end.

And now, I’m going to hit ALT + ENTER to force that new line and say, “awesome” over here, and hit ENTER to save or complete that cell entry, and now, it’s going to force that new line because I did the ALT + ENTER, instead of just ENTER by itself.

If you’re on a Mac, on the other hand, you’ll be using OPT + RETURN to create that line break inside the cell. F2 is the same, or you can even use CTRL + U, not COMMAND, but CTRL + U to edit the cell contents, and RETURN to complete the cell entry.

Couple things to note, CTRL + OPT + RETURN might work instead of OPT + RETURN. And if F2 doesn’t work, you might want to try fn + F2. A whole bunch of little quirky nuances here, just read through this and try it out. You can also change the system preferences and make it more standard and more easy to use.

So that’s a little bit more quirky for the Mac. PC is pretty simple, just ALT + ENTER to create that new line inside of the cell.

A great way to practice this specific shortcut is to go to this exercise here and create that new line inside of these cells, and actually from there, ALT + ENTER and type the word “line,” hit ENTER, and do that for all of these, and make it look exactly the same from left to the right. Have it match and you’ll be great.

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 as they say, “The devil’s in the details,” unless you share the Excel love, then you’re all set.

After painstakingly changing your column widths, there’s no need to do that work over again. Just use the shortcut I’m about to teach you to copy and paste the column width in a snap!

Download FREE Hands-On Exercises

Full Video Transcript:

If you’re anything like me, you probably spent the better half of your adult life not knowing that you could copy and paste column widths in Excel.

Now that you know it’s possible, today I’m going to teach you exactly how to do it.

In the previous video, I showed you how to change column widths and row heights. You can actually go ahead and click on the link in the description in case you missed it.

Now that you’ve done that work already, I’m going to teach you how to copy and paste only the widths of the columns from one section to another, using a little something I like to call “Paste Special.” Since Microsoft calls it Paste Special too, that’s what we’ll all be calling it from here on out.

Paste Special’s one of those amazing things that, if you’re using it correctly, you’re saving yourself tons of time every single time that you’re using Excel, but if you don’t use it, you’re really missing out.

The key to it is to first choose and select the cell that has the quality that you want to it. For example, if I want to take this cell right here, and actually this entire column really, I’ll go ahead and expand the selection using CTRL + SPACE. I’ll do a little CTRL + C to copy it.

Now, I’m not going to paste the actual contents, but what I will do is paste the width of the column. I can go ahead and do CTRL + SPACE again to select this column here, or I can just choose a single cell and do CTRL + ALT + V. Now notice what happens. I threw an ALT in there. Instead of doing a regular paste with a CTRL + V, I’m using CTRL + ALT + V, which brings me to Paste Special.

And of course, the magic here completely is the column width. I can jump to this even quicker by using the ALT + W on the PC to get to that spot immediately. I’ll hit OK, and it’s now going to obviously give me an error message, because why not? In fact, that thing I said before, let’s make it width, and then it’s going to work. See, it’s always better to make it like to like. If you’re doing a whole column, then paste a whole column. Otherwise it’ll yell at you, basically.

That’s how you want to do it. I can hit ESC to get rid of these little borders here. It’s just showing it’s on the clipboard. The whole point is, you’re doing a Copy and a Paste Special with just the column width.

Let’s see this in the Mac. It’s very much the same thing. The whole point on the Mac is that instead of doing it with…let’s do, for example, over here. Same concept. I’ll do CTRL + SPACE to get the whole column, COMMAND + C to copy. I’ll do CTRL + SPACE to get this new column over here, and do CTRL + COMMAND + V to bring my Paste Special window up here.

The only difference is, I’m not going to do ALT, I’m going to do a fn + W. I hit the fn + W, it will get you to this spot right here. Then you do OK, and it will apply that width to there as well. Hit ESC to get rid of that.

If you want to lock in this shortcut, then let’s go ahead and go to this exercise, where you’re actually going to take the original column width over here, you’re going to copy it, and you’re going to paste it over to these.

It’s really, really simple, but the whole point is to get it to look like it does on the right. Use that shortcut and then do it as many times as you need to until you can do it with your eyes closed. Enjoy.

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, every journey begins with a first step. May I suggest that first step be to share the Excel love. I read it in a fortune cookie once. It was delicious.