Groups and Subtotals in Excel 2016

Working with a long list of numbers or text in Excel can feel messy. You may spend a lot of time scrolling up and down to find totals or patterns. That’s where Groups and Subtotals come to the rescue. These tools help you organize and summarize your data quickly.

In this guide, you’ll learn how to use grouping and subtotal features in Excel 2016. Don’t worry — it’s simple and beginner-friendly!

What Are Groups and Subtotals?

Grouping lets you collapse or expand rows or columns in a worksheet. This keeps your data neat and easy to read.

Subtotals calculate quick summaries (like sums or averages) for each group of data. For example, if you have sales data by region, Excel can show subtotals for each region and a grand total at the bottom.

Step-by-Step: How to Create Subtotals in Excel 2016

1. Sort Your Data

Before adding subtotals, make sure your data is sorted by the column you want to group.

  • Select the column (for example, “Region”).
  • Go to the Data tab.
  • Click Sort A to Z or Sort Z to A.
Tip: Sorting ensures that Excel groups your data correctly when adding subtotals.

2. Open the Subtotal Dialog Box

  • Click anywhere inside your data range.
  • Go to the Data tab.
  • In the Outline group, click Subtotal.

3. Choose Your Subtotal Options

Now a box will appear. Here’s what to do:

  • In the At each change in box, select the column you sorted.
  • In the Use function list, choose what you want (like SUM, COUNT, or AVERAGE).
  • In the Add subtotal to box, tick the column(s) you want to summarize.
  • Click OK.

Excel will now insert subtotals for each group and a grand total at the end. You’ll also see outline buttons (1, 2, 3) on the left — these control how much detail is shown.

Working with Groups in Excel

How to Group Rows or Columns

  • Select the rows or columns you want to group.
  • Go to the Data tab and click Group.

The selected range is now grouped. You’ll see a small minus (–) sign to collapse it. Click it to hide details. To unhide, click the plus (+) sign.

Using Outline Levels

Excel adds outline levels automatically when you use subtotals:

  • Level 1: Shows only the grand total.
  • Level 2: Shows subtotals and hides details.
  • Level 3: Shows all data.
Tip: You can click numbers (1, 2, 3) at the top-left corner of the worksheet to switch between levels quickly.

How to Remove Subtotals

If you want to start fresh:

  • Click any cell in your data.
  • Go to Data → Subtotal.
  • Click Remove All.

All subtotal rows and outline levels will disappear.

Why Use Groups and Subtotals?

  • They make large worksheets easier to read.
  • You can quickly view summaries without using complex formulas.
  • They save time when analyzing reports, invoices, or sales lists.

5 FAQs of Groups and Subtotals in Excel 2016

1. Do I need to sort data before using Subtotal?

Yes. Excel groups and calculates subtotals based on sorted columns. Sorting ensures accurate grouping.

2. Can I use more than one subtotal in a sheet?

Yes, but it’s better to remove old subtotals before adding new ones to avoid confusion.

3. What’s the difference between Group and Subtotal?

Grouping hides or shows data sections, while Subtotal adds summary calculations like SUM or AVERAGE.

4. Can I edit subtotal functions later?

Yes. You can reopen the Subtotal dialog box and change the function or column anytime.

5. How do I ungroup without deleting subtotals?

Click Data → Ungroup and select Clear Outline. Your subtotal rows will stay.

Conclusion

Grouping and subtotals are powerful tools that make your Excel workbooks clean and easy to understand. With just a few clicks, you can organize long lists, create summaries, and focus on key information. Try it out — your spreadsheets will look more professional and less stressful to manage!

You May Also Like

Loading...