I need to create a Year-on-Year chart where values are compared month by month.
For 2018 all the numbers are Actual, but for 2019 my Projected numbers change into Actual numbers as the year progresses.
I need to show different formatting for 2019 Actual vs. 2019 Projected numbers. I know how to change the formatting manually, but I need it to be dynamic. I know how to do it dynamically for a line chart (thanks to Mynda's Dashboard course), but I have no idea how to coerce the data layout to be able to do this for column chart.
Please, see attached file with some sample data and the charts explaining my problem.
Thank you.
Blanka
Hi Blanka,
You can't automate the formatting you want for the column chart. Better to stick with the line chart or write a macro to change the formatting each month.
Mynda
So there is no way to structure the data in a way to trick Excel column chart similar way to the line chart? That's disappointing, I was really hoping there would be.
That kind of macro is way above my head.
Thank you for your response, Mynda.
Blanka
Hi Blanka
Try plotting the 2019 Projected to the secondary axis and then adjust its Gap Width.
Make sure you delete the secondary vertical axis on the right.
Good luck.
Sunny
I found a solution - it's a take on "Clustered and Stacked Column and Bar Charts" found on Peltier Tech Blog. I followed the technique described there.
I'm attaching my file with the solution itself - in case anyone needs something similar.
Clever is Mr Peltier 🙂 thanks for sharing, Blanka.
