Excel figure help

[cesco]

Dear all,
I have a simple and yet complicated question. I have a bar figure with three years for 13 countries. Here it is:

for each of the countries, and for each of the years, I also have another data (growth rate, if you are interested), that I'd like to plot as a point. So, take for example Italy for 2011, I have the blue bar indicating the variable that you see, and I'd like to have a dot indicating the growth rate in the same year, aligned on that bar. Is there a way to do that? Or should I just give up?
Thanks!
Francesco

 

[RoryA]

It's hard to be specific from that, but you can certainly add new series to the chart and plot them as a different type (e.g. a line chart or XY scatter) and use an additional Y axis if ncessary.

 

[Dave Paradi]

One option is to use invisible lines to position the extra data labels using the feature to add labels from other cells. Here is an example for a single set of columns.

Columns C and D are data series in the graph that are line graphs and have their line color set to None so they are invisible. I used two lines instead of one so that the labels for columns that are above the axis can be positioned above the column and the labels for columns below the axis can be positioned below the column.

Column E has the additional info that needs to be displayed and it may not relate at all to the values in column B.

Columns F and G are the labels to be used as data labels for the data series in columns C and D respectively. I added the word "growth" to the custom label so that it doesn't get misinterpreted as labels for the column value. The feature for using other cells as data labels was introduced in Excel 2013.

I also moved the horizontal axis labels to the Low position so they don't get run over by the columns.

You can learn more about creating custom labels and applying them using this feature in my article & video at Excel bar chart with conditional formatting based on MoM change %.

Hope that helps.