347 647 9001+1 714 797 8196Request a Call
Call Me

Financial Modeling Tutorial: Pivot Tables and Charts

December 9, 2013
, , ,

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.


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 42150