SOLVED

How to Make a Combination Chart with Line on Secondary Axis

%3CLINGO-SUB%20id%3D%22lingo-sub-2482638%22%20slang%3D%22en-US%22%3EHow%20to%20Make%20a%20Combination%20Chart%20with%20Line%20on%20Secondary%20Axis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2482638%22%20slang%3D%22en-US%22%3E%3CP%3EI%20used%20to%20be%20able%20to%20graph%20anything%20on%20Excel%2C%20but%20it%20seems%20like%20they%20have%20taken%20away%20all%20my%20control%20and%20put%20together%20wizards%20that%20don't%20work%2C%20if%20I%20don't%20have%20things%20formatted%20just%20the%20way%20Excel%20wants.%26nbsp%3B%20I%20need%20some%20help%20understanding%20how%20to%20make%20this%20Wizard%20happy.%26nbsp%3B%20When%20I%20try%20to%20choose%20Change%20Chart%20Type%20to%20Combo%20with%20Line%20on%20Secondary%20Axis%2C%20it%20is%20greyed%20out%20and%20will%20not%20let%20me%20choose%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2482638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2482831%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Make%20a%20Combination%20Chart%20with%20Line%20on%20Secondary%20Axis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2482831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087320%22%20target%3D%22_blank%22%3E%40Neptune287%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20your%20data%20formatted%20correctly%3F%20You'll%20need%20at%20least%203%20rows%2C%20your%20x-axis%2C%20and%20values%20for%20each%20line%20like%20so%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_1-1624559509229.png%22%20style%3D%22width%3A%20607px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291019i277F01106231D02C%2Fimage-dimensions%2F607x415%3Fv%3Dv2%22%20width%3D%22607%22%20height%3D%22415%22%20role%3D%22button%22%20title%3D%22DKoontz_1-1624559509229.png%22%20alt%3D%22DKoontz_1-1624559509229.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20there%2C%20all%20you%20have%20to%20do%20is%20click%20insert%20chart%20%26gt%3B%20Combo%20%26gt%3B%20and%20click%20the%20Secondary%20axis%20button%20on%20whichever%20data%20set%20you%20want%20on%20that%20axis%20and%20change%20both%20chart%20types%20to%20line.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2482881%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Make%20a%20Combination%20Chart%20with%20Line%20on%20Secondary%20Axis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2482881%22%20slang%3D%22en-US%22%3EYeah%2C%20obviously%20not%20as%20I%20have%203%20columns%20of%20data%20but%20Combo%20chart%20is%20grayed%20out%20and%20will%20not%20let%20me%20select.%20That's%20what's%20frustrating%20about%20the%20online%20help%2C%20it%20starts%20like%20you%20know%20what%20you%20are%20doing%20and%20then%20says%2C%20%22then%20push%20the%20button%2C%20voila%22.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20tell%20me%20step%20by%20step%20like%20I%20haven't%20been%20making%20charts%20for%2028%20years%3F%20Cuz%20this%20is%20hard%20to%20swallow%20that%20I%20suddenly%20can't%20make%20a%20chart.%20Thanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2482980%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Make%20a%20Combination%20Chart%20with%20Line%20on%20Secondary%20Axis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2482980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1087320%22%20target%3D%22_blank%22%3E%40Neptune287%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%203%20columns%20might%20be%20the%20problem%2C%20try%20switching%20the%20axis's%20by%20selecting%20you%20data%20%26gt%3B%20pressing%20insert%20chart%20(type%20doesn't%20matter%20now).%20I%20just%20did%20a%20line%20graph.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_1-1624561363216.png%22%20style%3D%22width%3A%20455px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291037iAF9DB2142CA3915B%2Fimage-dimensions%2F455x125%3Fv%3Dv2%22%20width%3D%22455%22%20height%3D%22125%22%20role%3D%22button%22%20title%3D%22DKoontz_1-1624561363216.png%22%20alt%3D%22DKoontz_1-1624561363216.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20right%20click%20the%20graph%20%26gt%3B%20select%20data%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_2-1624561414580.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291038i65EE5889DDC06018%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DKoontz_2-1624561414580.png%22%20alt%3D%22DKoontz_2-1624561414580.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20in%20the%20box%20that%20pops%20up%2C%20press%20switch%20row%2Fcolumn.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_3-1624561442760.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291039i515918F499FB3A8F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DKoontz_3-1624561442760.png%22%20alt%3D%22DKoontz_3-1624561442760.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20that%20fixes%20it.%20Excel%20is%20really%20good%20about%20creating%20graphs%20for%20you%2C%20there%20isn't%20much%20manual%20work%20required%20outside%20of%20getting%20the%20data%20formatted%20in%20a%20way%20that%20it%20reads%20it%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2483048%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Make%20a%20Combination%20Chart%20with%20Line%20on%20Secondary%20Axis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2483048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F993615%22%20target%3D%22_blank%22%3E%40DKoontz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Neptune287_0-1624562627854.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291046i98BD9F325F11299D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Neptune287_0-1624562627854.png%22%20alt%3D%22Neptune287_0-1624562627854.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I used to be able to graph anything on Excel, but it seems like they have taken away all my control and put together wizards that don't work, if I don't have things formatted just the way Excel wants.  I need some help understanding how to make this Wizard happy.  When I try to choose Change Chart Type to Combo with Line on Secondary Axis, it is greyed out and will not let me choose it.

9 Replies

@Neptune287 

 

Is your data formatted correctly? You'll need at least 3 rows, your x-axis, and values for each line like so:

DKoontz_1-1624559509229.png

 

 

From there, all you have to do is click insert chart > Combo > and click the Secondary axis button on whichever data set you want on that axis and change both chart types to line.

 

Yeah, obviously not as I have 3 columns of data but Combo chart is grayed out and will not let me select. That's what's frustrating about the online help, it starts like you know what you are doing and then says, "then push the button, voila".

Can you tell me step by step like I haven't been making charts for 28 years? Cuz this is hard to swallow that I suddenly can't make a chart. Thanks.

@Neptune287 

Having 3 columns might be the problem, try switching the axis's by selecting you data > pressing insert chart (type doesn't matter now). I just did a line graph. 

DKoontz_1-1624561363216.png

Then right click the graph > select data

DKoontz_2-1624561414580.png

And in the box that pops up, press switch row/column.

DKoontz_3-1624561442760.png

 

Let me know if that fixes it. Excel is really good about creating graphs for you, there isn't much manual work required outside of getting the data formatted in a way that it reads it correctly.

@Neptune287 

It helps seeing your data, I recreated a small piece of your data and ended up with something that looks like this:

DKoontz_1-1624563588651.png

 

With chart type set up like this:

DKoontz_2-1624563618131.png

 

To summarize, I selected the data, inserted chart (doesnt matter what type), changed chart type to combo, made both series into lines, and clicked secondary axis for the %.

 

 

@DKoontz 

It seems to be hopeless.  I cannot get my graph to look like I want.

 

This is closer to what I need it to look like.  Number of tasks done on Y axis vs. Dates on X axis.  The actual progress in bar form and a line showing the targeted progress from start to the finish of the project..

Neptune287_0-1624567959641.png

 

If you post your workbook I can see if I can get it to where you'll be happy! If there is sensitive info just change or delete if not necessary.

@DKoontz 

 

Thanks for your help.  I feel like I am so close and yet so far.  I am trying to get a sheet where I can show progress over the next few months.  I would like to be showing the actual count on one axis and percent complete on the secondary of harvesting the data off of 1700 drawings.   Every couple weeks I am going to enter the current progress and those will be bars, and the theoretical steady progress line will be there to show we are on pace or not.  

best response confirmed by Neptune287 (Occasional Contributor)
Solution

@Neptune287 

Ok great! So it looks like the problem was you had duplicate dates, and excel freaks out when there are multiple x axis and y axis coordinates. I slimmed your data down to just one date and one projected/actual count, and charted those.

 

You can change the numbers and dates as needed but this should give you a good template to work off of.

 

Let me know if this helps.

 

DKoontz_0-1624637132485.png