قالب وردپرس درنا توس
Home / Tips and Tricks / How to Calculate the Percent Change with Pivot Tables in Excel

How to Calculate the Percent Change with Pivot Tables in Excel



  Excel Logo

Pivot Tables are an amazing integrated reporting tool in Excel. They are typically used to aggregate data with sums. However, you can also use it to calculate the percent change value between values. Better yet, it's easy to do.

You can do all sorts of things with this technique ̵

1; almost anywhere you want to see how one value compares to another. In this article we use a simple example of how to calculate and display the percentage by which the total sales value changes from month to month.

Here is the datasheet we will use.

 Two years sales data for a PivotTable

This is a fairly typical example of a sales sheet that includes the order date, customer name, salesperson, total sales value, and a few other things.

Doing Everything In this case, we first format our range of values ​​as a spreadsheet in Excel and then create a pivot table to perform and display the percentage change calculations.

Formatting the area as a table

If your data area is not already formatted as a table, we recommend that you do so. Data stored in tables has several advantages over data in cell ranges of a worksheet, especially when using PivotTables (read about the benefits of using tables.)

To format an area as a table, select the cell range and click on Insert> Table.

 Dialog Box

Make sure that the range is correct and that you have headers in the first row of this range. Then click on "OK".

The area is now formatted as a table. When you create PivotTables, charts, and formulas, it's easier to name the table in the future.

Under Table Tools, click the Design tab and enter a name in the box at the top of the ribbon. This table was called a "sale".

 Name the table in Excel

You can also change the style of the table here if you want.

Create a PivotTable to display the percentage change

Let's start creating the PivotTable. In the new table, click Insert> PivotTable.

The Create PivotTable window appears. Your table will be recognized automatically. However, you can choose at this point the table or area that you want to use for the PivotTable.

 The window

groups the dates into months

We then group the desired date field into the row area of ​​the PivotTable. In this example, the field is called Order Date.

As of Excel 2016, date values ​​are automatically grouped by years, quarters, and months.

If your version of Excel does not do this or you just want to change it In the grouping, right-click a cell that contains a date value, and then choose the Group command.

 Grouping Dates in a PivotTable

Select the groups you want to use. In this example, only years and months are selected.

 Years and Months in the Group Dialog Box

The year and month are now fields that can be used for analysis. The months are still referred to as the order date.

 Fields for Year and Order Date in Rows

Adding Value Fields to PivotTable

Move the Year field from the rows to the Filter area. This allows the user to filter the PivotTable for one year instead of overloading the PivotTable with too much information.

Drag the field with the values ​​(in this example, "Total Sales") that you want to calculate into the "Values" section twice .

It may not look like much yet. However, this will change very soon.

 Sales Value field was added twice to PivotTable

. Both value fields have the total by default and currently have no formatting.

The values ​​in the first column We want to keep as sums. However, they must be formatted.

Right-click a number in the first column and choose Number Formatting from the shortcut menu.

In the Format Cells dialog box, choose Accounting with 0 decimal places. [19659003ThePivotTablesnowlooksasfollows:

 Formatting the First Column

Creating the Percent Change Column

Right-click a value in the second column, point to "Show Values," and then click then on option "% difference of".

 Show values ​​as a percentage difference

Select "(Previous)" as the base element. This means that the current monthly value is always compared with the value of the previous months ("Order Date" field).

 Select

. The PivotTable now shows the values ​​and the percentage change.

 Display Values ​​and Percentage Change

Click in the cell with row captions, and enter "month" as the header for this column. Then click in the head cell for the second value column and enter "Variance".

 Renaming PivotTable headers

Adding some variance arrows

To really mitigate this PivotTable, we'd like to do this better visualize the percentage change by adding some green and red arrows.

This gives us a nice way to see if a change was positive or negative.

Click one of the values ​​in the second column, and then click Start> Conditional Formatting> New Rule. In the following Edit Formatting Rule window, do the following:

  1. Select the "All cells with variance values" option for the order date.
  2. In the Style list, select Symbol Sets .
  3. From the list of symbol styles, select the red, amber, and green triangles.
  4. In the Type column, change the list option to Count instead of Percent, which turns the Value column into 0 Exactly what we want.

  Applying Deviation Symbols with Conditional Formatting

Click "OK" and conditional formatting is applied to the PivotTable.

 The full drift PivotTable

PivotTables are an incredible tool and one of the easiest ways to display the percentage change in time for values.


Source link