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.