In an Excel pivot table, you can use Excel’s built-in custom calculations, for a different view of the data.
For example, in the video below, I set up a pivot table to show what % of a company’s monthly sales were Binders. Also, what % of Binder sales was for each colour – red, blue, and black.
Note: You can get the sample Excel file that I used for this video on the Pivot Table Show Values As page, on my Contextures site.
Binder Sales – % By Colour
In the screen shot below, the pivot table has 4 fields in the worksheet layout:
- Item and Colour in the Row area
- Month in the Column area
- Sum of Units sold, in the Values area
Following the steps listed in the section below, I used pivot table custom calculations, to show additional sales details:
- the % for each colour‘s sales – Black, Blue and Red
- compared to the item‘s total sales
- in each month – Jan and Feb
Show % of Item Sales
In the pivot table screen shot below, I’ve added a 2nd copy of the Units field to the values area.
- Tip: To add a 2nd copy of a pivot field, drag it from the PivotTable Field list, onto the Values area in the field list
For the 2nd copy of the Units field, I followed the steps below, to change that field’s calculation settings
- Right-click one of the Units value cells
- In the pop-up menu, click Show Values As
- In the next pop-up menu, click % of Parent Row Total
In the pivot table, the second Units field changed, to show:
- the % for each colour‘s sales
- compared to the item‘s total
- in each month.
For example,
- 195 Binders were sold in January
- 51% (100 units) of those January Binders were Black colour
More Pivot Table Info
For more Pivot Table Subtotal tips, videos and examples, visit the following links:
Show Percent of Subtotal in Pivot Table
Pivot Table Show Values As % of Parent Total
Show Percent Of Subtotal In Pivot Table
______________________
