This practice problems Excel free download helps analysts, accountants, and business professionals improve PivotTable skills, summarize data, and analyze large datasets efficiently.
In this guide, you’ll learn:
- 20 real-world problems (with answers) to strengthen your PivotTable skills.
- What PivotTables are and why this Excel skill is critical.
- How Value Field Settings improve your analysis skills with SUM, AVERAGE, and % of Total.
What Is a PivotTable and Why Practice Problems Excel Free Download Matters
A PivotTable in Excel helps you summarize, analyze, explore, and present large amounts of data without writing complex formulas. By dragging and dropping fields, you can quickly see totals, averages, or percentages.
Example: From a dataset of 1,000 rows of sales data (Region, Product, Sales), a PivotTable can instantly show:
- Total Sales by Region
- Average Sales per Product
- % of Total Sales contributed by each Region
Learning PivotTable skills allows you to move beyond formulas into dynamic, professional reporting.
Value Field Settings in PivotTables: Learn with Practice Problems Excel Free Download
When you place a field in the Values area of a PivotTable, Excel applies a calculation. By default, it uses SUM, but you can change it to:
- SUM → Adds values together
- AVERAGE → Finds the mean
- % of Total → Shows each value as a percentage of the whole
To adjust: Right-click inside your PivotTable → Value Field Settings → choose SUM, AVERAGE, or % of Total.
Mastering this Excel skill is essential for financial reports, sales analysis, and dashboards.
Step-by-Step Guide Using Practice Problems Excel Free Download for PivotTables
- Open the practice Excel file.
- Select the dataset.
- Go to Insert → PivotTable.
- Drag Region into Rows.
- Drag Sales into Values.
- Right-click Values → Value Field Settings → choose SUM, AVERAGE, or % of Total.
This hands-on practice builds real skills instead of just theory.
Example Dataset for Practice Problems Excel Free Download
| Region | Product | Sales | Quantity |
|---|---|---|---|
| East | Phone | 5000 | 30 |
| East | Laptop | 7000 | 20 |
| West | Phone | 6000 | 25 |
| West | Laptop | 8000 | 18 |
| North | Tablet | 4000 | 15 |
| North | Phone | 5500 | 22 |
| South | Laptop | 9000 | 27 |
| South | Tablet | 3000 | 12 |
| East | Tablet | 3500 | 14 |
| West | Phone | 6500 | 28 |
20 PivotTable Exercises Included in Practice Problems Excel Free Download
Enhance your PivotTable skills with these 20 real-world Excel practice problems. Each problem uses Value Field Settings like SUM, AVERAGE, and % of Total to help you master data analysis.
1. Total Sales by Region (SUM)
- Rows: Region
- Values: Sales → SUM
- Answer:
- East = 15,500
- West = 20,500
- North = 9,500
- South = 12,000
2. Average Sales by Product (AVERAGE)
- Rows: Product
- Values: Sales → AVERAGE
- Answer:
- Phone = 5,666.7
- Laptop = 8,000
- Tablet = 3,500
3. % of Total Sales by Region
- Values: Sales → Show Values As → % of Grand Total
- Answer:
- East = 26.8%
- West = 35.4%
- North = 16.4%
- South = 20.7%
4. Compare Sales and Quantity by Region (SUM)
- Rows: Region
- Values: Sales (SUM), Quantity (SUM)
- Answer: Shows both total sales and total quantity per region.
5. Highest Average Sales per Region (AVERAGE)
- Values: Sales → AVERAGE
- Answer: West = 10,250
6. % of Total Quantity by Product
- Values: Quantity → % of Grand Total
- Answer:
- Phone = 47%
- Laptop = 29%
- Tablet = 24%
7. Region in Rows, Product in Columns, Sales in Values (SUM)
- Rows: Region
- Columns: Product
- Values: Sales → SUM
- Answer: Displays sales breakdown by region and product.
8. Average Quantity by Region
- Values: Quantity → AVERAGE
- Answer:
- East = 21.3
- West = 23.7
- North = 18.5
- South = 19.5
9. % of Grand Total Sales by Product
- Values: Sales → Show Values As → % of Grand Total
- Answer:
- Phone = 31%
- Laptop = 41%
- Tablet = 28%
10. Total Sales for East + West Only
- Filter: Region → Select East & West
- Values: Sales → SUM
- Answer: East + West = 36,000
11. Compare Average Sales for Phones vs. Laptops
- Rows: Product
- Values: Sales → AVERAGE
- Answer:
- Phone = 5,666.7
- Laptop = 8,000
12. % of Total Sales by Product Inside Each Region
- Columns: Product → Show Values As → % of Row Total
- Answer: Displays each product’s contribution within its region.
13. SUM of Sales and AVERAGE of Sales
- Values: Add Sales twice → one as SUM, one as AVERAGE
- Answer: Displays both total and average sales for each row/column.
14. % of Total Quantity Contributed by Laptops
- Values: Quantity → % of Grand Total
- Answer: 29%
15. Lowest Average Sales Region (AVERAGE)
- Values: Sales → AVERAGE
- Answer: North = 4,750
16. Sales by Region as % of Column Total
- Columns: Product → Show Values As → % of Column Total
- Answer: Displays each region’s contribution per product column.
17. Compare Sales vs. Quantity by Product (SUM)
- Rows: Product
- Values: Sales (SUM), Quantity (SUM)
- Answer: Displays both totals for each product.
18. Total Sales for South Region Only
- Filter: Region → South
- Values: Sales → SUM
- Answer: South = 12,000
19. % of Total Sales Across All Regions
- Rows: Region
- Values: Sales → Show Values As → % of Grand Total
- Answer: Same as Problem 3.
20. SUM of Sales and % of Total Sales by Region
- Values: Sales (SUM), Sales (% of Grand Total)
- Answer: Displays both total and percentage for each region.
Download the Practice Problems Excel Free Download File
Why Practice Problems Excel Free Download Boosts Excel Skills and Career Growth
✔ Saves hours of manual work
✔ Strong Excel skill for career growth
✔ Handles large datasets quickly
✔ Perfect for dashboards, finance, and sales reports
By completing these exercises, you can fully master PivotTable skills in Excel. Get your practice problems Excel free download now to start practicing.
