Summarize Data with Pivot Tables
April 13, 2018 - by Bill Jelen
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.
This week will feature five different ways to solve the problem.
- Monday: Summarize Data with Subtotals
- Tuesday: Summarize Data with Remove Duplicates
- Wednesday: Summarize Data with Advanced Filter
- Thursday: Summarize Data with Consolidate
- Today: Summarize Data with Pivot Tables
- Saturday: Summary of Summarize Data Week
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.
At that point… 9 clicks... you have your report
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