Grouping Excel Column Charts with a Box

In a previous post, I showed you how to group a column chart with lines:

You can check out the posting here:

Group Column Chart with Lines for Excel Dashboard Presentations

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
x y
216 510.95
216 2174
672 2174
672 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:image


The Breakdown

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;

b) 1 for your floating box made from an XY Scatter line.image

2) Create Column Chart

Highlight your clustered column area in cells.  In this data set above from A2:B20

Then go to the Insert Ribbon and Choose a Clustered Column Chart:image

Your Excel Graph will now look like this:image

3) Add Second Series

Now Select and COPY just the “Y” data points in cells E2:E6

Then Select your chart and then from the Home Ribbon, choose PASTE SPECIAL from the Paste button:image

You will then see this Excel Dialog Box:SNAGHTML6916efa

Click on the OK button and your chart will now look like this:image

Notice the new second series in Red.

4) Change Second Series to XY Scatter Chart Type

Select the Second Series (as seen in Red above).  Then change the chart type by choosing “Scatter with Straight Lines” from the Scatter button in the Chart group of the Insert Ribbon:image

Your chart will now look like this:image

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.

Right Click on Series 2 and choose “Format Data Series…”image

Choose the Primary Axis from the Plot Series On Series OptionsSNAGHTML6996318

Your chart will now look like this:image

Notice that the Secondary Axis’s are now gone.

6) Change the Chart Data Series Range for the Second Series

Final Step, we just need to add the X’s in your XY Scatter Line Series.  To do this, select the chart, then choose the Select Data button from the Data group of the Excel Design Ribbon:image

You will then see this dialog box where you should select “Series 2” and then press on the Edit button above it:SNAGHTML69dc39f

You will then see this Excel Dialog Box.  Then Add the X Series Values of D2:D6 and then press the OK button.SNAGHTML69f1c29

You are now done as your chart will look like this:image

As you can see, we have now created a floating line that is creating a box grouping in your Excel clustered column chart.

Video Tutorial

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.