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

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

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





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





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





Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here