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.
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.
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.
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.
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.
- 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.
- 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.
- 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.
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.
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.
- At first, drag the Product field into the Rows area.
- Then, drag the Sales and Customer fields into the Values area.
And the final output looks like the following one.
- At this moment, right-click on the Sum of Profit heading.
- From the context menu, select the Value Field Settings option.
- Instantly, the Value Field Settings dialog box pops up.
- Here, click on the Number Format.
- Suddenly, the Format Cells Wizard opens.
- In the Category section, select Percentage format.
- Then, change the Decimal places to 0.
- Lastly, click OK.
- 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.
Step 04: Insert Pivot Chart
Now, we’re able to insert a PivotChart in our worksheet. Let’s see the process in detail.
- 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.
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.
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!
- Primarily, right-click on the column of Sum of Sales.
- From the context menu, select Format Data Series.
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.
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.
- Similarly, right-click on the Sum of Profit series on the chart.
- Then, from the context menu, select Change Series Chart Type.
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.
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.
- 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.
Again, if we clear the Filter, the axis will be visible.
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.