[Excel] Once you have created a pivot table, it's not over!How to reflect the updated original data in the analysis

I want to reflect the changes in the original table to the pivot table!

I think there are many people who have used pivot tables in their daily work. I think many people have heard of it even if they have never used it.

Pivot tables are a useful feature for aggregating and analyzing data, and have the advantage of being intuitive with the mouse, without the need for cumbersome formulas or special functions.

If you manage and operate the pivot table for a long time, data may be added or changed to the table that is the basis of the pivot table. Have you ever been in trouble because the data changes in the original table were not reflected in the pivot table?

This time, I will explain how to reflect the data of the table that is the basis of the pivot table to the pivot table in the pivot table of Excel.

Let's make a pivot table

Before explaining the theme of this article, "How to reflect the data of the table that is the basis of the pivot table in the pivot table", I will explain how to create the pivot table again. increase.

As an example, let's use the following "goods management table" to aggregate monthly amounts by department. With any cell (1) selected in the table, click the [Insert] tab (2) → [Pivot Table] (3).

With any cell (1) selected in the table, click the [Insert] tab (2) → [Pivot Table] (3).

The PivotTable from Table or Range dialog box is displayed. In the [Table / Range] field, the cell range to be used as a pivot table is automatically entered (④), so check that it is entered correctly. Here, we will create a pivot table on a new sheet, so select [New Worksheet] (⑤).

After setting, click [OK] (⑥) to close the dialog box.

In the [Table / Range] field, the cell range to be used as a pivot table is automatically entered (④). Since a pivot table will be created on a new sheet, select [New Worksheet] (⑤).After setting, click [OK] (⑥)

Then, a new sheet is created and an empty pivot table (⑦) is created. The PivotTable Fields task pane (8) is displayed on the right side of the screen.

An empty PivotTable (⑦) is created on the new sheet, and the [PivotTable Fields] task pane (8) is displayed on the right side of the screen.

Now, select the item you want to display from the field list (9) and add it to the pivot table. This time, the monthly amount will be aggregated for each department, so select "Department name", "Date", and "Amount" from the field list.

Select the item you want to display from the field list (⑨)

First, drag [Date] from the field list to the [Row] area (⑩). Then, at the same time as placing the date, [Month] (⑪) will be placed automatically (depending on the version and settings of Excel, "Month" may not be placed automatically).

Drag [Date] from the field list to the [Row] area (⑩).Then, [Month] (⑪) will be automatically placed.

Next, drag [Department Name] to the [Column] area to place it (⑫), and drag [Amount] to the [Value] area to place it (⑬).

Drag [Department Name] to the [Column] area to place it (⑫). Drag [Amount] to the [Value] area to place it (⑬).

A pivot table for each department was created on the sheet, which was aggregated by month (14).

A monthly pivot table for each department has been created (14)

This is the basic operation of the pivot table. In the next section, we will explain the main theme of this time, "When the data of the table that is the basis of the pivot table is changed, how to reflect it in the pivot table".

Let's change and reflect the values ​​in the original table

First, let's display the table on which the pivot table is based. Currently, the number "15" (①) is entered in cell "G2" in this table, but let's change this to "50".

Change the numerical value "15" (①) entered in cell "G2" of this table to "50".

Changed the value of cell "G2" to "50" (②). In this table, the formula "Unit price x Quantity" is entered in column H, so the value in cell "G2" changes and the amount in cell "H2" changes at the same time. Earlier, "1,500" was changed to "5,000" (③).

If you change the value of cell "G2" to "50" (②), the amount of cell "H2" will also be "5,000" (③) at the same time.

Return to the PivotTable seat. Click [+] (④) displayed on the left side of the cell in the column where the month is entered (column A) to expand the data and check the daily data (⑤). Let's check the amount of "Development Section 2" of "September 3" that was changed earlier. It is displayed as "1500" (⑥). The previous changes have not yet been reflected. Therefore, click the [Pivot Table Analysis] tab (⑦) → [Update] (⑧).

You can check the daily data by clicking [+] (④) displayed on the left side of the cell in column A (⑤).Since the amount of "Development Section 2" of "September 3" changed earlier is still "1500" (⑥), click the [Pivot Table Analysis] tab (⑦) → [Update] (⑧).

The amount of "Development Section 2" (value of cell "G6") of "September 3" has been updated (⑨).

The amount (value) of cell "G6" has been updated (⑨)

If you want to update the data, use the [Update] button.

This time, I explained how to reflect the data of the table that is the basis of the pivot table in Excel to the pivot table.

If you make changes to the original table, don't forget to click the PivotTable Analysis tab → Update to reflect the changes in the PivotTable.