ASSIGNMENT – 28

Create Pivot Table Using Data

Topic: Sales Data Analysis Using Pivot Table

Sales Data

Enter the following data in Excel:

DATE OF SALE MONTH SALES AMOUNT
05-01-2026 January ₹25,000
12-01-2026 January ₹32,500
18-01-2026 January ₹18,750
25-01-2026 January ₹41,200
03-02-2026 February ₹22,500
09-02-2026 February ₹35,800
15-02-2026 February ₹28,400
21-02-2026 February ₹46,500
27-02-2026 February ₹31,750
04-03-2026 March ₹52,000
10-03-2026 March ₹38,500
16-03-2026 March ₹44,250
22-03-2026 March ₹29,800
28-03-2026 March ₹61,500
05-04-2026 April ₹35,000
12-04-2026 April ₹48,500
18-04-2026 April ₹42,750
25-04-2026 April ₹55,200

Instructions for Creating a Pivot Table

  1. Select the complete data.

  2. Go to Insert → PivotTable.

  3. Select New Worksheet.

  4. Drag the required field into Rows.

  5. Drag Sales Amount into Values.

  6. Change Value Field Settings whenever required:

    • Sum

    • Average

    • Count

  7. Use Sort → Largest to Smallest wherever required.

PART A — Basic Pivot Table

Q.1 — Month-wise Total Sales

Create a Pivot Table showing:

  • Month → Rows

  • Sales Amount → Values

  • Values should show Sum of Sales Amount

Q.2 — Month-wise Average Sales

Create a Pivot Table showing:

  • Month → Rows

  • Sales Amount → Values

  • Values should show Average of Sales Amount

Q.3 — Month-wise Number of Sales Transactions

Create a Pivot Table showing:

  • Month → Rows

  • Sales Amount → Values

  • Values should show Count of Sales Amount

PART B — Date-wise Analysis

Q.4 — Date-wise Total Sales

Create a Pivot Table showing:

  • Date of Sale → Rows

  • Sales Amount → Values

  • Values should show Sum of Sales Amount

Q.5 — Highest Sales Amount

Using a Pivot Table, identify the highest individual Sales Amount.

Q.6 — Lowest Sales Amount

Using a Pivot Table, identify the lowest individual Sales Amount.

PART C — Month & Sales Analysis

Q.7 — January and February Sales

Using a Pivot Table, find:

  1. Total Sales of January

  2. Total Sales of February

Q.8 — March and April Sales

Using a Pivot Table, find:

  1. Total Sales of March

  2. Total Sales of April

PART D — Advanced Pivot Table

Q.9 — Sort Month-wise Sales

Create a Pivot Table with:

  • Month → Rows

  • Sales Amount → Values

  • Values → Sum of Sales Amount

Then sort the Pivot Table from:

Highest Sales → Lowest Sales

Q.10 — Complete Month-wise Sales Analysis

Create a Pivot Table with:

Rows:

Month

Values:

Sales Amount

Add the Sales Amount field three times and set it to:

  1. Sum

  2. Average

  3. Count

Your Pivot Table should show:

Month Sum of Sales Average of Sales Count of Sales
January      
February      
March      
April      
Grand Total      

Then identify:

1. Highest Sales Month

2. Lowest Sales Month

3. Highest Average Sales Month

4. Total Number of Transactions

Skills Covered

By completing this assignment, students will practice:

  • Creating a Pivot Table

  • Adding fields to Rows

  • Adding fields to Values

  • Sum of Sales

  • Average of Sales

  • Count of Transactions

  • Date-wise analysis

  • Month-wise analysis

  • Sorting Pivot Table data

  • Finding highest and lowest values

  • Using one field multiple times in Values

  • Reading and interpreting Pivot Table results

PreviousNext