# How to create a XY (scatter) chart with three columns of data?

I have the following data:

``````Date      Name            Value

1/1/2011  FOO             10
1/1/2011  BAR             12
2/1/2011  FOO             16
3/1/2011  BAR             21
5/1/2011  FOO             19
4/1/2011  BAR             23
``````

Note that 'Dates' are not ordered. Note also that I have two different values in 'Name'.

I would like a chart like a XY (scatter) chart that shows 2 lines, one for FOO, one for BAR.

The chart must have a X-axis representing dates (correctly spaced relatively to each other value)

The chart must have a Y-axis representing values (correctly spaced relatively to each other value)

Maybe a line chart would do the trick? Please, if possible, avoid any new computation of values, or restructuring the table data. It seems easy for a tool like Excel to create such a chart without helping it by predoing some work on the data...

Please, if possible, avoid any new computation of values, or restructuring the table data

Placing such a constraint on the solution will likely only make the solution more complex, if its possible at all.

For what its worth, I would add two additional columns and chart those as a scatter chart:

``````       A           B       C       D                       E
1      Date        Name    Value   FOO                     BAR
2
3      1/1/2011    FOO     10      =IF(\$B3=D\$1,\$C3,NA())   =IF(\$B3=E\$1,\$C3,NA())
4      1/1/2011    BAR     12      =IF(\$B4=D\$1,\$C4,NA())   =IF(\$B4=E\$1,\$C4,NA())
``````

etc

PS please include version of Excel in your question (as a tag) as it can affect the answer

• Thanks for this answer. But what if I don't know in advance the different Names (FOO, BAR) ? Maybe I need some VBA code to create those additional columns... – TheExcelNoob Sep 7 '11 at 10:01
• VBA would probably be the way to go if the number of columns can vary. If its fixed at two, then formulas can be used to get those two values – chris neilsen Sep 7 '11 at 10:26
• If you don't know FOO, BAR etc in advance make a pivot table with dates and name as axes – user151019 Sep 7 '11 at 11:59