Chart & Visualization Issues

How to Display Values and Percentages in an Excel PivotChart

Huma Ashraf Ch Oct 10, 2026 869 views

Question details

The user needs to create an Excel PivotChart that simultaneously displays both the underlying raw numeric values and their corresponding percentages, such as the percentage of the grand total.

How to Display Values and Percentages in an Excel PivotChart

Product: Excel
Device & OS: not provided
Scenario: Creating advanced data visualizations where both absolute numbers and relative proportions (percentages) must be visible and clearly labeled on the same chart.
Observed behavior: To present two metrics with vastly different scales (large numeric values vs. small percentage decimals) on a single chart without the smaller values becoming invisible, requiring data labeling and dual-axis formatting.

Before you start

Ensure your source data is organized in a clean tabular format with headers, and that you have already created a basic PivotTable from which the PivotChart will pull its data.

Solution 1Recommended

Configure PivotTable Values and Add a Secondary Axis

By adding the same data field twice to your PivotTable, you can display one as a standard number and the other as a percentage. Using a secondary axis ensures both are visible on the chart.

Because percentages are stored as small decimals (e.g., 50% is 0.5), plotting them on the same axis as large raw numbers will make the percentage columns appear flat. A secondary axis is required to scale both data series properly.

Add the data field twice

Click inside your PivotTable to open the PivotTable Fields pane. Drag the desired numeric field into the 'Values' area two times.

Configure the percentage field

Right-click the second instance of the field in the PivotTable, select 'Value Field Settings', navigate to the 'Show Values As' tab, and choose '% of Grand Total' from the dropdown menu. Click OK.

Insert the PivotChart

Go to the 'Insert' tab on the ribbon and select 'PivotChart'. Choose your preferred chart type (such as a Clustered Column chart) and click OK.

Enable a Secondary Axis

In the PivotChart, right-click the data series representing the percentages (it may be barely visible at the bottom) and choose 'Format Data Series'. In the format pane, check the bubble for 'Secondary Axis'.

Add Data Labels

Click the green plus (+) icon in the top right corner of the chart and check the box for 'Data Labels'. This will display both the exact numeric values and the percentage figures directly on the chart elements.

Configure PivotTable Values and Add a Secondary Axis

Chart Readability: Changing the chart type for the percentage series to a 'Line' chart while keeping the numeric values as a 'Column' chart (a Combo Chart) often provides the cleanest visual representation.

Solution 2

Save the Configured Chart as a Template

PivotCharts can occasionally lose custom formatting (like secondary axes) when underlying data is heavily filtered or refreshed. Saving a template prevents you from having to rebuild it.

Advanced Data Visualization

Easily Create Dual-Axis PivotCharts in WPS Spreadsheet

WPS Spreadsheet provides a seamless, powerful environment for complex data analysis. You can effortlessly drag and drop fields to create PivotTables showing both numeric values and percentages, and visualize them using professional dual-axis PivotCharts.

1. Create a PivotTable: Open your dataset in WPS Spreadsheet, go to the Insert tab, and click PivotTable to generate a blank table.
2. Set Up Dual Values: Drag your target metric into the Values box twice. Right-click the second item, select Value Field Settings, and change 'Show Values As' to '% of Grand Total'.
3. Insert the PivotChart: With the PivotTable selected, navigate to the Insert tab and click PivotChart to visualize the data.
4. Apply a Secondary Axis: Double-click the percentage series on the newly created chart and select 'Secondary Axis' in the right-hand formatting pane to correct the scaling.

Fully compatible with Microsoft Excel (.xlsx) file formatsIntuitive drag-and-drop PivotTable and PivotChart interfacesRich chart customization including secondary axes and combo chartsFree to use, lightweight, and fast performance

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I see the percentage series on my PivotChart?

Because percentages are stored as small decimal values (e.g., 0.50 for 50%) while raw data might be in the thousands, the percentage series often sits completely flat at the bottom of the chart. You must assign the percentage series to a 'Secondary Axis' so it scales against a 0-100% axis rather than the large numeric axis.

How do I format the chart data labels to show the percent sign instead of a decimal?

If you configure the field via 'Value Field Settings' and choose '% of Grand Total', Excel typically formats the output as a percentage automatically. If it does not, right-click the data labels on your chart, select 'Format Data Labels', scroll down to the 'Number' category in the pane, and change the format code to 'Percentage'.

Does filtering the PivotTable remove my secondary axis formatting?

Sometimes, updating, refreshing, or applying heavy filters to a PivotChart can cause custom formatting like secondary axes or custom data labels to reset to defaults. To quickly fix this, save your completed chart as a Chart Template (right-click > Save as Template) and reapply it whenever the formatting drops via the Change Chart Type menu.

Excel & Data Problems