Quickly creating a x-y scatter chart with straight lines and markers

%3CLINGO-SUB%20id%3D%22lingo-sub-2787469%22%20slang%3D%22en-US%22%3EQuickly%20creating%20a%20x-y%20scatter%20chart%20with%20straight%20lines%20and%20markers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2787469%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20number%20of%20data%20series%20with%20annual%20data%20returned.%20I%20would%20like%20to%20quickly%20create%20an%20X-Y%20scatter%20chart%20with%20lines%20and%20markers%20with%20one%20line%20for%20each%20series%2C%20with%20values%20returned%20on%20the%20y-axis%20and%20year%20of%20data%20entry%20on%20the%20x-axis.%20I%20currently%20have%20a%20table%20of%20data%20with%20%22year%22%20along%20the%20top%2C%20%22series%20label%22%20down%20the%20side%20and%20data%20within%20the%20table.%20I%20could%20manually%20create%20a%20blank%20chart%20and%20add%20the%20series%20one%20by%20one%20but%20this%20would%20take%20a%20long%20time.%20I%20have%20tried%20marking%20all%20the%20data%20and%20creating%20a%20chart%20but%20the%20software%20does%20not%20seem%20to%20recognise%20what%20I%20am%20trying%20to%20do.%20Is%20there%20a%20way%20to%20create%20this%20chart%20quickly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2787469%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-2787635%22%20slang%3D%22en-US%22%3ERe%3A%20Quickly%20creating%20a%20x-y%20scatter%20chart%20with%20straight%20lines%20and%20markers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2787635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1167418%22%20target%3D%22_blank%22%3E%40Anonymous655%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20range.%3C%2FP%3E%0A%3CP%3EInsert%20a%20scatter%20chart%20with%20lines%20and%20markers.%3C%2FP%3E%0A%3CP%3EIf%20it%20looks%20wrong%2C%20click%20anywhere%20in%20the%20chart.%3C%2FP%3E%0A%3CP%3EOn%20the%20Chart%20Design%20tab%20of%20the%20ribbon%2C%20click%20Switch%20Row%2FColumn.%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example.%20First%2C%20the%20scatter%20chart%20as%20created%20by%20Excel%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0785.png%22%20style%3D%22width%3A%20488px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313046iD679DEFDCC0DFAEC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0785.png%22%20alt%3D%22S0785.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENext%2C%20the%20result%20of%20clicking%20Switch%20Row%2FColumn%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0786.png%22%20style%3D%22width%3A%20488px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313047i9CF1A1B8F127F370%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0786.png%22%20alt%3D%22S0786.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20needs%20a%20bit%20more%20work%20-%20we%20don't%20want%202017.5%20etc.%2C%20so%20we%20format%20the%20X-axis%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0787.png%22%20style%3D%22width%3A%20488px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313054iD761BFD1B1E870FC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0787.png%22%20alt%3D%22S0787.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a large number of data series with annual data returned. I would like to quickly create an X-Y scatter chart with lines and markers with one line for each series, with values returned on the y-axis and year of data entry on the x-axis. I currently have a table of data with "year" along the top, "series label" down the side and data within the table. I could manually create a blank chart and add the series one by one but this would take a long time. I have tried marking all the data and creating a chart but the software does not seem to recognise what I am trying to do. Is there a way to create this chart quickly?

2 Replies

@Anonymous655 

Select the range.

Insert a scatter chart with lines and markers.

If it looks wrong, click anywhere in the chart.

On the Chart Design tab of the ribbon, click Switch Row/Column.

Here is an example. First, the scatter chart as created by Excel:

S0785.png

Next, the result of clicking Switch Row/Column:

S0786.png

It needs a bit more work - we don't want 2017.5 etc., so we format the X-axis:

S0787.png

 

thank you - great response.