0031 Excel Shortcuts: How to Format Multiple Tabs (Worksheets)

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

Leave a Reply

Your email address will not be published. Required fields are marked *