I do a lot of original work here and elsewhere but every now and again I have to give credit where it is due. Here is such a case.
Over at chandoo.org there is a talented Excel man who does things that are well worth looking at and today I read his page on Then v Now Charts. The description Chandoo gives of how to create these charts is not complete so if you are a beginner, you will need help. In other words, the page has been aimed at pre intermediate users. I was OK then. Here is the URL of the page you want: http://chandoo.org/wp/2013/08/06/how-to-create-a-then-vs-now-interactive-chart-in-excel/
Chandoo does give away the Excel file that he created for his demonstration although I have to say I haven’t opened it! They are usually good.
This is my version of what Then v Now Charts are; and forgive my colour scheme as I have no graphic talent!
Firstly, the table of data that Chandoo provides … nothing unusual there
And now the chart:
It’s apparently an XY chart AND a Bar chart combined. The chart uses a VLOOKUP() formula, an INDEX() formula, named ranges, a combobox control and the chart title is dynamic … just look at this second image of the same chart with a new region having been selected:
See … new title, new entry in the combobox, new values in the bar chart area!
Give it a go