Excel Comparison Chart With One Vertical Axis
A comparison chart is useful for comparing series with each other while comparing them with their targets simultaneously. In this post, I will show you a quick way for creating such chart, namely, Excel Comparison Chart With One Vertical Axis.
Previously I posted a comparison chart on this website. You can check it from here. It does the exact same thing with this one but it uses two vertical axis. This makes things a bit complicated. There is also the risk of two axes not having same min and max values.
This time we are going to use just one vertical axis. This will make creating the chart easier and help us avoid axis value mismatch.
This will be our data for this chart.
When you select this data and insert a column chart, excel will create a default chart like the one below:
Double click on one of the blue columns to open format data series side menu. There, set parameters as shown below:
Series overlap %100 will position actual columns on top of budget columns and gap width %50 will decrease the gap between columns to the half of column width. Your chart is supposed to look like this:
Now it is time to format our columns. We will use this trick to format our chart. Insert a rectangle into any empty cell on your workbook.
Then insert a line, 2-3 times longer than the base of your rectangle. Position the line at the base of the rectangle like shown below.
Now select both shapes together by holding down Ctrl key and clicking on them. And group them as one shape like shown below. Now they will behave like a single shape.
I changed color of my shape to orange to match the color of actual series. Now copy your shape, click on any actual column and paste with Ctrl+V. Actual columns will be thinner than budget columns and your chart will look like this:
Now we can make the finishing touches line formatting, adding chart title, etc. to finalize our chart.
This is how we can do a Excel Comparison Chart with one vertical axis.