Excel – Splitting data into multiple series’ in Excel Graph

Assuming you're using Excel 2010 (and probably 2007), the following should help:

1. Put your data into a format Excel can easily recognize, e.g. Column A=Date, Column B=First Data Series, Column C=Second Data Series...
2. Highlight your first group of data series. This should include your date column (which Excel will automatically fill as your X-axis) and as many series as you want in that chart.
3. Select Insert>Chart (of appropriate type).
4. Format the automatic Chart to your needs (axis, labels, titles, etc...)
5. Save the chart as a template (with Chart selected, click Design>Save As Template.
6. Now that you have a properly formated template, you can select your next group of data series and apply the custom chart type to that.
7. Repeat as necessary.

You can really speed up modifying charts by using named ranges for your data, instead of direct cell references (plus it makes them much easier to read). Also, if you select a chart series, it's formula will display in Excel's formula bar. There you can directly enter new information to modify the series without having to open the Select Data dialog box everytime. That is especially handy with named ranges.

This is pretty easy to do (without VBA), but you'll need a helper row to get it done.

1. Organize your data into an Excel Table (this just makes referencing your data a bit easier).
2. Create one additional helper row (usually, I do this in the first row, above the data table).
3. In the helper row, first column, use Data Validation > List to select the name of each series. This will create a drop-down for you to quickly select any one of your 48 data series.
4. In the helper row, second-fourth columns, use =VLOOKUP to get the three data points from your chosen row.
5. Create a Chart and add each of the 48 data series (I'll usually format to a light gray, so they're visible, but not overemphasized).
6. Add your helper row data as a 49th data series, and format this to highlight.

Now, as you change your selection in the data validation drop down, it will automatically update the chart by highlighting your series of interest.

Note: It doesn't show in this screen grab, but the row above the chart's title row is the helper row. The first column (above name), is a data validation cell, and when selected, activates a drop-down window with all the values from the Name column in the table below.