## You are learning PivotTables in MS Excel

How to calculate percentages and variances within a PivotTable?

There are two main ways to calculate percentages and variances within a PivotTable in Excel:

1. Using "Show Values As" Option:

This is a simple and quick way to display percentages or variances directly within your PivotTable.

* Percentages:

1. Right-click on a value field within the PivotTable.

2. Select "Show Values As" and choose the appropriate option:

- "% of Column Total" - This will show each value as a percentage of the column total.

- "% of Row Total" - This will show each value as a percentage of the row total.

- "% of Grand Total" - This will show each value as a percentage of the entire PivotTable's grand total.

* Variances: (This method works best for comparing values between two time periods)

1. Drag the value field you want to compare twice into the Values area of the PivotTable.

2. Right-click on the second value field you added.

3. Select "Show Values As" and choose "% Difference from" option.

4. In the dropdown menu, select the base field (e.g., previous year) you want to compare against.

This will display the original value and the percentage difference from the chosen base period.

2. Using Calculated Fields:

This method offers more flexibility for complex calculations.

* Percentages:

1. Click on the Analyze tab in the PivotTable ribbon.

2. In the Calculations group, click "Fields, Items, & Sets" and then "Calculated Field."

3. Give your new field a descriptive name (e.g., "% of Sales").

4. In the Formula box, enter the formula for calculating the percentage. For example, to calculate the percentage of total sales for each region, you could use: `=([Sales]) / SUM([Sales])`

5. Click "OK" to add the calculated field to your PivotTable.

* Variances:

1. Follow steps 1-3 from the percentages section above.

2. In the Formula box, enter the formula to calculate the variance. For example, to calculate the difference in sales between this year and last year, you could use: `=([Sales this year]) - ([Sales last year])`

3. Click "OK" to add the calculated field to your PivotTable.

Additional Tips:

* You can format the calculated percentages or variances with appropriate number formats (e.g., percentage signs for percentages).

* You can hide the original value columns if you only need to see the percentages or variances.

By using these methods, you can easily analyze and compare data within your PivotTables using percentages and variances.