Groups and Sub-totals in MS Excel 2013

Excel 2013 - Groups and Subtotals

MS Excel can organize large amount of data of worksheets in groups, allowing you to show and hide different sections of your worksheet easily. And aslo to summarize  the different groups using  Subtotal command and create an outline in a worksheet

To group rows or columns:

1.    Select the rows or columns you want to group.  Here we'll select columns A, B, and C.

2.     Click  the Data tab on the Ribbon, then click  on the Group command.

3.    The selected rows or columns will be grouped. 

To ungroup  the grouped data , select the grouped rows or columns, then click on  Ungroup command.

To hide and show groups:

1.     To hide a group, click the Hide Detail/- button.

2.     The group will be hidden. To show a hidden group, click the Show Detail /+ button.

Creating subtotals

The Subtotal command is used to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize the data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. It will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Data must be  sorted before using the Subtotal command.

To create a subtotal:

Let us use the Subtotal command with a T-shirt order form to check  how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). An outline for the worksheet with a group for each T-shirt size and then count the total number of shirts in each group will be created.

1.     First of all , sort worksheet data for which subtotal is required. Suppose we want to create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size from smallest to largest.

2.     Select the Data tab, and  then click on the Subtotal command.

3.      Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal. Select T-Shirt Size.

4.     Click the drop-down arrow for the Use function: field to select the function you want to use. select COUNT to count the number of shirts  as per ordered in each size.

5.     In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. select T-Shirt Size then click on OK

6.     The worksheet will be outlined into groups, and the subtotal will be listed below each group.

To view groups by level:

When you create subtotals, your worksheet it is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons  to the left of the worksheet. While this example contains only three levels, Excel can show up to eight levels.

1.     Click the lowest level to display the minimum detail. if we select  level 1, it will show only the grand count, or total number of T-shirts ordered.

2.     Click the next level to expand the detail. In the  level 2, it only  contains each subtotal row and hides rest of the  data from the worksheet.

3.    
Click on the highest level to view and expand all the data  of our worksheet .

You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.

To remove subtotals:

 When you don't need to use subtotal then just  remove it from the  worksheet.

1.     Select the Data tab and then click on  Subtotal command.

2.     in the Subtotal dialog box  Click on Remove All.

3.     This will ungroup all worksheet data , and also the subtotals will be removed.

To remove all the groups without deleting  subtotals, click the Ungroup command's  drop-down arrow, then choose Clear Outline.