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
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?
