Let’s suppose you have sales by state, and each state belongs to a sales region. What percentage of your sales come from each region?
First, Set Up Your PivotTable
1. Align your data in a table format, as shown here. Type “State,” “Region,” and “Sales” in row A.
2. Drag your mouse across the entire table to select it.
3. On the Ribbon, click on Insert, then click on PivotTable.
4. In the Create PivotTable dialog box, either accept the suggested range for the new table or select a new location. Then click OK.
5. Your column names from Step 1 now appear as fields in the PivotTable Field List on the right-hand side of the screen. Drag “Region” to the box marked Row Labels. Then drag “State” to the box marked Row Labels, placing it just below “Region.”
6. Drag “Sales” to the box marked Values.
Your new PivotTable should look like this:
Now Change the Format to Percentages
By default, the column “Sum of Sales” shows the sales dollars for each state, subtotaled in regions. Display the results as PivotTable percentages by following these steps:
1. Click on the triangle to the right of “Sum of Sales” in the Values box. A menu appears. Select Value Field Settings… to launch the dialog box.
2. Click on the tab labeled Show Values As.
3. In the dropdown listbox, select % of Column Total then click OK.
Your modified table should look like this:
Other Ideas
Now that you’ve mastered the technique, you can use the PivotTable percentage as a powerful tool to analyze which combination of states, products, employees, or customers have the most impact on your revenue, expenses, and profitability.
With a little extra effort, you can use a PivotTable displaying percentages when you have several different fields that overlap, such as product by state by sales rep, and more complex metrics, such as gross profit. Because the PivotTable sorts and groups the data automatically, you can browse to find the combination with the highest profit percentage, the lowest expense ratio, or the most favorable increase over last year.
How many other tools can you name that provide so much insight into the pushes and pulls that drive your business metrics?
No Comments
Hi:
In this lesson, above the paragraph “Other Ideas” – there is a table and above the table is the statement, “Your modified table should look like this:”
I don’t think that the depiction is correct. Is that what was intended as an insertion to the write-up? I think the depiction desired is the revised table with %ages shown.
The next question…..
When the “% of column total” is selected — I note that the heading does not change. It still says “sum of sales”;– shouldn’t it say “percentage of sales”? Is there a way to make the heading state “percentage of sales”?
Thanks.
Regards,
Gary
We appreciate the feedback and made corrections. The heading didn’t show correctly. But yes, you are right, the heading should have said “percentage of sales.” Thanks for your comment, and for reading!
thanks. I hope to get more….