Excel Pivot Charts: Show Values and Percentages

Yes. In Excel for Microsoft 365 on Windows, add the same numeric field to a PivotTable’s Values area twice, then set the second copy to a percentage calculation under Value Field Settings > Show Values As. Create a PivotChart from that PivotTable; it can plot the raw values and percentage field together, although a regular chart or a pie chart may be clearer when you need both figures to be easy to read.

What it means to show a value and a percentage

A value is the underlying total, such as $480 in sales. A percentage expresses that value relative to a chosen base: for example, $480 as a share of all sales, of the sales in its region, or of the sales in its product category. Excel does not infer which base you intend. You choose the comparison using Show Values As, and that choice determines what the percentage means.

There are two common goals that sound similar but call for different chart setups:

Show both measures as plotted series: chart the raw total and the percentage as separate series. This is useful for comparing patterns, but the different scales can make one series difficult to see.
Show the value and its share as labels: a pie PivotChart can show each slice’s value and percentage in its data labels. This is usually the most direct choice when the question is how each category contributes to one total.

A percentage is not a second version of the raw number. It answers a different question, so name the field to make its meaning explicit. For instance, use Sales and % of Grand Total rather than leaving both series named Sum of Sales.

How PivotTable percentage calculations work

PivotCharts use the structure and calculations of their associated PivotTable. A percentage field is created in the PivotTable by displaying a value field as a percentage of a selected base. The source data does not have to contain a separate percentage column. Excel calculates the share from the summarized values shown in the PivotTable.

For example, suppose a PivotTable summarizes sales by product. If Product A totals 480 and the grand total is 1,200, its percentage of the grand total is 40%. If you instead choose percentage of the row total or column total, Excel calculates against that row’s or column’s subtotal. The denominator matters: the same 480 can represent different percentages depending on the grouping and comparison you select.

Show Values As changes how a value field is displayed; it does not change the source records. You can keep one instance of Sales as a normal sum and use a second instance for the percentage. That is the key to showing both kinds of measure in the same PivotTable and making both available to a PivotChart.

Add the same field twice and set one copy to a percentage

Start with a source table that has a numeric measure, such as Sales, and categories such as Product, Month, or Region. Create a PivotTable, then add the category fields to Rows, Columns, or Filters as appropriate. Add Sales to Values twice. If the field is not summarized as a sum, open its Value Field Settings and choose Sum when the source values are numbers that should be added.

Click a cell in the PivotTable and open the PivotTable Fields pane if it is not visible. Drag the numeric field, such as Sales, into the Values area twice.
In the Values area, open the menu for the second Sales entry and choose Value Field Settings.
In the dialog, select the Show Values As tab. Choose the percentage calculation that matches the question, such as % of Grand Total, % of Row Total, or % of Column Total.
On the Custom Name box, give this copy a clear name, such as % of Grand Total. Use the Number Format button in the dialog to choose Percentage and set the desired decimal places.
Leave the first Sales entry as a normal sum. Check that the PivotTable now has one value column for the amount and another for the percentage.
Click in the PivotTable and choose PivotTable Analyze > PivotChart. Select a chart type and confirm. The chart will use the fields and layout in the PivotTable.

In some Excel layouts, the Values area appears as a field called Σ Values. If you have more than one value field, Excel may place that field in the Rows or Columns area of the PivotTable. Moving it changes whether the measures appear side by side or in separate chart categories; it does not change their underlying calculations.

Choose the right percentage base

Pick a denominator that matches the sentence you want a reader to be able to say about the chart. If the chart asks how much each product contributes to all sales, use % of Grand Total. If it asks what share each product has within its region, arrange the PivotTable by region and product, then choose a row or column percentage that uses the regional subtotal as the base. Confirm the direction by reading the row and column headings in the resulting PivotTable.

% of Grand Total: each displayed item is divided by the overall total represented by the PivotTable. Shares across the full set of items generally add to 100%, provided the displayed items represent that full set.
% of Row Total: each item is divided by the total for its row. This helps compare the mix within each row, such as products within a region when regions are rows.
% of Column Total: each item is divided by the total for its column. This can show a category’s share within each column group, such as a product mix within each month when months are columns.

These percentages may not add to 100% across the entire chart when the denominator is a separate subtotal for each row or column. A filtered PivotTable can also change what appears in its displayed totals. Before presenting a percentage chart, inspect the PivotTable and check that the visible categories and total correspond to the intended comparison.

Make both measures legible in the chart

When you plot raw amounts and percentages as separate series in a column or bar PivotChart, Excel normally puts both on the same value axis. That is often a poor visual comparison: sales might range into thousands while percentages range from 0% to 100%. The raw-value series can dominate the chart, leaving the percentage bars too small to interpret. Formatting the percentage field as a percentage changes its number format, not the scale relationship between the two measures.

For a composition question, a pie PivotChart is often simpler. Create it from a PivotTable with one category field and one normal value series. Select the chart, use Chart Design > Add Chart Element > Data Labels > More Data Label Options, and in the Format Data Labels pane select Value and Percentage. You can clear other label options if they add clutter. The labels can then show, for example, 480 and 40% for a slice whose total is 1,200. A pie chart is most useful when the categories form parts of one whole; it is harder to compare exact amounts across many slices.

For a trend over time, a practical alternative is a regular chart based on a small range that references the PivotTable results. Use a combo chart with amounts as columns and percentages as a line on a secondary axis. A PivotChart does not offer the same freedom to combine chart types and axes as a standard chart in Excel. Create a separate, ordinary chart from a helper range linked to the PivotTable if you need that combo presentation. Keep the helper range aligned with the PivotTable’s categories and check it after filters or refreshes change the PivotTable size.

Filters, refreshes, and chart behavior

A PivotChart follows its PivotTable. Changing a report filter, slicer, or field selection changes the summarized data available to the chart, and the percentage calculation is evaluated against the current PivotTable context. If you filter out categories, the visible values and their shares may change. This is expected when the chosen denominator is the displayed grand total, but it may surprise readers who expect shares of the complete source dataset.

When the source data changes, refresh the PivotTable so that its summary and chart update. Click inside it and choose PivotTable Analyze > Refresh, or use Data > Refresh All when you want to refresh workbook connections and other PivotTables too. If new source rows are not included, confirm that the PivotTable source range covers them. Formatting the source data as an Excel table before creating the PivotTable can make it easier to include new rows when refreshing.

After a refresh, check the percentage field’s name, number format, and calculation setting. Also inspect the chart legend and axis labels. A refreshed chart can remain technically correct but become harder to understand if a new category expands the scale or if the field layout changes how the series are arranged.

Common problems and practical fixes

The percentage is displayed as a decimal. Open Value Field Settings for the percentage copy, choose Number Format, and select Percentage. This formats the display without changing the calculation.
Both series have the same name. Set a distinct Custom Name for the second value field. A clear legend is important because the chart is plotting two versions of the same source measure.
Percentages add up to more than 100% or do not total 100%. Check whether you selected percentage of row or column total rather than grand total. Also check whether filters hide items or whether the displayed categories are not the complete set.
The percentage bars look almost flat. The amount and percentage are sharing an axis with different scales. Use a pie chart with value-and-percentage labels for a part-to-whole view, or create a regular combo chart with a secondary axis from a helper range.
The chart shows counts instead of sums. In Value Field Settings, inspect Summarize Values By. Excel may use Count if the source column contains text or blanks mixed with numbers. Check the source data and select Sum when appropriate.
A percentage changes after filtering. This can be correct when the denominator is the filtered PivotTable total. If you need a fixed benchmark, consider displaying the benchmark separately or build a chart from a purpose-designed summary range.

PivotChart versus a regular chart

A PivotChart is convenient when viewers need to explore the data with PivotTable fields, filters, or slicers. Its series and categories remain tied to the PivotTable structure. It is usually the right starting point when the main requirement is interactive grouping and filtering, and duplicated value fields make both the amount and the percentage available.

A regular chart offers more control over chart types, axes, and layout. It is a better option when the chart must combine unlike measures, such as a sales column series and a percentage line, or when you want a specific presentation that PivotChart restrictions make awkward. A helper range can refer to PivotTable output so that the regular chart uses summarized data without being a PivotChart itself. The trade-off is maintenance: if the PivotTable changes shape, you may need to adjust the helper range or chart source.

For a simple share-of-total visual, a pie or doughnut chart with labels can show both the amount and the share without plotting a second series. For comparisons across many categories or across time, a bar, column, or line chart of values is often easier to read; place the percentage in a neighboring PivotTable or use a regular combo chart when showing both measures in the graphic is essential.

Frequently Asked Questions

Can an Excel PivotChart show a value and percentage at the same time?
Yes. Add the same numeric field to the PivotTable Values area twice, keep one as the normal sum, and set the other to a percentage under Value Field Settings > Show Values As. Both fields are then available to the PivotChart. Whether both are easy to read depends on the chart type and scale.

How do I show a percentage on a PivotChart without changing the original value?
Keep the original value field and add a second copy. Apply the percentage calculation only to the second copy. Show Values As changes the summarized display for that copy; it does not overwrite the source data or the first value field.

How can I show value and percentage on a pie PivotChart?
Select the pie chart, then choose Chart Design > Add Chart Element > Data Labels > More Data Label Options. In the Format Data Labels pane, select Value and Percentage. Make sure the chart uses the category and value series you intend to describe.

Why do my PivotChart percentages not add up to 100%?
You may be showing percentages of separate row or column totals, which add to 100% within each base rather than across the whole chart. Filters, hidden categories, and a different denominator can also affect the visible total. Check the Show Values As choice and compare the chart with its PivotTable.

Can I put percentages on a secondary axis in a PivotChart?
PivotCharts have limitations for combination chart types and secondary-axis layouts. For a value-and-percentage combo with separate scales, create a regular combo chart from a helper range linked to the PivotTable, then assign the percentage series to a secondary axis.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.