Forum Discussion
Solved
Pivot Table Help
I would like to create a pivot table and possibly a graph that can show the month to month variance of sales in $ amount and % as well as the previous year. Something like this: Month Sale...
You can create a pivot table in Excel to show the month-to-month variance of sales in $ amount and % as well as the previous year.
Here’s how you can do it:
- Select the data that you want to use for your pivot table.
- Go to Insert > PivotTable.
- In the Create PivotTable dialog box, select the location where you want to place your pivot table and select OK.
- In the PivotTable Fields pane, drag the Month field to the Rows area, the Sales field to the Values area, and the Year field to the Columns area.
- Right-click on any value in the Sales column of your pivot table and select Show Values As > % Difference From > Previous Month to show the month-to-month variance in %.
- Right-click on any value in the Sales column of your pivot table and select Value Field Settings > Show Values As > Difference From > Previous Month to show the month-to-month variance in $ amount.
- To show the variance from last year, drag another instance of the Sales field to the Values area and right-click on any value in this column of your pivot table and select Show Values As > % Difference From > Previous Year or Value Field Settings > Show Values As > Difference From > Previous Year.
Once you have created your pivot table, you can create a chart based on it by selecting any cell within your pivot table and going to Insert > Recommended Charts.
I hope this helps!
You can create a pivot table in Excel to show the month-to-month variance of sales in $ amount and % as well as the previous year.
Here’s how you can do it:
- Select the data that you want to use for your pivot table.
- Go to Insert > PivotTable.
- In the Create PivotTable dialog box, select the location where you want to place your pivot table and select OK.
- In the PivotTable Fields pane, drag the Month field to the Rows area, the Sales field to the Values area, and the Year field to the Columns area.
- Right-click on any value in the Sales column of your pivot table and select Show Values As > % Difference From > Previous Month to show the month-to-month variance in %.
- Right-click on any value in the Sales column of your pivot table and select Value Field Settings > Show Values As > Difference From > Previous Month to show the month-to-month variance in $ amount.
- To show the variance from last year, drag another instance of the Sales field to the Values area and right-click on any value in this column of your pivot table and select Show Values As > % Difference From > Previous Year or Value Field Settings > Show Values As > Difference From > Previous Year.
Once you have created your pivot table, you can create a chart based on it by selecting any cell within your pivot table and going to Insert > Recommended Charts.
I hope this helps!