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

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
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.