Creating a PivotTable in Excel - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a PivotTable from the data in Sheet1.

Excel

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets("Sheet1").Range([1])).CreatePivotTable TableDestination:=Sheets("Sheet2").Range("A3"), TableName:="SalesPivot"

Drag options to blanks, or click blank then click option'

A"B2:E50"

B"A1:D100"

C"A1:B10"

D"A2:C20"

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting a range that is too small and misses data.

Using incorrect sheet names.

The range "A1:D100" covers the full data table for the PivotTable source.

2fill in blank

medium

Complete the code to add a row field named "Region" to the PivotTable.

Excel

With Sheets("Sheet2").PivotTables("SalesPivot")
  .PivotFields([1]).Orientation = xlRowField
End With

Drag options to blanks, or click blank then click option'

A"Sales"

B"Product"

C"Region"

D"Date"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a numeric field like "Sales" as a row field.

Using a field not present in the data.

"Region" is the field to be added as a row in the PivotTable.

3fill in blank

hard

Fix the error in the code to add a data field summing the "Sales" column.

Excel

With Sheets("Sheet2").PivotTables("SalesPivot")
  .AddDataField .PivotFields([1]), "Total Sales", xlSum
End With

Drag options to blanks, or click blank then click option'

A"Sales"

B"Total"

C"Sum"

D"Amount"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a field name that does not exist in the data.

Using a non-numeric field for summing.

"Sales" is the correct field name to sum in the data field.

4fill in blank

hard

Fill both blanks to filter the PivotTable to show only "East" region data.

Excel

With Sheets("Sheet2").PivotTables("SalesPivot")
  .PivotFields([1]).ClearAllFilters
  .PivotFields([2]).CurrentPage = "East"
End With

Drag options to blanks, or click blank then click option'

A"Region"

B"Product"

C"Sales"

D"Date"

Attempts:

3 left

💡 Hint

Common Mistakes

Using different fields for clearing filters and setting the page.

Filtering on a field that is not in the PivotTable.

Filtering is applied on the "Region" field to show only "East" data.

5fill in blank

hard

Fill all three blanks to group the PivotTable dates by months and years.

Excel

With Sheets("Sheet2").PivotTables("SalesPivot")
  .PivotFields([1]).Orientation = xlColumnField
  .PivotFields([2]).Group Periods:=Array([3])
End With

Drag options to blanks, or click blank then click option'

A"Date"

B"Region"

CxlMonths

DxlYears

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping a non-date field.

Using incorrect grouping constants.

The "Date" field is set as a column field and grouped by months.

Practice

1. What is the main purpose of a PivotTable in Excel?

easy

A. To sort data alphabetically only

B. To create charts and graphs automatically

C. To write complex formulas for calculations

D. To quickly summarize and analyze large sets of data

Solution

1. Step 1: Understand PivotTable functionality

   A PivotTable is designed to summarize and analyze data quickly without manual calculations.

2. Step 2: Compare options to PivotTable purpose

   Options B, C, and D describe other Excel features, not PivotTables.

3. Final Answer:

   To quickly summarize and analyze large sets of data -> Option D

4. Quick Check:

   PivotTable = Data summary tool [OK]

Hint: PivotTables summarize data fast without formulas [OK]

Common Mistakes:

• Confusing PivotTables with charts
• Thinking PivotTables create formulas
• Believing PivotTables only sort data

2. Which menu in Excel do you use to start creating a PivotTable?

easy

A. Insert

B. Formulas

C. Data

D. Review

Solution

1. Step 1: Recall Excel menu options

   The Insert menu contains the option to create PivotTables.

2. Step 2: Eliminate other menus

   Formulas is for functions, Data is for sorting/filtering, Review is for comments and proofing.

3. Final Answer:

   Insert -> Option A

4. Quick Check:

   Insert menu = PivotTable start [OK]

Hint: PivotTables start from Insert menu, not formulas [OK]

Common Mistakes:

• Looking under Formulas instead of Insert
• Choosing Data menu by mistake
• Confusing Review with data tools

3. You have a sales table with columns: Date, Product, Region, and Sales. You create a PivotTable and drag Region to Rows and Sales to Values. What does the PivotTable show?

medium

A. Sales data sorted by date

B. Total sales for each region

C. Average sales per product

D. List of all sales transactions by date

Solution

1. Step 1: Understand field placement in PivotTable

   Dragging Region to Rows groups data by region. Dragging Sales to Values sums sales per group by default.

2. Step 2: Interpret the result

   The PivotTable shows total sales for each region, not individual transactions or averages.

3. Final Answer:

   Total sales for each region -> Option B

4. Quick Check:

   Rows = Region, Values = Sum of Sales [OK]

Hint: Rows group data; Values calculate totals by default [OK]

Common Mistakes:

• Thinking it lists all transactions
• Assuming average instead of sum
• Confusing sorting with grouping

4. You created a PivotTable but the Sales values show as count instead of sum. What should you do to fix this?

medium

A. Change the value field settings from Count to Sum

B. Remove the Sales field and add it again

C. Sort the Sales column in ascending order

D. Refresh the PivotTable data source

Solution

1. Step 1: Identify why values show as count

   If Sales contains any text or blanks, Excel defaults to Count instead of Sum.

2. Step 2: Fix by changing value field settings

   Right-click the Sales field in Values area, choose Value Field Settings, then select Sum.

3. Final Answer:

   Change the value field settings from Count to Sum -> Option A

4. Quick Check:

   Value Field Settings control sum vs count [OK]

Hint: Set Values to Sum in Value Field Settings [OK]

Common Mistakes:

• Just removing and re-adding fields
• Sorting instead of changing aggregation
• Refreshing without fixing field settings

5. You want to analyze monthly sales trends by product category using a PivotTable. Your data has columns: Date, Category, Product, Sales. Which setup will best show total sales per month for each category?

hard

A. Drag Product to Rows, Sales to Values; filter by Date

B. Drag Category to Columns, Date to Rows, Sales to Values; no grouping

C. Drag Date to Columns, Category to Rows, Sales to Values; group Date by Months

D. Drag Sales to Rows, Date and Category to Values

Solution

1. Step 1: Arrange fields for monthly sales by category

   Placing Date in Columns and grouping by month shows time horizontally. Category in Rows groups products vertically.

2. Step 2: Confirm Sales in Values for totals

   Sales in Values sums sales per category per month.

3. Final Answer:

   Drag Date to Columns, Category to Rows, Sales to Values; group Date by Months -> Option C

4. Quick Check:

   Columns = Months, Rows = Categories, Values = Sales sum [OK]

Hint: Group dates by month in Columns for trend analysis [OK]

Common Mistakes:

• Not grouping dates by month
• Putting Sales in Rows instead of Values
• Swapping Rows and Columns incorrectly