Dynamic Charts with Index & Match Function

Have you ever created a dynamic chart in excel? If yes, Kudos to you!  If not don't worry today you can master that skill in couple of minutes.  I would like to tell you that this is my first post on charts and I am starting a series on charting with this.  I will keep adding more to it time and again for sure.  In this post I'll be talking about how to create dynamic column chart using few amazing but simple functions of excel.  INDEX, MATCH & VALIDATION are three things I am using to create a dynamic chart which will look like the below example.

You can download the example from here! DYNAMIC CHART FILE

I am assuming that you have already downloaded the file from above.  In it you can see my main data is in range A1:E7, but using nested INDEX & MATCH functions I have created a new data for my chart to take in range A10:B16.

The challenge here is to change the data in range A10:B16.  If you see, you will notice that in cell H1 I have used data validation and it is giving me year for which I want my chart to plot the data. The data range A10:A16 has the name of car manufactures.  Using these two inputs I am pulling out data from main data.  The combination of functions I have used is as follows:

B11=INDEX($B$2:$E$7,MATCH($A11,$A$2:$A$7,0),MATCH($H$1,$B$1:$E$1,0))

Now I have dragged this function till B16 and I have my dynamic data ready with me.  It changes as I change the year in Cell H1.  Using this data series I have now created a chart.  To do it the steps are as follows:

Select your data > Insert > Charts > Column > 2-D Column > Clustered Column

You can now make adjustments as per your presentation requirement and give it a look as you want.  I have removed data labels from this chart and changed the color of each bar as well.  Just keep one thing in mind if you have the nested function mentioned above, rest of the work is simple.  You can use these charts in sales dashboards and many other places.  I use them it to show the performance of my team on weekly basis.

Dear reader, please let me know any feedback you have for me.  You can also give suggestions and topics that you want me to cover in upcoming posts.  Till then "HAPPY LEARNING!"