Summarize Data with Subtotals
April 09, 2018 - by Bill Jelen
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.
This week will feature five different ways to solve the problem.
- Today: Summarize Data with Subtotals
- Tuesday: Summarize Data with Remove Duplicates
- Wednesday: Summarize Data with Advanced Filter
- Thursday: Summarize Data with Consolidate
- Friday: Summarize Data with Pivot Tables
- Saturday: Summary of Summarize Data Week
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:
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