In Excel, how do I chart multiple series when the series is in the first column

ChrisHDog_25719 0
Viewed 906

I have an excel spreadsheet with three columns (component, data, count) where I'd like to chart (line chart, stacked area, etc...) that data such that the component column is the series, and the date and count columns are the X, Y values (like a date and a count).

|component| date |count|

| A | 2017-01-01 | 10 |

| B | 2017-01-01 | 12 |

| A | 2017-03-01 | 15 |

| B | 2017-03-01 | 10 |

I know that I can re-arrange the data so that the component and the date are across the top/side and the values in the middle and that charts pretty naturally, but I keep getting data in this "first column is the series" and it would be great to be able to chart that without a big data conversion process.

| |2017-01-01|2017-03-01|

| A | 10 | 15 |

| B | 12 | 10 |

Basically I'm looking for the method to chart data provided like the top table as if it were formatted like the bottom table without having to manipulate the data (particularly when there is a lot of data to manipulate).

charts excel
1 Answers

Your data is in good database format, so this is the perfect time to use a PivotChart:

  1. Select your data
  2. Go to the Insert tab of the ribbon, click on the Dropdown arrow on PivotTable button and choose PivotChart
  3. On the field list on the right, drag Component to Legend (Series)
  4. Then drag Date to Axis Fields (Categories)
  5. Then drag Count to Values
  6. You'll get a bar chart by default. Click on the background of the chart to select the entire chart. Then on the PivotChart Tools Ribbon, go to the Design Tab, click Change Chart Type and choose Line.

enter image description here

user_74445070
Related