top of page

You are learning PivotTables in MS Excel

How to calculate differences or ratios between PivotTable values?

There are two main ways to calculate differences or ratios between PivotTable values in Excel:

1. Show Values As:

This built-in feature allows you to display pre-defined calculations within your PivotTable itself. Here's how:

* Click on the value field you want to modify (e.g., Sales).
* From the dropdown arrow, select "Value Field Settings."
* In the settings window, under "Show Values As," choose the appropriate option:
* Difference from: This calculates the difference between the current value and the value of another item in the PivotTable (e.g., difference from previous month).
* % Difference from: This calculates the percentage difference between the current value and the value of another item.
* % of Parent Total: This displays the current value as a percentage of the parent category's total (e.g., Sales as a % of Total Sales by Region).
* % of Grand Total: This displays the current value as a percentage of the grand total for the entire PivotTable.

2. Calculated Fields:

This method allows you to create custom formulas within the PivotTable for more complex calculations. Here's how:

* Click on the "Analyze" tab in the PivotTable ribbon. (This tab might be hidden; if so, right-click anywhere on the PivotTable and check "Show PivotTable Analyze Tab.")
* In the "Fields, Items & Sets" group, click on "Calculated Field."
* In the "Name" field, enter a descriptive name for your calculation (e.g., "Sales Difference").
* In the "Formula" box, write your formula using existing PivotTable fields. Here are some examples:
* To calculate the difference between current month sales and previous month sales: `[This Month Sales] - [Previous Month Sales]`
* To calculate the percentage change between current month sales and previous month sales: `([This Month Sales] - [Previous Month Sales]) / [Previous Month Sales] * 100`
* Click "OK" to save your calculated field.
* The new field will appear in the PivotTable Fields list. You can drag it to the Values area to display the calculated results.

Remember:

* Whichever method you choose, ensure your PivotTable is set up with the necessary rows and columns to facilitate the calculations you want to perform.
* Calculated fields offer more flexibility but require formula knowledge.
* You can format the calculated values (differences or ratios) with appropriate number formats (e.g., percentage).

bottom of page