ASSIGNMENT – 12

Create Pivot Table Using Data

Sales Data

Enter the following data in Excel and use it to create different Pivot Tables.

LAST NAME SALES COUNTRY QUARTER
Sharma ₹12,500 India Qtr 1
Verma ₹18,750 USA Qtr 2
Singh ₹9,850 India Qtr 3
Gupta ₹15,600 UK Qtr 4
Mehta ₹7,450 USA Qtr 1
Kapoor ₹21,300 India Qtr 2
Sharma ₹11,200 UK Qtr 3
Verma ₹16,850 India Qtr 4
Singh ₹13,400 USA Qtr 1
Gupta ₹8,950 UK Qtr 2
Mehta ₹19,600 India Qtr 3
Kapoor ₹14,250 USA Qtr 4
Sharma ₹22,500 India Qtr 1
Gupta ₹10,750 USA Qtr 2
Verma ₹17,900 UK Qtr 3
Singh ₹12,650 India Qtr 4
Mehta ₹9,300 UK Qtr 1
Kapoor ₹20,450 USA Qtr 2

Questions

Q.1

Create a Pivot Table showing the Total Sales by Country.

Pivot Table Setup:

  • Rows: Country

  • Values: Sales → Sum

Q.2

Create a Pivot Table showing the Total Sales by Quarter.

Pivot Table Setup:

  • Rows: Quarter

  • Values: Sales → Sum

Q.3

Create a Pivot Table showing the Total Sales by Last Name.

Pivot Table Setup:

  • Rows: Last Name

  • Values: Sales → Sum

Q.4

Find the Total Sales of India using a Pivot Table.

Pivot Table Setup:

  • Rows: Country

  • Values: Sales → Sum

Q.5

Find the Total Sales of USA using a Pivot Table.

Pivot Table Setup:

  • Rows: Country

  • Values: Sales → Sum

Q.6

Create a Pivot Table showing Country in Rows and Quarter in Columns.

Pivot Table Setup:

  • Rows: Country

  • Columns: Quarter

  • Values: Sales → Sum

Q.7

Create a Pivot Table showing Last Name in Rows and Country in Columns.

Pivot Table Setup:

  • Rows: Last Name

  • Columns: Country

  • Values: Sales → Sum

Q.8

Find which Country has the highest Total Sales using a Pivot Table.

Pivot Table Setup:

  • Rows: Country

  • Values: Sales → Sum

  • Sort the Total Sales from Largest to Smallest.

Q.9

Find which Quarter has the highest Total Sales using a Pivot Table.

Pivot Table Setup:

  • Rows: Quarter

  • Values: Sales → Sum

  • Sort the Total Sales from Largest to Smallest.

Q.10

Create a Pivot Table showing Last Name-wise Total Sales for each Quarter.

Pivot Table Setup:

  • Rows: Last Name

  • Columns: Quarter

  • Values: Sales → Sum

Pivot Table Skills to Practise

Pivot Table Feature Practice
Rows Country, Quarter, Last Name
Columns Quarter, Country
Values Sum of Sales
Sorting Highest to Lowest Sales
Filtering Country / Quarter
Cross-tabulation Country × Quarter
Cross-tabulation Last Name × Quarter

Basic Pivot Table Process

Step 1: Select the complete data table.

Step 2: Go to:

Insert → PivotTable

Step 3: Select New Worksheet.

Step 4: Drag the required fields into:

  • Rows

  • Columns

  • Values

Step 5: Make sure the Sales field is summarized as Sum, not Count.

PreviousNext