Pivot Tables
Defination :
A Pivot Table is a data summarization and analysis tool in Excel that allows you to summarize
and analyze large amounts of data from different sources quickly and efficiently.
Problem Statement :
A company named XYZ has the sales dataset containing the name of the salesperson, name of
the product, region where the sales happened, the amount of sales done, date of the sale and
the month of sale. You are required to make use of Pivot Tables to summarise data acording to
the questions provided.
Questions :
Using the given sales dataset, create a pivot table to show the total sales made by each
salesperson in each region.
Using the given sales dataset, create a pivot table to show the total sales made for each
product in each month.
Using the given sales dataset, create a pivot table to show the average sales made by
each salesperson for each product across all three months.
Using the same sales dataset, let’s modify the pivot table to show only the sales made for
the "Phone" product in the "South" region for the month of January.
Using the given sales dataset, modify the pivot table to show the total sales made by each
salesperson for each product, but only for sales greater than $500.
Using the given sales dataset, add a slicer to filter the pivot table by region
Using the same sales dataset, add a slicer to filter the pivot table by month, showing sales
for a specific month or range of months.
Using the sales dataset, create a pivot table that shows the total sales for each region by
dividing it into two groups , one north -south and other east-west.
Salespe
rson Product Region Sales Date Month
John Phone North 500 1/1/2022 January
Sarah Laptop South 1200 1/1/2022 January
Mike Tablet East 800 1/1/2022 January
Emily Laptop West 900 1/1/2022 January
John Tablet North 700 1/1/2022 January
Sarah Phone South 300 1/1/2022 January
Mike Laptop East 1500 1/1/2022 January
Emily Phone West 600 1/1/2022 January
John Laptop North 1000 1/1/2022 January
Sarah Tablet South 400 1/1/2022 January Region2 Region Sum of Sales
Mike Phone East 200 1/1/2022 January Group2 10100
Emily Tablet West 800 1/1/2022 January East 4800
John Laptop North 1200 1/2/2022 February West 5300
Sarah Phone South 500 1/2/2022 February Group1 10700
Mike Tablet East 600 1/2/2022 February North 6200
Emily Laptop West 700 1/2/2022 February South 4500
John Phone North 400 1/2/2022 February Total Result 20800
Sarah Laptop South 1000 1/2/2022 February
Mike Tablet East 1200 1/2/2022 February
Emily Phone West 800 1/2/2022 February This shape represents a This shape repr
John Laptop North 1500 1/2/2022 February slicer. Slicers are supported Slicers are supp
Sarah Tablet South 300 1/2/2022 February in Excel 2010 or later. Excel 2010 or l
Mike Phone East 100 1/2/2022 February
If the shape was modified in If the shape wa
Emily Tablet West 500 1/2/2022 February an earlier version of Excel, or an earlier versio
John Tablet North 900 1/3/2022 March if the workbook was saved in if the workbook
Sarah Laptop South 800 1/3/2022 March Excel 2003 or earlier, the Excel 2003 or e
Mike Phone East 400 1/3/2022 March slicer cannot be used. slicer cannot be
Emily Tablet West 1000 1/3/2022 March
o summarize
rson, name of
f the sale and
ata acording to
ade by each
ade for each
es made by
sales made for
made by each
showing sales
ach region by
Sum of Sales
This shape represents a slicer.
Slicers are supported in
Excel 2010 or later.
If the shape was modified in
an earlier version of Excel, or
if the workbook was saved in
Excel 2003 or earlier, the
slicer cannot be used.
