Refreshing PivotTable data in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to refresh all PivotTables in the active workbook.

Excel

ActiveWorkbook.[1]

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

ARecalculate

BUpdateAll

CRefreshAll

DRefreshPivotTables

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'UpdateAll' which is not a valid method.

Using 'Recalculate' which recalculates formulas but does not refresh PivotTables.

Using 'RefreshPivotTables' which is not a valid method.

The RefreshAll method refreshes all data connections and PivotTables in the workbook.

2fill in blank

medium

Complete the code to refresh a specific PivotTable named "SalesPivot" on the worksheet "Sheet1".

Excel

Worksheets("Sheet1").PivotTables("[1]").RefreshTable

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

ASalesPivot

BPivotSales

CPivotTable1

DSalesData

Attempts:

3 left

💡 Hint

Common Mistakes

Using an incorrect PivotTable name causes runtime errors.

Misspelling the worksheet name or PivotTable name.

The PivotTable name must match exactly. Here, the PivotTable is named "SalesPivot".

3fill in blank

hard

Fix the error in the code to refresh the PivotTable named "DataPivot" on the active sheet.

Excel

ActiveSheet.PivotTables([1]).RefreshTable

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

A"DataPivot"

BDataPivot

CActiveSheet

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting quotes around the PivotTable name.

Using an index number instead of the name when the name is required.

The PivotTables collection requires the PivotTable name as a string in quotes.

4fill in blank

hard

Fill both blanks to refresh all PivotTables on the worksheet named "Report".

Excel

For Each pt In Worksheets("[1]").PivotTables
    pt.[2]
Next pt

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

AReport

BRefreshTable

CSummary

DUpdate

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong worksheet name.

Using 'Update' instead of 'RefreshTable' which does not refresh PivotTables.

Use the worksheet name "Report" and the method RefreshTable to refresh each PivotTable.

5fill in blank

hard

Fill all three blanks to refresh all PivotTables in all worksheets of the active workbook.

Excel

For Each ws In ActiveWorkbook.[1]
  For Each pt In ws.[2]
    pt.[3]
  Next pt
Next ws

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

AWorksheets

BPivotTables

CRefreshTable

DSheets

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sheets' instead of 'Worksheets' which includes charts and other sheet types.

Using 'Update' instead of 'RefreshTable' method.

Loop through all worksheets, then all PivotTables in each worksheet, and refresh each PivotTable.