Filtering PivotTable data in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to filter the PivotTable to show only items labeled "East" in the Region field.

Excel

ActiveSheet.PivotTables("SalesPivot").PivotFields("Region").CurrentPage = [1]

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

A"West"

B"East"

C"North"

D"South"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a region name not present in the PivotTable.

Forgetting to use quotes around the region name.

Trying to assign a number instead of a string.

Setting CurrentPage to "East" filters the PivotTable to show only the East region.

2fill in blank

medium

Complete the code to clear all filters from the "Product" field in the PivotTable.

Excel

ActiveSheet.PivotTables("SalesPivot").PivotFields("Product").ClearAllFilters[1]

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

A()

B.Reset

C.ApplyFilter

D.RefreshTable

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the parentheses after ClearAllFilters.

Using .ApplyFilter or .RefreshTable instead of ClearAllFilters.

Adding extra parameters inside the parentheses.

The ClearAllFilters() method removes all filters from the specified PivotField.

3fill in blank

hard

Fix the error in the code to filter the "Year" field to show only 2023.

Excel

ActiveSheet.PivotTables("SalesPivot").PivotFields("Year").CurrentPage = [1]

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

A2023

B"2023"

CYear

DActiveSheet.PivotTables("SalesPivot")

Attempts:

3 left

💡 Hint

Common Mistakes

Assigning a number instead of a string.

Using an object or variable instead of the item name.

Forgetting quotes around the filter value.

The CurrentPage property expects a string, so the year must be in quotes.

4fill in blank

hard

Fill both blanks to filter the "Category" field to show only "Electronics" and then refresh the PivotTable.

Excel

With ActiveSheet.PivotTables("SalesPivot")
  .PivotFields("Category").CurrentPage = [1]
  .[2]
End With

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

A"Electronics"

BRefreshTable

CPivotCache.Refresh

DCalculate

Attempts:

3 left

💡 Hint

Common Mistakes

Using a method other than RefreshTable to refresh.

Not using quotes around the filter value.

Calling RefreshTable before setting the filter.

Set CurrentPage to "Electronics" to filter, then call RefreshTable to update the PivotTable display.

5fill in blank

hard

Fill all three blanks to create a filter on the "Region" field that shows only "North", then clear all filters on the "Product" field.

Excel

With ActiveSheet.PivotTables("SalesPivot")
  .PivotFields("Region").CurrentPage = [1]
  .PivotFields("Product").[2][3]
End With

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

A"North"

BClearAllFilters

C()

DRefreshTable

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting parentheses after ClearAllFilters.

Using RefreshTable instead of ClearAllFilters to clear filters.

Not using quotes around the filter value.

Set the Region filter to "North" (string), then call ClearAllFilters() on the Product field to remove filters.