Groups and Subtotals in Excel

Ever wonders how can you edit PDF in Word? Here is how:



Group rows or columns:

  • Select the rows or columns you want to group. For example select columns A, B, and C.
  • Select the Data tab on the Ribbon, then click the Group command.
  • The selected rows or columns will be grouped. In our example, columns A, B, and C are grouped together.

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

Hide and show groups:

  • To hide a group, click the Hide Detail button (-)
  • The group will be hidden. To show a hidden group, click the Show Detail button (+)

Creating subtotals

The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your 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.

Note: Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more.

Create a subtotal:

In this example, we will use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.

  • First, sort your worksheet by the data you want to subtotal.
  • Select the Data tab, then click the Subtotal command.
  • The Subtotal dialogue box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal.
  • Click the drop-down arrow for the Use function: field to select the function you want to use.
  • In the Add subtotal to: field, select the column where you want the calculated subtotal to appear.
  • When you’re satisfied with your selections, click OK.
  • The worksheet will be outlined into groups, and the subtotal will be listed below each group.

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 image of button for levels 1, 2, 3 to the left of the worksheet. In our example, we’ll switch between all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight.

  • Click the lowest level to display the least detail.
  • Click the next level to expand the detail.
  • Click the highest level to view and expand all of your worksheet data.

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

Remove subtotals:

Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you’ll need remove it from your worksheet.

  • Select the Data tab, then click the Subtotal command.
  • The Subtotal dialogue box will appear. Click Remove All.

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

Please leave your comments below with your thoughts or alternative methods.

Nik

Experienced IT Consultant working and living in the UK. In early years he was born and raised up in Greece and been to the UK since studies. He is passionate with technology, gadgets and computers, he really likes the internet and IT in general.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *