MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summarize Data with Pivot Tables

April 13, 2018 - by Bill Jelen

Summarize Data with Pivot Tables

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at using a pivot table to summarize the data.

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 2191: Summarize with a Pivot Table.

This is wrapping up our Summarized Data Week-- five different methods to create a summary report of one line per Customer. And Pivot Tables-- why didn't I do this first? Well, that's a great question.

So here we have our original data set. Choose one cell on the data, Insert, Pivot Table, just accept all of these defaults, click OK. We got a brand new worksheet to the left of the original worksheet. This is where the report's going to be built, and it's going to be… choose Customer, sends it to the rows area. Quantity, Revenue, Profit, and Cost. On a bigger screen I would have had to scroll. I think nine clicks, and there is our result. Definitely the fastest, easiest, coolest way to go out of the whole week.

Now, all five methods that we talked about this weak are in this book, MrExcel 54-- LIVe, Live. MrEcel LIVe, The 54 Greatest Excel Tips of All Time. Click that "I" on the top right-hand corner.

All week we take a look at how to create this report five different ways: Subtotals, Remove Duplicates, Advanced Filter, Consolidate, and then today, Pivot Tables. It's easy; it's nine clicks. Select one cell in your data, Insert, Pivot Table, OK, and then in the Pivot Table field list just add a check mark to Customer, Quantity, Revenue, Profit, and Cost; and that's it, nine clicks you're done. Tomorrow, we're going to wrap up with these-- a summary of these five methods and a chance for you to vote on your favorite.

Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.

Here are the steps:

  • Choose any cell in your data. Click on the Insert tab of the ribbon. Choose Pivot Table. Accept all of the defaults in the Pivot Table dialog. Click OK.
  • In the Pivot Table Field, click five fields: Customer, Quantity, Revenue, Profit, Cost.

    Choose fields in the Fields list.
    Choose fields in the Fields list.
  • At that point… 9 clicks... you have your report

    A pivot table with the results
    A pivot table with the results

Out of the five methods discussed this week, this is likely the fastest and easiest. Come back tomorrow for a recap of the five methods and a chance to vote.

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:

"Just Pivot It"

Title Photo: Gabriel Izgi

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.