Assignments Of Class 8
Pivot
Tables in MS Excel
Assignment 1: Create a
Pivot Table for Sales Data
Data Provided:
|
Region |
Product |
Salesperson |
Sales ($) |
Month |
|
North |
Laptops |
John |
5000 |
January |
|
South |
Mobiles |
Sarah |
3000 |
February |
|
East |
Tablets |
Alex |
4000 |
March |
|
North |
Laptops |
John |
6000 |
April |
|
South |
Mobiles |
Sarah |
3500 |
May |
|
East |
Tablets |
Alex |
4500 |
June |
Tasks:
1.
Create a Pivot Table to show the total
sales for each region.
2.
Filter the Pivot Table to show only
"North" region sales.
Solution:
Steps:
1.
Select the data range (A1:E7).
2.
Go to the Insert tab and click on PivotTable.
3.
Place the Pivot Table in a new worksheet.
4.
In the Pivot Table Fields pane:
o Drag
Region to Rows.
o Drag
Sales ($) to Values.
5.
Total sales for each region will appear in
the Pivot Table.
6.
To filter for "North," use the
filter dropdown in the Pivot Table and select "North."
Output Pivot Table:
|
Region |
Sum of Sales ($) |
|
North |
11000 |
|
South |
6500 |
|
East |
8500 |
Filtered Output (North
Only):
|
Region |
Sum of Sales ($) |
|
North |
11000 |
Explanation: The
Pivot Table summarizes the sales data by region. Using the filter option, you
can focus on specific regions like "North."
Assignment 2: Analyze
Monthly Sales
Data Provided:
|
Region |
Product |
Salesperson |
Sales ($) |
Month |
|
North |
Laptops |
John |
5000 |
January |
|
South |
Mobiles |
Sarah |
3000 |
February |
|
East |
Tablets |
Alex |
4000 |
March |
|
North |
Laptops |
John |
6000 |
April |
|
South |
Mobiles |
Sarah |
3500 |
May |
|
East |
Tablets |
Alex |
4500 |
June |
Tasks:
1.
Create a Pivot Table to show total sales
for each month.
2.
Add a slicer to filter by product.
Solution:
Steps:
1.
Select the data range (A1:E7).
2.
Go to the Insert tab and click on PivotTable.
3.
Place the Pivot Table in a new worksheet.
4.
In the Pivot Table Fields pane:
o Drag
Month to Rows.
o Drag
Sales ($) to Values.
5.
To add a slicer:
o Select
the Pivot Table.
o Go
to the Insert tab and click Slicer.
o Choose
Product for the slicer.
6.
Use the slicer to filter data by product.
Output Pivot Table
(Without Slicer):
|
Month |
Sum of Sales ($) |
|
January |
5000 |
|
February |
3000 |
|
March |
4000 |
|
April |
6000 |
|
May |
3500 |
|
June |
4500 |
Filtered Output (With
Slicer for Product = "Laptops"):
|
Month |
Sum of Sales ($) |
|
January |
5000 |
|
April |
6000 |
Explanation: The
Pivot Table provides a summary of sales data by month. The slicer allows for
quick filtering based on the product.
Assignment 3: Identify
Top Salesperson
Data Provided:
|
Region |
Product |
Salesperson |
Sales ($) |
Month |
|
North |
Laptops |
John |
5000 |
January |
|
South |
Mobiles |
Sarah |
3000 |
February |
|
East |
Tablets |
Alex |
4000 |
March |
|
North |
Laptops |
John |
6000 |
April |
|
South |
Mobiles |
Sarah |
3500 |
May |
|
East |
Tablets |
Alex |
4500 |
June |
Tasks:
1.
Create a Pivot Table to show total sales
by salesperson.
2.
Identify the top salesperson based on
total sales.
Solution:
Steps:
1.
Select the data range (A1:E7).
2.
Go to the Insert tab and click on PivotTable.
3.
Place the Pivot Table in a new worksheet.
4.
In the Pivot Table Fields pane:
o Drag
Salesperson to Rows.
o Drag
Sales ($) to Values.
5.
Sort the sales column in descending order
to identify the top salesperson.
Output Pivot Table:
|
Salesperson |
Sum of Sales ($) |
|
John |
11000 |
|
Alex |
8500 |
|
Sarah |
6500 |
Top Salesperson: John
Explanation: The
Pivot Table aggregates sales data for each salesperson, and sorting the sales
column in descending order helps identify the top performer.
Assignment 4: Analyze Product-Wise Sales
Data Provided:
|
Region |
Product |
Salesperson |
Sales ($) |
Month |
|
North |
Laptops |
John |
5000 |
January |
|
South |
Mobiles |
Sarah |
3000 |
February |
|
East |
Tablets |
Alex |
4000 |
March |
|
North |
Laptops |
John |
6000 |
April |
|
South |
Mobiles |
Sarah |
3500 |
May |
|
East |
Tablets |
Alex |
4500 |
June |
Tasks:
1.
Create a Pivot Table to show total sales
for each product.
2.
Display the data in descending order of
sales.
Solution:
Steps:
1.
Select the data range (A1:E7).
2.
Go to the Insert tab and click PivotTable.
3.
Place the Pivot Table in a new worksheet.
4.
Drag Product to Rows and Sales
($) to Values.
5.
Sort the sales column in descending order.
Output:
|
Product |
Sum of Sales ($) |
|
Laptops |
11000 |
|
Tablets |
8500 |
|
Mobiles |
6500 |
Explanation:
The Pivot Table aggregates the total sales for each product, and sorting makes
it easier to compare performance.
Assignment 5: Calculate
Sales Contribution by Region
Data Provided:
Same as Assignment 4.
Tasks:
1.
Create a Pivot Table to show the total
sales for each region.
2.
Add a calculated field to display the
percentage contribution of each region to total sales.
Solution:
Steps:
1.
Create a Pivot Table with Region in
Rows and Sales ($) in Values.
2.
Right-click on the Pivot Table and select Show
Values As → % of Grand Total.
Output:
|
Region |
% of Grand Total |
|
North |
42% |
|
South |
25% |
|
East |
33% |
Explanation:
Using the Show Values As feature, you can easily calculate percentages
relative to the total sales.
Assignment 6: Count
Transactions by Salesperson
Data Provided:
Same as Assignment 4.
Tasks:
1.
Create a Pivot Table to count the number
of transactions for each salesperson.
Solution:
Steps:
1.
Create a Pivot Table with Salesperson
in Rows and Sales ($) in Values.
2.
Change the value field setting to Count
instead of Sum.
Output:
|
Salesperson |
Count of Transactions |
|
John |
2 |
|
Sarah |
2 |
|
Alex |
2 |
Explanation:
The Count option in Pivot Tables helps in determining the frequency of
transactions per salesperson.
Assignment 7: Filter Data
for Specific Months
Data Provided:
Same as Assignment 4.
Tasks:
1.
Create a Pivot Table to show sales for the
months of January, March, and June only.
Solution:
Steps:
1.
Create a Pivot Table with Month in
Rows and Sales ($) in Values.
2.
Use the filter dropdown to select only January,
March, and June.
Output:
|
Month |
Sum of Sales ($) |
|
January |
5000 |
|
March |
4000 |
|
June |
4500 |
Explanation:
Filters in Pivot Tables allow for displaying only specific data as per
requirements.
Assignment 8: Group Data
by Quarter
Data Provided:
Same as Assignment 4.
Tasks:
1.
Group the data by quarter and calculate
total sales for each quarter.
Solution:
Steps:
1.
Create a Pivot Table with Month in
Rows and Sales ($) in Values.
2.
Right-click on the Month column in
the Pivot Table and select Group → By Quarter.
Output:
|
Quarter |
Sum of Sales ($) |
|
Q1 |
12000 |
|
Q2 |
14000 |
Explanation:
Grouping by quarters consolidates monthly data into broader time periods for
easier analysis.
Assignment 9: Display
Sales Trends Using a Pivot Chart
Data Provided:
Same as Assignment 4.
Tasks:
1.
Create a Pivot Table for monthly sales.
2.
Insert a Pivot Chart to visualize the
sales trend.
Solution:
Steps:
1.
Create a Pivot Table with Month in
Rows and Sales ($) in Values.
2.
Go to the Insert tab and choose a Line
Chart under the Pivot Chart options.
Output Chart:
- A
line chart showing a visual representation of sales trends over months.
Explanation:
Pivot Charts provide an interactive way to visualize Pivot Table data, making
trends easier to spot.
Assignment 10: Create a
Region-Product Sales Matrix
Data Provided:
Same as Assignment 4.
Tasks:
1.
Create a Pivot Table to display total
sales for each product in each region.
Solution:
Steps:
1.
Create a Pivot Table with Region in
Rows, Product in Columns, and Sales ($) in Values.
Output:
|
Region |
Laptops |
Mobiles |
Tablets |
|
North |
11000 |
||
|
South |
6500 |
||
|
East |
8500 |
Explanation:
Pivot Tables allow you to create cross-tabulations for detailed comparisons.
Assignment 11: Use
Conditional Formatting in a Pivot Table
Data Provided:
Same as Assignment 4.
Tasks:
1.
Highlight the highest sales value in a
Pivot Table using conditional formatting.
Solution:
Steps:
1.
Create a Pivot Table for Region and
Sales ($).
2.
Select the sales column, go to Home →
Conditional Formatting, and choose Top/Bottom Rules → Top 1.
Output:
- The
highest sales value is highlighted in the Pivot Table.
Explanation:
Conditional formatting in Pivot Tables emphasizes specific data points like the
highest sales value.
Assignment 12: Add
Multiple Filters to a Pivot Table
Data Provided:
Same as Assignment 4.
Tasks:
1.
Filter the Pivot Table to display sales
data for the North region and the Laptops product only.
Solution:
Steps:
1.
Create a Pivot Table with Region in
Rows and Sales ($) in Values.
2.
Add Product to the Filters area.
3.
Select North in the Region filter
and Laptops in the Product filter.
Output:
|
Region |
Product |
Sum of Sales ($) |
|
North |
Laptops |
11000 |
Explanation:
Adding multiple filters refines the data shown in the Pivot Table based on
specific conditions.
SOME MORE ASSIGNMENTS
Assignment 1: Create a PivotTable to Analyze Sales Data
Objective:
Create a PivotTable to
summarize sales data by product and date.
Dataset:
|
Date |
Product |
Sales Amount |
|
2024-01-01 |
A |
200 |
|
2024-01-01 |
B |
150 |
|
2024-01-02 |
A |
300 |
|
2024-01-02 |
B |
200 |
|
2024-01-03 |
A |
250 |
|
2024-01-03 |
B |
100 |
|
2024-01-04 |
A |
350 |
|
2024-01-04 |
B |
400 |
Steps:
1.
Select the Data:
o Highlight
the entire dataset (including headers).
2.
Insert a PivotTable:
o Click
on the Insert tab and select PivotTable.
o Choose New
Worksheet and click OK.
3.
Add Fields to the PivotTable:
o Drag Product to
the Rows area.
o Drag Date to
the Columns area.
o Drag Sales
Amount to the Values area.
4.
Analyze the PivotTable:
o Review
the summarized data by product and date.
Solution:
The PivotTable should
display total sales for each product over the specified dates, allowing you to
quickly see which products performed best on each date.
Assignment 2: Customize a
PivotTable
Objective:
Customize the PivotTable
created in Assignment 1 by applying filters and sorting.
Steps:
1.
Open the PivotTable from Assignment
1.
2.
Apply Filters:
o Click
the dropdown arrow next to the Product label in the
PivotTable.
o Select
only Product A to filter the data.
3.
Sort Data:
o Right-click
on any sales amount in the Values column.
o Select Sort > Sort
Largest to Smallest to arrange the sales data.
Solution:
After applying filters
and sorting, the PivotTable will display only the sales data for Product A,
sorted by the highest sales amount.
Assignment 3: Create a PivotChart from the PivotTable
Objective:
Create a PivotChart to
visualize the sales data from the PivotTable.
Steps:
1.
Select the PivotTable created in
Assignment 1.
2.
Insert a PivotChart:
o Go
to the Insert tab and select PivotChart.
o Choose
a Column Chart and click OK.
3.
Customize the PivotChart:
o Click
on the chart title and change it to "Sales Data Overview".
o Add
data labels to the chart for clarity.
Solution:
The PivotChart will
display total sales by product for each date, allowing for easy visualization
of sales trends. Data labels will enhance the readability of the chart.
Assignment 4: Analyze Sales Trends Over Time
Objective:
Use a PivotTable to
analyze sales trends over time.
Dataset:
|
Date |
Product |
Sales Amount |
|
2024-01-01 |
A |
200 |
|
2024-01-01 |
B |
150 |
|
2024-01-02 |
A |
300 |
|
2024-01-02 |
B |
200 |
|
2024-01-03 |
A |
250 |
|
2024-01-03 |
B |
100 |
|
2024-01-04 |
A |
350 |
|
2024-01-04 |
B |
400 |
|
2024-01-05 |
A |
500 |
|
2024-01-05 |
B |
450 |
Steps:
1.
Create a PivotTable:
o Highlight
the dataset and insert a PivotTable.
o Place Date in
the Rows area.
o Place Sales
Amount in the Values area.
2.
Group Dates by Month:
o Right-click
on any date in the PivotTable.
o Select Group and
choose to group by Months.
3.
Create a PivotChart:
o Insert
a PivotChart from the PivotTable and select a Line Chart.
Solution:
The PivotTable will show
total sales per month, while the PivotChart will provide a visual
representation of sales trends over the analyzed period, allowing for easy
comparison of sales performance.
Assignment 5: Advanced Customization of a PivotTable
Objective:
Perform advanced
customization techniques on a PivotTable to enhance data analysis.
Steps:
1.
Create a PivotTable from any dataset
you prefer (similar to previous assignments).
2.
Add a Filter for Product Type:
o Include
a new field for Product Type (e.g., Electronics, Clothing) in your dataset.
o Add
this field to the Filters area of the PivotTable.
3.
Add Slicers for Interactive
Filtering:
o With
the PivotTable selected, go to the PivotTable Analyze tab.
o Click
on Insert Slicer and choose Product.
o Arrange
the slicer on your worksheet for easy access.
4.
Analyze the Data:
o Use
the slicer to filter data interactively and observe changes in the PivotTable.
Solution:
Assignment 1: Create a PivotTable to Analyze Sales Data
Objective:
Create a PivotTable to
summarize sales data by product and date.
Dataset:
|
Date |
Product |
Sales Amount |
|
2024-01-01 |
A |
200 |
|
2024-01-01 |
B |
150 |
|
2024-01-02 |
A |
300 |
|
2024-01-02 |
B |
200 |
|
2024-01-03 |
A |
250 |
|
2024-01-03 |
B |
100 |
|
2024-01-04 |
A |
350 |
|
2024-01-04 |
B |
400 |
Steps:
1.
Select the Data:
o Highlight
the entire dataset (including headers).
2.
Insert a PivotTable:
o Click
on the Insert tab in the Ribbon.
o Click
on PivotTable.
o In
the dialog box, select New Worksheet and click OK.
3.
Add Fields to the PivotTable:
o In
the PivotTable Fields pane, drag Product to
the Rows area.
o Drag Date to
the Columns area.
o Drag Sales
Amount to the Values area.
4.
Analyze the PivotTable:
o Review
the summarized data, which will show total sales for each product over the
specified dates.
Solution:
The resulting PivotTable
will look like this:
|
Product |
2024-01-01 |
2024-01-02 |
2024-01-03 |
2024-01-04 |
Total |
|
A |
200 |
300 |
250 |
350 |
1100 |
|
B |
150 |
200 |
100 |
400 |
850 |
|
Total |
350 |
500 |
350 |
750 |
1950 |
Assignment 2: Customize a PivotTable
Objective:
Customize the PivotTable
created in Assignment 1 by applying filters and sorting.
Steps:
1.
Open the PivotTable from Assignment
1.
o Make
sure the PivotTable created earlier is selected.
2.
Apply Filters:
o Click
the dropdown arrow next to the Product label in the
PivotTable.
o Uncheck (Select
All) and select only Product A. Click OK.
3.
Sort Data:
o Right-click
on any sales amount in the Values column.
o Select Sort > Sort
Largest to Smallest.
Solution:
After applying the filter
and sorting, the PivotTable will show only the sales data for Product A, sorted
by the highest sales amount:
|
Product |
2024-01-01 |
2024-01-02 |
2024-01-03 |
2024-01-04 |
Total |
|
A |
200 |
300 |
250 |
350 |
1100 |
|
Total |
200 |
300 |
250 |
350 |
1100 |
Assignment 3: Create a PivotChart from the PivotTable
Objective:
Create a PivotChart to
visualize the sales data from the PivotTable.
Steps:
1.
Select the PivotTable created in
Assignment 1.
2.
Insert a PivotChart:
o Go
to the Insert tab.
o Click
on PivotChart.
o Choose
a Column Chart from the options and click OK.
3.
Customize the PivotChart:
o Click
on the chart title and change it to "Sales Data Overview".
o Right-click
on any bar in the chart and select Add Data Labels.
Solution:
The resulting PivotChart
will visually display the total sales by product for each date, with the title
"Sales Data Overview" and data labels indicating sales amounts for
better clarity.
Assignment 4: Analyze Sales Trends Over Time
Objective:
Use a PivotTable to
analyze sales trends over time.
Dataset:
|
Date |
Product |
Sales Amount |
|
2024-01-01 |
A |
200 |
|
2024-01-01 |
B |
150 |
|
2024-01-02 |
A |
300 |
|
2024-01-02 |
B |
200 |
|
2024-01-03 |
A |
250 |
|
2024-01-03 |
B |
100 |
|
2024-01-04 |
A |
350 |
|
2024-01-04 |
B |
400 |
|
2024-01-05 |
A |
500 |
|
2024-01-05 |
B |
450 |
Steps:
1.
Create a PivotTable:
o Highlight
the dataset and insert a PivotTable as in previous assignments.
2.
Add Fields to the PivotTable:
o Drag Date to
the Rows area.
o Drag Sales
Amount to the Values area.
3.
Group Dates by Month:
o Right-click
on any date in the PivotTable.
o Select Group and
choose to group by Months. Click OK.
4.
Create a PivotChart:
o Select
the PivotTable and insert a PivotChart as a Line Chart.
Solution:
The PivotTable will show
total sales per month, and the PivotChart will provide a visual representation
of sales trends over the analyzed period, allowing for easy comparison of sales
performance.
PivotTable:
|
Month |
Total Sales |
|
Jan 2024 |
1950 |
PivotChart: A line chart
will display the trend of sales over the month.
Assignment 5: Advanced Customization of a PivotTable
Objective:
Perform advanced
customization techniques on a PivotTable to enhance data analysis.
Steps:
1.
Create a PivotTable from any dataset
you prefer (similar to previous assignments).
2.
Add a Filter for Product Type:
o Include
a new field for Product Type (e.g., Electronics, Clothing) in your dataset.
o Drag
this field into the Filters area of the PivotTable.
3.
Add Slicers for Interactive
Filtering:
o With
the PivotTable selected, go to the PivotTable Analyze tab.
o Click
on Insert Slicer and choose Product. Click OK.
o Position
the slicer on your worksheet for easy access.
4.
Analyze the Data:
o Use
the slicer to filter data interactively (for example, select "A" from
the slicer) and observe changes in the PivotTable.
Solution:
This assignment
demonstrates advanced customization by allowing users to filter data
dynamically using slicers. The PivotTable will change based on the selection
made in the slicer, showing relevant sales data only for the selected product.
