Create a Line chart with a table of data

**JustGoCW** · 07-25-2018, 05:07 AM

Hello all,

I have a problem creating a line chart in Excel and was wondering if there's a better solution.

My table contains a row A1 which is a list of dates across to S1

There are a list of travel destinations from A2 down to A97

In all the other fields, B2 down and across to S97, are figures that show Return of Investment.

My problem is that when I try and make this a line chart with 97 series, I have to add these manually.

What I'm trying to do is have each destination with its corresponding ROI figure plotted on the chart. The chart's X axis should be the Tour Date, the Y axis should be the ROI figure. This way, I can see which destinations are performing or dropping.

I can get this in Excel's line chart, but it requires adding each Legend Entry (Series) manually - I have to click select data, click add, click the name field then click the data range.

It seems like Excel is really struggling to produce a chart based on the data I have as it's formatted in a grid, instead of in a couple columns.

Thank you!
- Chris

**Kaper** · 07-25-2018, 06:39 AM

The issue is caused by the number of columns 18 smaller than rows (96) of data. So you probably tried to select all A1:S97 range and from Insert ribbon use Line Chart. then it ends with 18 lines for each date with cities on X axis.
Once you have it, right click on a chart, from popup meny use Select Data (or similar - I do not have english version of Excel handy today) and then click Switch rows/columns button.
Now you shall get 96 series and X-axis showing dates.