Excel 2024: Sort Subtotals

April 24, 2024 - by Bill Jelen

This tip is from my friend Derek Fraley in Springfield, Missouri. I was doing a seminar in Springfield, and I was showing my favorite subtotal tricks.

For those of you who have never used subtotals, here is how to set them up.

Start by making sure your data is sorted. The data below is sorted by customers in column C.

Columns for Sales Rep, Date, Customer, Quantity, Revenue, Cost, and Profit. The data is sorted by Customer (column C).

From the Data tab, choose Subtotals. The Subtotal dialog box always wants to subtotal by the leftmost column. Open the At Each Change In dropdown and choose Customer. Make sure the Use Function box is set to Sum. Choose all of the numeric fields, as shown here.

The Subtotal dialog box says: At Each Change in Customer, Use Function Sum, Add Subtotal To Quantity, Revenue, Profit. The boxes for Replace Current Subtotals and Summary Below Data are checked. The box for Page Break Between Groups is unselected. There are three buttons at the bottom of the dialog: Remove All, OK, and Cancel. To add the subtotals, click OK.

When you click OK, Excel inserts a subtotal below each group of customers. But, more importantly, it adds Group and Outline buttons to the left of column A.

More Reading

Excel 2024: Sort Left to Right »

April 22, 2024 - by Bill Jelen

Every day, your IT department sends you a file with the columns in the wrong sequence. It would take them two minutes to change the query, but they have a six-month backlog, so you are stuck rearranging the columns every day.

Excel 2024: Sort East, Central, and West Using a Custom List »

April 19, 2024 - by Bill Jelen

At my last day job, we had three sales regions: East, Central, and West. The company headquarters was in the East, and so the rule was that all reports were sorted with the East region first, then Central, then West. Well, there is no way to do this with a normal sort.

Excel 2024: Set Up Your Data for Data Analysis »

April 17, 2024 - by Bill Jelen

Make sure to follow these rules when you set up your data for sorting, subtotals, filtering and pivot tables.

MrExcel 2021 - Unmasking Excel

February 2021

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.

Microsoft 365 Excel: The Only App That Matters

June 2022

Excel Worksheet, Power Query, Power Pivot, Power BI. Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight!

Cool Excel Sh*t

March 2021

Cool Excel Sh*t is designed with the Excel guru in mind, introducing advanced, creative solutions and hacks for the software's most challenging problems.

Programming PowerPoint with VBA

February 2022

This book assumes you already use PowerPoint and want to automate or enhance your presentations using Visual Basic for Applications (VBA). This book includes VBA samples for working with layouts, themes & masters, creating tables, drawing objects, charting, animation effects and event programming.

see more products »

Excel 2024: Sort Subtotals

More Reading

Featured Products