Refreshing PivotTable data in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to update the monthly sales PivotTable report after new sales data has been added.

📊 Data: You have a sales data table with columns: Date, Region, Product, and Sales Amount. New sales records have been added to the table.

🎯 Deliverable: You need to refresh the existing PivotTable so it includes the new sales data and then verify the updated total sales.

Sample Data

Date	Region	Product	Sales Amount
2024-05-01	North	Apples	120
2024-05-02	South	Oranges	150
2024-05-03	East	Bananas	100
2024-05-04	West	Apples	130
2024-05-05	North	Oranges	140
2024-05-06	South	Bananas	110
2024-05-07	East	Apples	115
2024-05-08	West	Oranges	125
2024-05-09	North	Bananas	105
2024-05-10	South	Apples	135
2024-05-11	East	Oranges	145
2024-05-12	West	Bananas	120
2024-05-13	North	Apples	130
2024-05-14	South	Oranges	155
2024-05-15	East	Bananas	115
2024-05-16	West	Apples	140
2024-05-17	North	Oranges	150
2024-05-18	South	Bananas	120
2024-05-19	East	Apples	125
2024-05-20	West	Oranges	130
2024-05-21	North	Bananas	110
2024-05-22	South	Apples	140
2024-05-23	East	Oranges	150
2024-05-24	West	Bananas	125
2024-05-25	North	Apples	135
2024-05-26	South	Oranges	160
2024-05-27	East	Bananas	120
2024-05-28	West	Apples	145
2024-05-29	North	Oranges	155
2024-05-30	South	Bananas	125
2024-05-31	East	Apples	130
2024-06-01	West	Oranges	135
2024-06-02	North	Bananas	115
2024-06-03	South	Apples	145
2024-06-04	East	Oranges	155
2024-06-05	West	Bananas	130

1

Step 1: Locate the existing PivotTable that summarizes sales by Region and Product.

No formula needed; just find the PivotTable on the worksheet.

Expected Result

You see a PivotTable showing total sales amounts grouped by Region and Product.

2

Step 2: Add the new sales data rows to the original sales data table if not already included.

Manually add new rows below existing data in the sales table.

Expected Result

The sales data table now includes all new sales records.

3

Step 3: Refresh the PivotTable to include the new data.

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

Expected Result

The PivotTable updates to show totals including the new sales data.

4

Step 4: Verify the updated total sales amount in the PivotTable matches the sum of the Sales Amount column in the data table.

=SUM(D2:D36) (assuming sales amounts are in D2 to D36)

Expected Result

The sum formula returns 4735, which matches the total sales shown in the PivotTable.

Final Result

PivotTable: Total Sales by Region and Product

+---------+---------+---------+---------+
| Region  | Apples  | Oranges | Bananas |
+---------+---------+---------+---------+
| North   | 385     | 445     | 330     |
| South   | 420     | 465     | 355     |
| East    | 370     | 450     | 335     |
| West    | 415     | 390     | 375     |
+---------+---------+---------+---------+
| Total   | 1590    | 1750    | 1395    |
+---------+---------+---------+---------+

✓Total sales amount is 4735 after including new data.

✓South region has the highest sales for Apples and Oranges.

✓West region leads in Bananas sales.

✓Refreshing the PivotTable successfully updated the report with new data.

Bonus Challenge

Create a PivotChart based on the refreshed PivotTable to visualize sales by Region and Product.

Show Hint

Select the PivotTable, then go to Insert > PivotChart, and choose a clustered column chart.