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

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

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