Excel PivotTable Percentage: Which Customers Are Costing You A Fortune?

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

Fred Pryor Seminars_PivotTable Percentage Figure 1

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:
Fred Pryor Seminars_PivotTable Percentage Figure 2

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.
Fred Pryor Seminars_PivotTable Percentage Figure 32. Click on the tab labeled Show Values As.
3. In the dropdown listbox, select % of Column Total then click OK.
Fred Pryor Seminars_PivotTable Percentage Figure 2c

Your modified table should look like this:

Fred Pryor Seminars_PivotTable Percentage Figure 2

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?

Local Seminars Related to this Topic:

Microsoft® Excel® Basics

Microsoft® Excel®: Beyond the Basics

Related PivotTables Articles

Categories

Monthly Archives

No Comments

  • Gary Prorok says:

    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

    • Excel Tips & Tricks says:

      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!

  • Konnah Bernard says:

    thanks. I hope to get more….

Leave a Reply to Konnah Bernard Cancel reply

Your email address will not be published. Required fields are marked *