How to add both value and percentage to Pivot Chart?

Question

How to add both value and percentage to Pivot Chart?

sharon 0

The % column in my pivot table is already set as % of grand total, and when I insert the pivot chart, there is no % shown in the data label but only as the legend.

Please help i'm going crazy!

1 answer

Your answer

Answer 1

Emmanuel Santana 33,415 Independent Advisor

Hello. Can you confirm which version of Excel for Mac you're using (e.g., Excel 365, Excel 2019, etc.)?

Here’s a workaround that works on all versions:

Add a helper column next to your Pivot Table:

   =TEXT(B2,"#,##0") & " (" & TEXT(C2,"0%") & ")"

  This creates a label like “835 (26%)”.

Select your Pivot Chart, click a bar to select the data series, then:
- Right-click → Add Data Labels
- Right-click again → Format Data Labels
- If available, choose “Value From Cells” and select the helper column.

If "Value From Cells" isn’t shown, your version may not support it for Pivot Charts, in that case, you can:

Copy the Pivot Table to a normal range.
Create a standard chart (not Pivot Chart) from that.
Use the helper column to display both values and percentages.

This allows full control over how your labels appear without relying on PivotChart limitations. Let us know your version and we’ll help further if needed.

sharon 0 Reputation points

2025-09-22T23:01:23.08+00:00
Thank you for your reply! I'm running on version 16.101 of Excel 365.

Subsequent to my post, I could see the % data series when I changed the design of the chart (i.e. this is Style 5 instead of Style 1).

why is the data series only visible in specific chart designs?

I tried to follow this advice (https://dori-uw-1.kuma-moon.com/en-us/answers/questions/5250759/how-to-add-both-percentage-and-number-in-the-pivot) before your reply and got the % column to be on the secondary axis and now both data series are overlapped. I then set no fill for the % data series since I only wanted the percentages. But now I'm stuck with having to manually adjust each percentage to be outside of the bar and under the value.

would creating a helper column like you suggested be a more efficient way? Also, why is Excel so counterintuitive in that I have to create a column outside of the pivot table when there already is a % column?
Emmanuel Santana 33,415 Reputation points Independent Advisor

2025-09-23T05:19:24.97+00:00
Thanks for the update. Well, Excel’s chart styles, especially for PivotCharts, have preset behavior for showing data labels and series. For example, Styles 1 and 2 prioritize clarity for single-series data, while Style 5 is configured to show multiple series, secondary axes, and external labels by default. These aren’t custom settings, they're built into the theme logic.

Why do I need a helper column when the % is already in the PivotTable?

This is a known design limitation:

“Value From Cells” only supports normal ranges, not PivotTable fields. You can’t reference internal PivotTable calculations as label sources. → Microsoft Docs – Add or remove data labels in a chart

PivotCharts restrict formatting and formulas. You can’t combine value + percentage in a label using built-in chart options if both fields exist in the PivotTable.

This is not new or a bug, it's been the case for over a decade. Even in the latest Excel 365 (v16.101 for Mac), “Value From Cells” only works on regular charts.

Is using a helper column better than adding a second series (and hiding it)?

Yes:

Cleaner layout: No dual axes or transparent series.

One combined label: Show 835 (26%) in a single text block.

Fewer manual tweaks: Labels stay aligned when data updates.

This workaround gives you full control over formatting and simplifies future updates.

Share via

How to add both value and percentage to Pivot Chart?

1 answer

Your answer