Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Before you can begin to group your data, you need to sort it. And that's what we're going to talk about in this movie. By selecting a cell within a particular table, you can then go to your Sort commands, in your Sort & Filter group on your Data tab, and click. It then presents you with the Sort window, where you identify the items that you want a sort on, and the way that you like to sort them on. First of all, you identify which column you want a sort on. In your drop-down list, you'll see all of the item titles that you have in your table.
Let's select Item Category. Next we decide how we're going to Sort On that information. I can Sort On it by Value or by Cell Color, Font Color or Cell Icon, if I'm using conditional formatting. In this case and a use Values. The final decision I need to make is what order am I going to sort the information on? In this case, I have two options. I can sort alphabetically from A to Z, or the reverse from Z to A. I could also create a custom list of orders if I'm interested in doing that.
But I'm going to keep it simple and sort in alphabetical order. This particular sorting ability is very easy, and I can add additional levels if I'm interested, I can copy levels so that I have Sort by Item Category and then Sort by Item Number as my choice. And I can move these different options up and down to organize the way that the information's going to get sorted, simply by using my arrow keys to move them up and down in the order. In this case though, I'm just interested in one level of sorting, so I'm going to select the one that I'm not interested in and click Delete Level.
I'm now ready to add my sorting criteria to my table, and I click the OK button. When I look back at my table, I can see that the information is now nicely sorted by item category. Grouping my flours, my icing, my mixes, my sprinkles and my topics. Now that I've sorted my data, I can start grouping it into like categories. The first thing I'm going to do, is I'm going to select the whole group of the table so that I can do some analysis on all the items in my table. By selecting that, I click on the Group command in my Outline group, click Group, identify that I'm grouping based on the rows that are in my table, and click OK. Now, you'll notice over on the far left hand side, I have a new column that's shown, which identifies the order of the grouping that I've got in place. So, this is showing one large group as my very first level of grouping. And what this is going to allow me to do is to create a summary report when I want to subtotal the information that's in here.
I next want to do one more level of grouping. And I'm going to do that by selecting the individual item categories in my table. As I highlight through my flour, stopping just ahead of my final flour entry, and if I go up to my Grouping command, identifying again that I'm grouping by rows and clicking OK, you'll see that you can create a second level of grouping in your table. You can continue doing the same set of information and grouping, by selecting all of the different categories, going up to the Group command, clicking Group, identifying how you're grouping the information and then clicking OK.
For groups that only have two items in it, just select the first item that you see. Click Group, selects the Group command, click OK, and it's going to grab all the information. It's going to go down to the end of the last instance of that particular category. Select Toppings, click Group, and it's going to group all of the topics together as well. That's how easy it is to create groups. The next thing we want to do is we want to subtotal all of those groups, and we can do that Very simply, by making sure we've selected our table, going over to the Subtotal command button, clicking on it, and identifying in the pop-up box where you want the subtotals to be. So at the end of each item category, you want to use the Sum function, so you want to get the total cost for each category, and add it in to the final costs lists. You want to replace any current subtotals that are in place, and you also want to create a summary at the very end of all your data.
If I click OK, you'll notice that I have now inserted rows in the table for Flour Total, Icing Total, Mix Total, if we scroll down even further, I'll see the total for my sprinkles, my toppings and a Grand Total. If you collapse any of the different total screens, you'll be able to create a real nice summary of all the information that you have in your table. You've now seen the real power of grouping.
In our next movie, we're going to see how you can create a summary report with only two clicks.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91099 Viewers
80 Video lessons · 138153 Viewers
59 Video lessons · 56967 Viewers
52 Video lessons · 70601 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.