Multi-Series Charts in Excel
December 22nd, 2006
I am increasingly fascinated with, and frustrated by, spreadsheets. Like most computer scientists, I have mostly ignored them, thinking that they were toys for non-programmers. The more I play with Excel for managing grading and other tasks, though, the more impressed I am.
But now I have a problem. I have three columns of data: the first is a date, the second is a project name, and the third is a count of lines of code. I want a time-series chart that shows the size of each project over time. The samples are irregularly spaced, and different projects are sampled at different times. Damned if I can figure out how to get Excel to do it. If anyone knows…
| 2005-11-08 | Red | 71897 |
| 2005-11-12 | Blue | 1728 |
| 2005-11-29 | Red | 73443 |
| 2005-12-01 | Green | 96101 |
| 2005-12-02 | Green | 96437 |
| 2006-01-14 | Red | 73599 |
| … | … | … |
See if this page helps out. http://www.mrexcel.com/tip032.shtml
Is what you want a chart, or would a pivot table suffice? http://www.cpearson.com/excel/pivots.htm
Thanks for the pointers — they’re a big help. I think pivot tables are part of the answer, but getting Excel to put several different time series in one chart, *without* truncating the X (time) axis so that it only shows the *shortest* of the series, is beyond me right now. I’ll try again after the holidays…
OK, I’ve messed with pivot tables, but am still unhappy. I have the project on the Y axis, the date on the X axis, and I’m getting one time-series line per project showing its size, but:
a) I can’t persuade the Pivot Chart Wizard (which is, of course, separate from the usual chart wizard) to format the X axis as dates, so my values there are evenly spaced (even though the data is not).
b) I have a button labeled “Sum of 1640″ in the upper right of the chart that I can’t get rid of (deleting it makes the whole chart go away). Uncoincidentally, the first value in my data is 1640.
c) Excel, in its wisdom, does not join up all the values associated with a particular project. Instead, it only joins up those that occur consecutively. For example, if the project entries are “red, green, green, red”, there’s a gap in the red line. This is *not* what I want.
Bother…