Percentages in a PivotTable

In a PivotTable, you can create fields that will show you the percentage change of the previous value. This is especially popular in companies that want to see how did the sales changed compared to the previous month.

We are going to use the following example.

Calculate the percentage difference

In order to know how the sales changed from month to month, you have to follow these steps.

  1. Copy the Sales column to column C and name it Change.
  2. Click any cell inside the table.
  3. Navigate to Insert >> Tables and click PivotTable.
  4. When the Create PivotTable window appears, click OK without changing any options.
  5. Inside the PivotTable fields check all the fields.
  1. You can see in the newly created table, that you have two fields with the same values but different names.
  1. Right-click any fields inside the Sum of Change and choose Show Values As >> % Difference From….
  2. Change Base Item to (previous). It will compare the difference between this month and the previous one. This difference will be displayed in percentages.
  1. Click OK to see the result.

To make this PivotTable even more interesting, select all the values inside the Sum of Change field and navigate to Home >> Styles. Click Conditional Formatting >> Color Scales and choose the first option.

This is how the PivotTable looks like.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.