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