Group Column Chart with Lines for Excel Dashboard Presentations

Yesterday a user in the MrExcel.com Forums asked how he could make a Column Chart in Excel and then Group those Columns with a Line and Label the Line.

Here was the sample picture presented in the forum:

GroupedColumnChart

 

This type of chart in Excel is very useful in an Executive Excel Dashboard.  Perhaps you want to highlight seasonality in your chart, or point out a particular time in the data.  It has many uses in a Company Dashboard and we should figure out how to make this Excel Graph.

Below you will find a Step-by-Step Tutorial along with a Free Download and Video Tutorial on How-to Make a Combination Column and Line Grouping Chart.  Lets get started…

The Breakdown:

The easiest way to make this chart would be to create a 2D Column Chart with the data and then make it a combination chart by adding an XY Scatter Chart with Lines to draw the groupings.

Step-by-Step

1) Plot the Column Data in a Clustered Column Excel Chart:

CreateColumnChart

2) Select the Chart and choose the Data Ribbon> Choose Select Data to Add the Data Series for the XY Line:

AddXYSeriestoColumnChart

3) Change the XY Line Data Series to an XY Scatter with Straight Lines Chart

ChangeXYSeriestoXYLine

4) Select the XY Scatter Line and choose the Data Ribbon> Choose Select Data to Edit the Series

SelectDataMenu

SelectDataDialogBox

5) Edit the X Series and the Y Series

ChangeXYSeriesDataPoints

6) Format the Line Style to Dashed Line Style

ChangeXYSeriestoDashLine

7) Add Data Labels

AddDataLabelsXYSeries

8) Change Data Labels to Series Name

ChangeXYDataLabels

9) Delete Un-needed Labels and Move Label above Dashed Line

DeleteandMoveDataLabelsXYseries

10) Repeat the steps 2-9 above for any additional groupings

11) Sit back and Smile with a Dashboard Job well done!

 

Free Sample Download File

Sample Free Download Template File to Try-it Yourself:

Excel-Column-Chart-Groupings-with-Line.xlsx

 

Video Tutorial

Watch the Video How-to Tutorial to See Exactly How-To Do it:

 

UPDATE: SEE AN UPDATED POST THAT SHOWS HOW TO ADD A BOTTOM LINE TO THE GROUPING CREATING A FLOATING COLUMN BOX GROUPING:  https://www.exceldashboardtemplates.com/?p=2728

 

Please remember to sign up for my newsletter subscription and Leave me a Comment on what you think of this Video and Excel Dashboard Tutorial.

Steve=True