Interested in becoming an Affiliate Trainer? We are now accepting applications for local and international positions. No need for cold calling! Receive referrals of people looking for specialized training in your area and get access to all of the training curriculum I have created and continue to create to use at your appointments.
Outlining is one of the best ways of making your data more manageable. You can use it to automatically use subtotals, to group certain rows or columns together and also to hide details you don’t need to see.
In this example, we have the different levels of kits sold in March by four regions. We want to know how many of each level were sold in March, so we will use the Subtotal command.
Before you create subtotals, you will need to sort your data. We have already sorted ours by the different levels of kits sold.
Go to the Data tab and select the Subtotal command from the Outline group.
The Subtotal dialog box will appear, as shown in the image to the left.
In the first field, we will choose the column that will be the basis for the outline which is Kits Sold.
Then you will need to pick the function you will be using. We will select Count which will just count the number of cells that contain each level of kits sold.
In Add subtotal to, we will select the column where we want the subtotal to appear (Kits Sold).
Select OK after making your selections.
Each level of kits sold is now in its own group as shown in the image below. All of the smaller groups are in a larger group. This is called an outline. The other thing that has happened is that each level of kits sold now has a subtotal under it, so we can see the number of kits sold in each level. At the very bottom we can see the total of all the kits sold.
To make the spread sheet easier to read, you can hide the details of each group, so that you only see the subtotals and the grand total. You can hide a group by clicking the minus sign, and to show it again click the plus sign.
You can also show or hide groups by level by clicking on the numbers in the upper-left side of your worksheet as shown in the image to the right.
The highest number, which is 3 in our example, will show you all of the data.
Level 2 will hide the details of each kit sold, so we can still see the individual subtotals (as shown in the image to the left).
If you decide you don’t want part of your spreadsheet to collapse, you can Ungroup it. I would like to Ungroup the Value kits, so I will select the cells that contain the Value information. Select the Ungroup command from the Outline group. When a window pops up, select Rows and then click OK.
All of the groups are in a larger group, so we will need to click Ungroup again to completely ungroup it.
Now you can see that the brackets are gone from these rows and they won’t collapse when you change the level.
You can also ungroup everything and remove all of the subtotals by going to the Subtotal command and selecting Remove All.
You can create your own groups of either rows or columns. In this example, we don’t really need to see the Region, so we will select that column and click the Group command. Now, we can just hide this group by clicking on the minus sign above the column.
Grouping data can make your spreadsheet less cluttered, more organized and easier to view.
This concludes the Data tab tutorial.
Click next to continue to the Excel 2010 Review Tab tutorial.