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:
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:
2) Select the Chart and choose the Data Ribbon> Choose Select Data to Add the Data Series for the XY Line:
3) Change the XY Line Data Series to an XY Scatter with Straight Lines Chart
4) Select the XY Scatter Line and choose the Data Ribbon> Choose Select Data to Edit the Series
5) Edit the X Series and the Y Series
6) Format the Line Style to Dashed Line Style
7) Add Data Labels
8) Change Data Labels to Series Name
9) Delete Un-needed Labels and Move Label above Dashed Line
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
