How to Add Secondary Axis in Excel Pivot Chart (with Easy Steps)

Using a Pivot Chart is one of the best ways to present your data in Excel. While working in Excel you might find it difficult to add a Secondary Axis to your PivotChart. But after going through this article, it’ll be straight as water. Here, we will take you through 6 easy and quick steps on how to add Secondary Axis in Excel Pivot Chart.


Download Practice Workbook

You may download the following Excel workbook for a better understanding and to practice yourself.


A Visual Illustration: Secondary Axis in Excel Pivot Chart

Let’s illuminate the context with a relatable example. Suppose you have a PivotTable in your hand. It includes the Sum of Sales and the Sum of Profit.

how to add secondary axis in excel pivot chart

Using the above table, you can create a PivotChart depending on your requirements.

Here, the blue bars represent the Sales. But the orange bars aren’t visible as the value of Profit is so small compared to Sales. Here comes the essentiality of the Secondary Axis. If you would have added a new Secondary Axis for the Sum of Profit, then this problem would not exist. Let’s see the image below.

Practical Reason to Add Secondary Axis in Excel Pivot Chart

Here, we’ve changed the vertical axis of the Sum of Profit. Now, we can easily interpret the chart and get a sense of the profit percentage for each product.


6 Steps to Add Secondary Axis in Excel Pivot Chart

In this article, we’ll show you how to create a PivotTable first. Then, we’ll insert a PivotChart based on this table. So, let’s explore the method step by step.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.


Step 01: Prepare Dataset

At the very beginning, we will talk about the dataset. Here, we are using a monthly Sales Report of a particular bakery. This dataset includes the Product name, the corresponding Sales amount, and the analogous Profit percentages in columns B, C, and D respectively.

Prepare Dataset

Note: Make sure you format the Sales column in Currency format. On the other hand, apply the Percentage formatting in the Profit column.


Step 02: Insert a Blank Pivot Table

Initially, we need to select the specific range of data from which we want to create our PivotTable. It’s simple and easy. Just follow along.

📌 Steps:

  • At the very beginning, select any cell inside the dataset. In this case, we selected cell B4 in our dataset.
  • Then, go to the Insert tab.
  • Later, click on PivotTable in the Tables group.

Insert a Blank Pivot Table

  • Immediately, the PivotTable from table or range dialog box appears.
  • Here, we can see that our data range was automatically detected and sat in the Table/Range box.
  • In the Choose where you want the PivotTable to be placed section, select New Worksheet. This will place our PivotTable in a new worksheet.
  • After doing that, click OK.

As a result of our previous actions, Excel created a blank PivotTable in a new worksheet.

Blank Pivot Table in Sheet

  • Now, select any cell on the PivotTable. For example, we chose cell B4.
  • Immediately, the PivotTable Fields task pane opens on the right side of the worksheet.

Bringing up the PivotTable Fields Task Pane

PivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the upper part’s field names as per your necessity. In our example, the upper part of the PivotTable Fields task pane holds the Product, Sales, and Profit. The lower part has Filters, Columns, Rows, and Values area.

Read More: How to Hide Secondary Axis in Excel Without Losing Data


Step 03: Lay out the Pivot Table

Now we shall work on the PivotTable Fields task pane. The following steps will create a simple PivotTable. So, follow along.

📌 Steps:

  • At first, drag the Product field into the Rows area.
  • Then, drag the Sales and Customer fields into the Values area.

Lay out the Pivot Table

And the final output looks like the following one.

Lay out the Pivot Table to add the Secondary Axis to Pivot Chart

  • At this moment, right-click on the Sum of Profit heading.
  • From the context menu, select the Value Field Settings option.

Opening Value Field Settings Option

  • Instantly, the Value Field Settings dialog box pops up.
  • Here, click on the Number Format.

Utilizing Value Field Settings Option

  • Suddenly, the Format Cells Wizard opens.
  • In the Category section, select Percentage format.
  • Then, change the Decimal places to 0.
  • Lastly, click OK.

Using Format Cells Option

  • Simply, it returns us to the Value Field Settings wizard.
  • Consequently, click OK.

  • Currently, go to the Design tab.
  • After that, click on the Layout group drop-down.
  • Then, select the Grand Totals drop-down option.
  • Correspondingly, choose the Off for Rows and Columns.

After all the effort, our PivotTable looks like the one below.

Lay out the Pivot Table to Add Secondary Axis in Excel Pivot Chart

Read More: How to Show Grand Total with Secondary Axis in Pivot Chart


Step 04: Insert Pivot Chart

Now, we’re able to insert a PivotChart in our worksheet. Let’s see the process in detail.

📌 Steps:

  • Initially, select cell B4 to open up the Tool.
  • Then, jump to the PivotTable Analyze tab.
  • Following this, click on the Tools drop-down group.
  • Later, select PivotChart from the available options.

Insert Pivot Chart

Thenceforth, the Insert Chart prompt appears.

  • Here, click on the Column chart.
  • Then, choose Clustered Column from the choices.
  • As always, click OK.

Forthwith, we can see a 2-D Clustered Column chart in our worksheet.

Insert Pivot Chart

Read More: How to Create a Combination Chart in Excel (4 Effective Examples)


Step 05: Utilize Format Data Series Pane

In the image above, we cannot visualize the column of the Sum of Profit though they are present in the chart. The reason is the very tiny amount of them. So, we’ll try to ascent from this dilemma. So, without further delay, let’s dive in!

📌 Steps:

  • Primarily, right-click on the column of Sum of Sales.
  • From the context menu, select Format Data Series.

Utilize Format Data Series Pane

Right away, the Format Data Series task pane appears on the right side of the worksheet.

  • Here, click on the Chart Elements drop-down beside the Series Options text.
  • Therefore, select Series “Sum of Profit” from the options.

  • Afterward, select Secondary Axis from the Plot Series On section.

Contemporarily, the chart looks like the screenshot below.

Utilize Format Data Series Pane to Add Secondary Axis in Excel Pivot Chart

Clearly, we can see a new Secondary Axis for the Sum of Profit has been added on the right side of the chart.


Step 06: Change Series Chart Type

But, now we cannot distinguish between the two different columns. Because the orange one covers the blue one. So, we’ll change the type of chart now. Allow me to demonstrate the process below.

📌 Steps:

  • Similarly, right-click on the Sum of Profit series on the chart.
  • Then, from the context menu, select Change Series Chart Type.

Change Series Chart Type to Add Secondary Axis in Excel Pivot Chart

Thus, the Change Chart Type wizard opens.

  • Then, click on the Chart Type drop-down of the Sum of Profit Series.
  • Hence, select the Line chart from the options.
  • As usual, click OK.

Magically, our PivotChart gets changed into an explainable one.

Change Series Chart Type to Add Secondary Axis in Excel Pivot Chart

At this time, the Sum of Profit series is displayed by lines. So, we can easily portray the contents of the chart in our minds.


A Possible Solution If Secondary Axis Disappears from Excel Pivot Chart

Sometimes, we see the Secondary Axis gets disappeared from the chart. Here, we’ll show how it gets done.

📌 Steps:

  • Firstly, click on the down arrowhead icon beside the Row Labels heading.
  • Secondly, deselect the option Select All and select Bagel only.
  • Subsequently, click OK.

Now, the chart is showing the illustration just for the Product named Bagel. Here, we cannot see the Secondary Axis in our chart. So, we can say that it gets disappeared from the chart for this instance.

Pivot Chart Secondary Axis Disappears in Excel

Again, if we clear the Filter, the axis will be visible.


Conclusion

This article provides easy and brief solutions to add a Secondary Axis in Excel PivotChart. Don’t forget to download the Practice file. Thank you for reading this article and we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo