Take the 2-minute tour ×

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

share|improve this question
If you don't have special reasons for using a spreadsheet, you might consider feeding CSV data to GnuPlot or R - which you might find easier to automate (or not) –  RedGrittyBrick Jun 25 '12 at 16:18
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


