Pivot Table Assignment
Sales Analysis Using Excel Pivot Tables
Dataset
Copy the table below into Excel (or use the provided sample sheet) and convert it to a proper Table before building
your Pivot Table. Note: two rows contain intentional data issues — you will address them in Task 8.
Date Region Salesperson Product Category Units Sold Unit Price Total Sale
01-Jan-2026 North Ayesha Khan Wireless Mouse Electronics 12 1200 14400
02-Jan-2026 South Bilal Ahmed Office Chair Furniture 4 8500 34000
03-Jan-2026 North Ayesha Khan Notebook Pack Stationery 30 150 4500
05-Jan-2026 East Sara Malik LED Monitor Electronics 6 15000 90000
07-Jan-2026 West Usman Tariq Desk Lamp Furniture 10 2200 22000
09-Jan-2026 South Bilal Ahmed Wireless Mouse Electronics 15 1200 18000
11-Jan-2026 North Ayesha Khan Office Chair Furniture 3 8500 25500
14-Jan-2026 East Sara Malik Stapler Stationery 20 350 7000
16-Jan-2026 West Usman Tariq LED Monitor Electronics 5 15000 75000
18-Jan-2026 South Bilal Ahmed Notebook Pack Stationery 40 150 6000
02-Feb-2026 North Ayesha Khan Desk Lamp Furniture 8 2200 17600
04-Feb-2026 East Sara Malik Wireless Mouse Electronics 18 1200 21600
06-Feb-2026 West Usman Tariq Office Chair Furniture 6 8500 51000
08-Feb-2026 South Bilal Ahmed LED Monitor Electronics 7 15000 105000
10-Feb-2026 North Ayesha Khan Stapler Stationery 25 350 8750
12-Feb-2026 East Sara Malik Desk Lamp Furniture 9 2200 19800
15-Feb-2026 West Usman Tariq Notebook Pack Stationery 35 150 5250
17-Feb-2026 South Bilal Ahmed Stapler Stationery 22 350 7700
20-Feb-2026 North Ayesha Khan LED Monitor Electronics 4 15000 60000
22-Feb-2026 East Sara Malik Office Chair Furniture 5 8500 42500
03-Mar-2026 West Usman Tariq Wireless Mouse Electronics 20 1200 24000
05-Mar-2026 South Bilal Ahmed Desk Lamp Furniture 11 2200 24200
07-Mar-2026 North Ayesha Khan Notebook Pack Stationery 28 150 4200
09-Mar-2026 East Sara Malik LED Monitor Electronics
11-Mar-2026 West Usman Tariq Stapler Stationery 18 350 6300
13-Mar-2026 South Bilal Ahmed Office Chair Furniture 4 8500 34000
15-Mar-2026 North Ayesha Khan Wireless Mouse Electronics 16 1200 19200
18-Mar-2026 East Sara Malik Notebook Pack Stationery 33 150 4950
20-Mar-2026 West Usman Tariq Desk Lamp Furniture 7 2200 15400
22-Mar-2026 South Bilal Ahmed LED Monitor Electronics 6 N/A N/A
Tasks to Perform
1. Create the Pivot Table
a) Select the full data range (including headers) and insert a Pivot Table on a new worksheet.
2. Total Sales by Region
b) Rows: Region Values: Sum of Total Sale.
c) Identify which region generated the highest total sales.
3. Break Down by Category within Region
d) Add Category as a second-level Row field (nested under Region).
e) Note which category performs best in each region.
4. Filter by Salesperson
f) Add Salesperson to the Filter area.
g) Use the filter to view totals for one salesperson at a time and record their best-selling category.
5. Average Unit Price by Category
h) Add Category to Rows and set Values to Average of Unit Price.
i) Format the average to 2 decimal places.
6. Show Sales as % of Grand Total
j) In the Total Sale value field, open Value Field Settings → Show Values As → % of Grand Total.
k) Identify which Region contributes the largest share of overall sales.
7. Submit
l) Submit the completed Excel workbook containing the cleaned data, Pivot Table(s), and Pivot Chart.
Tip: Refresh the Pivot Table after every change to the source data (Right-click inside the Pivot Table → Refresh, or Alt+F5).