MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summarize Data with Subtotals


April 09, 2018 - by Bill Jelen

Summarize Data with Subtotals

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. I typically solved this using the Subtotals command. But during a recent seminar in Dallas, an attendee suggested there is a faster way. This week is Summarize Data week and I will look at five different ways to solve the problem.

The goal is to summarize this 563-row data set so you have one row per customer.

Create a summary with 1 row per customer
Create a summary with 1 row per customer

This week will feature five different ways to solve the problem.


Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2187: Summarize with Subtotals.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. I was doing a seminar down in Dallas and doing one of my classic tricks, where we take this data-- 563 rows of data-- and summarize it down to a nice one-page summary using the Subtotals command. Alight?

And so, we start out-- we want to make sure that the data is Sorted by Customer-- choose one cell in the Customer column. On the Data tab, click the A to Z button to sort by Customer, then we're going to use the Subtotals command. So, from this point: Data, Subtotal. Subtotal always wants the subtotal by the leftmost column so we have to open this drop-down, choose Customer, Sum, and then we're going to choose those four columns. Choose OK. And what we'll get is at each change in customer, they've added a new row with the total for that row-- beautiful. Now, click the number 2 Group & Outline button; these are added by the Subtotal command; we now have a list of all of our Customers. Start here from cell D1, Ctrl+Shift+Down Arrow. I don't let go of Ctrl+Shift, I press Right Arrow-- selects all of my data. I want only the visible cells-- that's Alt+Semicolon, Ctrl+C to copy, Ctrl+N for New, Ctrl+V to paste, and we have our one-page summary. I guess we could double-click here to make it wide enough-- like that.

Now, I was doing this trick, and Adam from Dallas says, "Hey, there's a faster way to do that." Alright, and I realized there's actually five ways to do that. So this week, say this week we'll be taking a look at a different way. I'm starting out on my Summarizing Data week, using my favorite method using Subtotals; but each day, we'll take a look at a different way to go.

Now, all of these methods are in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time. Click that "I" on the top right-hand corner to be taken to a link for the book.

Alright, wrap-up: Today's Episode, we're talking about summarizing data in Excel using Subtotals; this is the first of a 5 part series on summarizing data. We're going to look at Remove Duplicates, Advanced Filter, Consolidate, and Pivot Tables throughout the week. To summarize the data with Subtotals, we have to sort the data by Customer; and then go to the Data, Subtotals, and each change in Customer, choose the four numeric columns; click OK; number 2 Group & Outline button; and then from the Customer heading, Ctrl+Shift+Down, Ctrl+Shift+Right; Alt+Semicolon to select the visible cells; Ctrl+C to copy; Ctrl+N for new workbook; Ctrl+V to paste. About 17 clicks to solve the problem.

Well, hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.

Here are the steps:

  • Sort the data by customer: Select one cell in the Customer column. Click the AZ icon on the Data tab to sort.
  • Data, Subtotals. At each change in: Customer. Add checkmarks to Quantity, Revenue, Cost. Click OK.
  • Click the #2 Group and Outline button (located to the left of column A)
  • Click in D1. Ctrl + Shift + Down Arrow Ctrl + Shift + Right Arrow to select all data
  • Press Alt + ; to select visible cells. Ctrl + C to copy. Ctrl + N for new workbook, Ctrl + V to paste.

After 17 clicks, you have this result:

A summary by customer
A summary by customer

While I almost always use Subtotals to create this report, tomorrow we look at a different method suggested by Adam in Dallas.

This week is Summarizing Data week. Each day, we will look at five different ways to solve a problem.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Never name an important Excel file Book1."

Title Photo: James Baldwin on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.