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.