Hi, this is Wayne again with a topic “Grouping Rows and Columns in Excel”.
In this article, I’m going to show you how to group rows or columns in Microsoft Excel. Here I have a list of all of the NBA basketball teams and they’re divided up by division, and this is a really good way to organize the list of basketball teams. But what if I would like to hide or show the list of teams based on the division that I’m interested in? How could I do that? Well, it’s as simple as clicking and dragging to highlight the range of data that I would like to hide and then going here to the data tab. Looking over here on the right side of the data ribbon you’ll find outline now for you. You might see the option we’re looking for without having to click on outline, but for me because of the screen size of the computer, I’m working with.
I have to click here and then choose group when I do that: a dialog box pops open and I need to choose whether I want to group rows or columns, I’m going to leave it at rows. Click, OK and it looks like almost nothing happened, but what we have here at the left is a vertical line, indicating that this range is now grouped and I can click this button here to collapse. The group, so I could do the same thing here with the Central division now this time, instead of going to data outline group, I’m going to use a keyboard shortcut and that is you hold alt, hold shift and then tap the right arrow key on the keyboard. When you do that, you get that same dialog box that pops open I’m going to choose rows, click! Ok! Now I can collapse the Central Division so give me a minute to finish doing this for each of the divisions and then I’ll resume the video okay, great. I’Ve finished grouping all of the NBA teams based on their divisions.
So now, let’s say I’m interested just in the Pacific Division. I click the plus sign here and all of those teams are revealed if I’m interested in the Southwest Division. I click here and all of those teams are revealed and again I can collapse those just by clicking the minus Signs Now here in the upper left corner.
We also have some buttons that we can click there’s a number one. If you click the number one, it takes you to this top level where it just shows the list of Divisions. If you click on two, it expands everything. So I can quickly collapse everything and expand everything by using these two buttons in the upper left, or I can use these individual buttons here to collapse, each division, one by one that I would like to collapse and that way I can see the specific information I’m Looking for now, in addition to grouping based on rows, we can also group based on columns, so I’ve copy pasted the same data onto a new tab.
If I now select all of the teams in the Atlantic division, I can hold alt shift right arrow, but this time I’m going to group based on columns, I click. Ok and now my data is collapsible in a different way up here at the top there’s a minus button, and you can see in this case it doesn’t make a lot of sense to collapse The Columns, but I just wanted you to see that that is possible And to get a glimpse of how that works jumping back to sheet 1. What would I do if I wanted to remove these groups that I’ve created? All I would need to do is click and drag to select the range that I want to stop being a group and then go to the data tab. Data ribbon here on the outline button, I’m going to click and you can see in addition to group, there’s also an option to ungroup. So I’ll just click there do. I want to ungroup the rows or the columns.
Definitely the rows, and I click. Ok and now you can see my line here at the left is gone because this range is no longer grouped one last thing that I want to point out here on my second sheet: I’m going to ungroup the columns that I had grouped, and I want you To see that there is an option that we have on the data tab here on the outline button. If you click there, there is an option for grouping. If you click here on the Arrow, not on the button itself, but the arrow there is an option to try to do an auto outline. Now this sometimes works and it sometimes doesn’t.
I don’t think it’s going to work in this case, but you can try it so I’m going to click here, Auto outline it says, cannot create an outline. So that’s something that you can try if you would like, doesn’t always work when it does work. What happens is Excel will automatically apply groups to the spreadsheet, based on its own logic, its own reasoning.
Sometimes it works well. Sometimes it doesn’t now one last tip that I have for you when it comes to grouping rows or columns in Excel. I’Ve taught you the shortcut key for grouping rows or columns. You click and drag to select the range that you would like to group hold.
Alt hold, shift tap the right arrow and then choose rows or columns. Okay, fine, that’s working great, but there’s also a keyboard shortcut for removing the group. You can probably guess what it is: it’s alt shift left arrow and then you select rows or columns, click, OK and that group is removed. So I hope you can see the potential that this technique has in your Excel spreadsheets. You can use it to organize your day, data, to make it easier for people to read and to find the data that they’re looking for, while ignoring the other data, at least temporarily thanks for watching. I hope you found this tutorial to be helpful.
If you did please like follow And subscribe, and when you do click the Bell so you’ll be notified. When I post another video, if you’d like to support my channel, consider clicking the thanks button below the video or you can support me through my patreon account or by buying Channel merch and you’ll, find information about those options in the description below the video. Speaking of patreon, I want to give a quick shout out to my five dollar patreon supporters. Thank you. So much for all you do to support the channel without you, I wouldn’t be able to post as many videos as I do, and I really appreciate actually all my patreon supporters, not just at the five dollar level. Thank you.
.