Easily Plotting Multiple Data Series in Excel

Question

I really need help figuring out how to speed up graphing multiple series on a graph. I have seperate devices that give monthly readings for several variables like pressure, temperature, and salinity. Each of these variables is going to be its own graph with devices being the series. My x-axis is going to be the dates that these values were taken. T

It takes ages to do this for each spreadsheet since I have monthly dates from 1950 up to the present and I have about 50 devices in each spreadsheet. I also have graphs for calculated values that are in columns next to them. Each of these devices is going to become a data series in the graph. E.g. In one of my graphs I have all the pressures from the devices and each of the data series' names is the name of the device. I want a fast way to do this. Doing this manually is taking a very long time.

Is there any easier way to do this? It is consistent and the dates all line up. I am just repeating the same clicks over and over again

Answer 1

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.

Answer 2

Are all sheets laid out the same? The same columns for each item you need to chart? If so:

1. process one worksheet with all the charts you need, all properly formatted. Save the workbook.

2. Copy this workbook for each additional sheet you have data for. Copy a new workbook's data onto this copied workbook's data. The chart keeps pointing to the same range, but you've pasted in new data, so the charts should reflect the new data.