Share with your network!

Pivot Table and Chart in Excel

Dual axis charts have been highly efficient in data presentation. It is a superb way to deal which with the data that can be categorized. Continuing my effort to provide you with the MS-Excel tutorials, today I have prepared my tutorial on preparation of a dual axis chart using pivot table. Here’s my data table for this tutorial.

Data Table for Creating Dual Axis Chart

The reason for repetition is just to ensure that we obtain the required dual axis. Now select data — insert — pivot table.

How to use Pivot Table

The table will be inserted in a new sheet by default. The PivotTable field list will appear on the right side of your window. Put main category under Report Filter. Put Category and sub-category under row labels and put the no of students place in the value section.

Deciding Fields in Pivot Table

The pivot table is obtained in the sheet as shown in left side of the image above.

Next, go to insert and insert the column chart.

How to insert Chart

There was a small hurdle for me as whenever I had to use the chart for different categories; I had to edit the chart tile every time. At that instance, I hoped for a tool that can make the chart title change automatically as the selected category is changed. And it seems that MS-Excel has remedy for all my problems. The point to note is here that the graph title can be made dynamic which means that the graph title changes every time the category of the plot is changed.

To make the graph title dynamic, click on the title. In the formula bar, type = and select the cell whose content you wish to have as the heading.

How to make the Graph Title Dynamic

Here, I have made B1 the selected cell.

Now, the graph will look like these for different categories.

Graph for Different Categories-1

Graph for Different Categories-2

Graph for Different Categories-3

Graph for Different Categories-4

Thus, plotting a dual axis graph and simultaneously making the title dynamic has been eased out.