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 ̵
Here is the datasheet we will use.
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.
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".
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.
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.
Select the groups you want to use. In this example, only years and months are selected.
The year and month are now fields that can be used for analysis. The months are still referred to as the order date.
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.
. 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:
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".
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).
. The PivotTable now shows the values and the 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".
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:
- Select the "All cells with variance values" option for the order date.
- In the Style list, select Symbol Sets .
- From the list of symbol styles, select the red, amber, and green triangles.
- In the Type column, change the list option to Count instead of Percent, which turns the Value column into 0 Exactly what we want.
Click "OK" and conditional formatting is applied to the PivotTable.
PivotTables are an incredible tool and one of the easiest ways to display the percentage change in time for values.