Refreshing PivotTable data in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

🎯 Scenario

intermediate

Refreshing PivotTable after data update

You have a PivotTable summarizing sales data. You just added new sales records to the source table. What should you do to see the new data in the PivotTable?

A Right-click the PivotTable and select 'Refresh'.

B Delete the PivotTable and create a new one.

C Close and reopen the Excel file.

D Change the PivotTable layout to force update.

💡 Hint

Think about how to update the PivotTable without recreating it.

Refreshing the PivotTable updates it with the latest data from the source without deleting or recreating it.

📊 Formula Result

intermediate

Effect of Refresh on PivotTable values

You have a PivotTable showing total sales by region. You add new sales data for a region not previously in the source. After refreshing the PivotTable, what will happen?

A The PivotTable will delete old regions and show only the new one.

B The new region will appear in the PivotTable with its sales total.

C The PivotTable will ignore the new region until recreated.

D The PivotTable will show an error because the region is new.

💡 Hint

Refreshing updates the PivotTable to include all current source data.

Refreshing includes all new data, so new regions appear automatically.

❓ Function Choice

advanced

VBA code to refresh all PivotTables

You want to write a VBA macro to refresh all PivotTables in the active workbook. Which code snippet correctly does this?

A ActiveWorkbook.RefreshAll()

B ActiveWorkbook.PivotTables.RefreshAll()

C For Each ws In ActiveWorkbook.Worksheets: For Each pt In ws.PivotTables: pt.RefreshTable: Next pt: Next ws

D For Each pt In ActiveWorkbook.PivotTables: pt.RefreshTable: Next pt

💡 Hint

Remember PivotTables belong to worksheets, not directly to the workbook.

PivotTables are objects inside worksheets, so you must loop through worksheets and their PivotTables to refresh all.

📊 Formula Result

advanced

PivotTable refresh and calculated fields

You have a PivotTable with a calculated field. After changing source data and refreshing, what happens to the calculated field?

A The calculated field updates automatically with new data.

B The calculated field is deleted and must be recreated.

C The calculated field shows errors until you refresh again.

D The calculated field does not update until you change its formula.

💡 Hint

Think about how PivotTables handle calculated fields on refresh.

Calculated fields remain and update their results automatically when the PivotTable refreshes.

❓ data_analysis

expert

Troubleshooting PivotTable not refreshing

You refreshed a PivotTable but the new data does not appear. Which is the most likely cause?

A The worksheet is protected, preventing refresh.

B The PivotTable cache is corrupted and needs repair.

C Excel does not support refreshing PivotTables automatically.

D The PivotTable source range does not include the new data rows.

💡 Hint

Check the data source range carefully.

If the source range does not cover new rows, refreshing won't show new data. You must update the source range.