Refreshing PivotTable data in Excel - Dashboard Guide

Dashboard Mode - Refreshing PivotTable data

Dashboard Goal

See updated sales summaries automatically after adding new sales data by refreshing the PivotTable.

Sample Data

Order ID	Product	Region	Sales
1001	Apples	East	120
1002	Bananas	West	150
1003	Apples	West	200
1004	Oranges	East	180
1005	Bananas	East	90
1006	Oranges	West	160

Dashboard Components

• PivotTable: Summarizes total sales by Product and Region.
  Located starting at cell G2.
  Created by converting the data range A1:D7 to an Excel Table, then inserting PivotTable from the Table.
  Rows: Product
  Columns: Region
  Values: Sum of Sales
• New Data Entry: Additional sales data entered below original data in rows 8 and 9.
  Example new rows:
  1007, Apples, East, 130
  1008, Bananas, West, 170
• Refresh Button: A button linked to the PivotTable refresh command.
  In Excel: Right-click PivotTable > Refresh or use Ribbon Data > Refresh All.
  This updates the PivotTable to include new data.

Dashboard Layout

+----------------------+----------------------+
|      Sales Data       |      PivotTable      |
|  (A1:D7 original)    |    (G2:J6 summary)   |
|  + New entries below  |                      |
|  (A8:D9 new data)     |                      |
+----------------------+----------------------+
|                  Refresh Button               |
+----------------------------------------------+

Interactivity

When you add new sales rows below the original data, the PivotTable does not update automatically.

Click the Refresh Button or use the Refresh command in Excel to update the PivotTable.

This action makes the PivotTable include the new rows in its summary.

Self Check

If you add a new sales row for Product = "Oranges", Region = "East", Sales = 210 below the data and then refresh the PivotTable, which component updates?

• The PivotTable updates to show the increased total sales for Oranges in the East region.
• The original data table shows the new row you added.
• The Refresh Button triggers the update but does not change itself.

Key Result

Dashboard shows sales summary by product and region using a PivotTable that updates after refreshing to include new data entries.

Practice

1. What does refreshing a PivotTable in Excel do?

easy

A. Updates the PivotTable to reflect changes in the source data

B. Deletes the PivotTable from the worksheet

C. Saves the workbook automatically

D. Changes the PivotTable layout to default

Solution

1. Step 1: Understand the purpose of refreshing

   Refreshing updates the PivotTable to show the latest data from the source.

2. Step 2: Identify what refreshing does not do

   It does not delete, save, or reset layout automatically.

3. Final Answer:

   Updates the PivotTable to reflect changes in the source data -> Option A

4. Quick Check:

   Refreshing = update data [OK]

Hint: Refreshing means updating PivotTable data to match source [OK]

Common Mistakes:

• Thinking refresh deletes the PivotTable
• Confusing refresh with saving the file
• Assuming refresh changes layout automatically

2. Which of the following is a correct way to refresh a PivotTable in Excel?

easy

A. Delete the PivotTable and recreate it

B. Press Ctrl + S to refresh

C. Double-click the PivotTable to refresh

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

Solution

1. Step 1: Identify the refresh method

   Right-clicking the PivotTable and choosing 'Refresh' is a standard way to update it.

2. Step 2: Eliminate incorrect options

   Ctrl + S saves the file, double-clicking edits data, deleting recreates but is not refreshing.

3. Final Answer:

   Right-click the PivotTable and select 'Refresh' -> Option D

4. Quick Check:

   Right-click + Refresh = update PivotTable [OK]

Hint: Right-click PivotTable and pick Refresh to update [OK]

Common Mistakes:

• Pressing Ctrl + S expecting refresh
• Double-clicking PivotTable to refresh
• Deleting PivotTable instead of refreshing

3. You have a PivotTable based on data in Sheet1. You add new rows to the source data but do not refresh the PivotTable. What will the PivotTable show?

medium

A. It will show an error message

B. It will include the new rows automatically

C. It will show data only up to the last refresh

D. It will delete the old data

Solution

1. Step 1: Understand PivotTable behavior without refresh

   PivotTables do not update automatically when source data changes; they show data as of last refresh.

2. Step 2: Analyze options

   New rows are not included until refresh; no error or deletion occurs.

3. Final Answer:

   It will show data only up to the last refresh -> Option C

4. Quick Check:

   No refresh = old data shown [OK]

Hint: PivotTable shows old data until refreshed [OK]

Common Mistakes:

• Assuming PivotTable updates automatically
• Expecting error message without refresh
• Thinking old data is deleted automatically

4. You refreshed a PivotTable but the new data is still not showing. What is the most likely cause?

medium

A. The PivotTable is corrupted and must be deleted

B. The source data range does not include the new data

C. PivotTables cannot be refreshed more than once

D. You need to save the workbook first

Solution

1. Step 1: Check the source data range

   If the source range does not cover new rows, refresh won't show new data.

2. Step 2: Rule out other causes

   Saving is unrelated; PivotTables can refresh multiple times; corruption is rare and deletion is last resort.

3. Final Answer:

   The source data range does not include the new data -> Option B

4. Quick Check:

   Source range must include new data [OK]

Hint: Check source range covers new data before refreshing [OK]

Common Mistakes:

• Thinking saving triggers refresh
• Believing PivotTables refresh only once
• Assuming PivotTable corruption without checking range

5. You have a PivotTable linked to a dynamic Excel Table named 'SalesData'. After adding new sales records to 'SalesData', what is the best way to ensure the PivotTable shows all updated data?

hard

A. Refresh the PivotTable; the Table range updates automatically

B. Manually change the PivotTable source range to include new rows

C. Delete and recreate the PivotTable each time new data is added

D. Save the workbook and close Excel to update the PivotTable

Solution

1. Step 1: Understand Excel Tables and PivotTables

   Excel Tables automatically expand their range when new rows are added.

2. Step 2: Refresh PivotTable to update data

   Refreshing the PivotTable updates it to include all data in the Table without manual range changes.

3. Step 3: Eliminate other options

   Manual range changes are unnecessary; deleting is inefficient; saving/closing does not refresh data.

4. Final Answer:

   Refresh the PivotTable; the Table range updates automatically -> Option A

5. Quick Check:

   Excel Table + Refresh = updated PivotTable [OK]

Hint: Use Excel Table and refresh PivotTable to auto-update data [OK]

Common Mistakes:

• Manually adjusting source range for Table data
• Deleting PivotTable instead of refreshing
• Thinking saving or closing updates PivotTable