Create a Pivot Table Percent Distribution View for Your Excel Reports

By Michael Alexander

One of the more useful report views in an Excel pivot table is the percent distribution view. A percent distribution (or percent contribution) view allows you to see how much of the total is made up of a specific data item. This view is useful when you’re trying to measure the general impact of a particular item.

The pivot table, as shown in this figure, gives you a view into the percent of sales that comes from each business segment. Here, you can tell that bikes make up 81 percent of Canada’s sales whereas only 77 percent of France’s sales come from bikes.

image0.jpg

Here are the steps to create this type of view:

  1. Right-click any value within the target field.

    For example, if you want to change the settings for the Sales Amount field, right-click any value under that field.

  2. Select Value Field Settings.

    The Value Field Settings dialog box appears.

  3. Click the Show Values As tab.

    Select % of Row Total from the drop-down list.

  4. Click OK to apply your change.

The pivot table in this figure gives you a view into the percent of sales that comes from each market. Here, you have the same type of view, but this time, you use the % of Column Total option.

image1.jpg

Again, remember that because you built these views in a pivot table, you have the flexibility to slice the data by region, bring in new fields, rearrange data, and most importantly, refresh this view when new data comes in.