How to add both percentage and number in the pivot chart?

Hello I_291,

I'm Shalom and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

To show both percentages and numbers inside an Excel pivot chart, you can follow these steps:

Add the field to the Values area of the pivot table twice. For the first instance of the field, leave it as is (this will display the numbers). For the second instance of the field, click on the drop-down arrow, go to Value Field Settings, and select Show Values As > % of Grand Total (this will display the percentages). This way, your pivot table will show both the numbers and their corresponding percentages.

If you want to display these values in a chart:

Create a chart based on your pivot table. Click on the chart to select it. Go to the Chart Tools > Design tab on the Ribbon. Click Add Chart Element > Data Labels > Center. This will add data labels to your chart that show both numbers and percentages.

Please let us know if you need more help.

Best Regards, Shalom

Thanks Shalom.

I tried but there will be 2 'columns' in the chart, one for percentage and one for number.

Is that possible to combine them into one 'column'?

To combine both percentage and number values into a single column in a PivotChart, you can use a secondary axis. Here are the steps to achieve this:

1. Create Your PivotTable:

Follow the initial steps to create your PivotTable with both the percentage and number fields.

2. Create Your Pivot Chart:

a. Select any cell within your PivotTable. b. Go to the "Insert" tab, click on "PivotChart," and select the chart type you prefer.

3. Add Data to the Pivot Chart:

a. You'll initially see two separate series in your chart, one for percentage and one for numbers. We'll now combine them into one column.

4. Move One Series to the Secondary Axis:

a. Right-click on one of the series in the chart, and select "Format Data Series." b. In the Format Data Series pane that appears, go to the "Series Options" tab. c. Check the box that says "Secondary Axis."

5. Format the Chart:

a. After moving one series to the secondary axis, you'll notice that one of the series is now on the primary axis (the left side of the chart), and the other is on the secondary axis (the right side of the chart). b. You can format the chart further by right-clicking on various chart elements (e.g., data labels, axes, titles) and formatting them as needed.

By following these steps, you will have both percentage and number values displayed in a single column chart with one series on the primary axis and the other on the secondary axis. This approach allows you to combine the two data sets into a single column in the chart while preserving the distinct data types.

Best Regards, Shalom

Thank you for your detailed explanation, it works!

However, if I connect the pivot chart to the slicer, when I am doing some filtering, the setting I set for the secondary axis will change back to the primary axis, is there a way to fix this?

One alternative approach to maintaining the secondary axis settings is to use a pivot chart template. Here's how you can create and apply a chart template to help maintain the formatting and secondary axis settings:

1. Create a Chart Template:

a. Open your PivotChart.

b. Format the chart, including setting up the secondary axis as desired.

c. Right-click on the chart, and select "Save as Template."

d. Give your template a name and save it. This template will store your formatting settings.

2. Apply the Chart Template:
3. After filtering data using the slicer (which may reset the formatting), you can reapply your chart template to restore the formatting and secondary axis settings.
4. Right-click on your chart.
5. Choose "Change Chart Type."
6. In the "Change Chart Type" dialog, go to the "Templates" folder on the left.
7. Select your saved chart template and click "OK."

This process will reapply your saved chart template to the current chart, restoring the secondary axis and other formatting settings you had applied. While this method doesn't prevent the resetting of formatting due to slicer interaction, it provides a quicker way to restore the desired formatting.

Best Regards, Shalom