In a previous post, I showed you how to group a column chart with lines:
You can check out the posting here:
Then I received a question from Eva P. from my Exceldashboard YouTube channel:
EVA P. writes:
this helped however need something additional!
my group starts above the x axis so I basically want to create a box…. so I can depict a range… how can I get to the chart to make the bottom line of the box? (a line going from e.g.point 672, 510.95 to point 216, 510.95
So she wants to see how to do this same line grouping technique of a clustered column chart in Excel by adding a bottom line to the grouping. The bottom line would effectively create a floating box. Kinda like this:
All we need to do is add one last data point equal to your first (bottom left) data point so that the line continues and closes back to your first point.
1) Set Up the Data
First you need to set up 2 series:
a) 1 for your clustered column chart;
2) Create Column Chart
Highlight your clustered column area in cells. In this data set above from A2:B20
3) Add Second Series
Now Select and COPY just the “Y” data points in cells E2:E6
Notice the new second series in Red.
4) Change Second Series to XY Scatter Chart Type
5) Move Second Series to Primary Axis
We do not need to have this series on the Secondary Axis. Actually, if we don’t move it, it will just cause us problems since we will have to make the Secondary Axis match the Primary Axis. However, if the data changes, we will have to remember to change the Secondary Axis every time we change. So lets just move it back to the Primary Axis.
Notice that the Secondary Axis’s are now gone.
6) Change the Chart Data Series Range for the Second Series
You will then see this Excel Dialog Box. Then Add the X Series Values of D2:D6 and then press the OK button.
As you can see, we have now created a floating line that is creating a box grouping in your Excel clustered column chart.
Please make sure you leave me a comment and let me know what you think of this technique and if you can see using it in your next Excel Dashboard.