5 Ways To Show Percentage In Pivot Table Excel

Showing percentages in a Pivot Table in Excel can be a powerful way to analyze and visualize data. Whether you're trying to understand the proportion of sales by region, the percentage of defects in a manufacturing process, or the distribution of survey responses, Pivot Tables offer a flexible and dynamic way to summarize and present your data. Here are five ways to show percentages in a Pivot Table in Excel.

Understanding Pivot Tables

A screenshot of a Pivot Table in Excel, displaying various fields and options for customization.

Before diving into how to display percentages, it's helpful to understand the basic components of a Pivot Table. A Pivot Table is made up of fields, which can be dragged into different areas of the table to analyze data from various perspectives. The main areas are the Filter, Column Labels, Row Labels, and Values.

Setting Up Your Data for Percentages

To calculate percentages, you first need to ensure that your data is appropriately set up. Ideally, your data should be in a table format with each row representing an observation and each column a variable or field. Make sure your data is in a single table without any blank rows or columns, as this can cause issues with your Pivot Table calculations.

Method 1: Using the "Value Field Settings"

A screenshot showing how to access the Value Field Settings in a Pivot Table to set up percentage calculations.

One straightforward way to display percentages in a Pivot Table is by using the "Value Field Settings." Here’s how:

Drag a field into the Values area of your Pivot Table.
Right-click on the field and select "Value Field Settings."
In the Value Field Settings dialog box, click on the "Show Values As" tab.
Select "% of Grand Total" from the options. This will calculate the percentage of each value as part of the grand total.

Method 2: Calculating Percentages of a Specific Field

Sometimes, you might want to calculate percentages based on a specific field rather than the grand total. For instance, you might want to see the percentage of sales by region compared to the total sales of each region, not the grand total.

Drag the field you want to calculate percentages for into the Values area.
Right-click on the field and select "Value Field Settings."
In the Value Field Settings dialog, click on the "Show Values As" tab.
Select "% of" and then choose the specific field you want to calculate the percentage against.

Method 3: Using the "Show Values As" Option for Running Totals

A demonstration of how to set up running totals in a Pivot Table to display percentages.

If you're looking to display percentages that reflect running totals (for example, to show the cumulative percentage of sales over time), you can use another option within the "Show Values As" dialog.

Drag your date field to the Row Labels and your sales field to the Values area.
Right-click on the sales field and select "Value Field Settings."
In the Value Field Settings dialog, click on the "Show Values As" tab.
Select "% Running Total In" and then choose the field you're using for dates.

Method 4: Creating a Calculated Field for Custom Percentages

For more complex calculations, such as comparing the percentage change from one period to another, you might need to create a calculated field.

Go to the "Analyze" tab in the Pivot Table Tools.
Click on "Fields, Items, & Sets," and then select "Calculated Field."
Name your field, for example, "Percentage Change," and enter a formula that calculates the percentage change you're interested in.
Click "OK" to add the calculated field to your Pivot Table.

Method 5: Displaying Percentage Distribution with a Pie Chart

A pie chart showing the percentage distribution of responses based on a Pivot Table.

For a visual representation of percentage distribution, consider using a pie chart.

Select a cell outside your Pivot Table to place your chart.
Go to the "Insert" tab and click on "Pie or Doughnut Chart."
Select the pie chart option and click "OK."
In the "Chart Filters" pane, choose the field you want to display as a percentage distribution.

Gallery of Excel Pivot Table Percentages

Excel Pivot Table Percentage Calculations

A screenshot of a Pivot Table showing percentages for different regions.

A detailed view of the Value Field Settings dialog box for setting up percentage calculations.

A bar chart showing the percentage distribution of survey responses based on a Pivot Table.

An example of creating a calculated field in a Pivot Table for custom percentage calculations.

A pie chart showing the percentage distribution of sales by region based on a Pivot Table.

A Pivot Table showing running totals as percentages over time.

An example of a calculated field for percentage change in a Pivot Table.

A bar chart showing the percentage distribution of different categories based on a Pivot Table.

A Pivot Table showing percentages of the grand total for different fields.

An example of calculating percentages against a specific field in a Pivot Table.

Sharing Your Insights

After setting up your Pivot Table to display percentages, you might want to share your findings with others. Excel offers several ways to do this, including exporting your Pivot Table to other Microsoft Office applications like PowerPoint or Word, or sharing it directly with others by using Excel’s sharing features.

We hope this guide has been helpful in showing you the various ways you can display percentages in a Pivot Table in Excel. Whether you're analyzing sales trends, survey responses, or any other kind of data, being able to effectively communicate percentages is a powerful tool for insights and decision-making.