Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Microsoft Excel Question

  1. Jul 12, 2005 #1
    Maybe not the best place to ask the question, but being an engineer, I figured that I might be able to pick someone elses brain about this matter.

    I have one column with a Time value in hh:mm:ss.
    There are multiple other columns that I would like to graph as well...up to 15.

    Rather than make a graph for each one of the columns, is there some sort of setting or program that I could use the do this automatically? All of the graph types are the same.

    Someone has already suggested using Matlab to do this...I am not familiar with using Matlab just yet, so if someone has a suggestion, I would appreciate it.

  2. jcsd
  3. Jul 12, 2005 #2
    Hi scott,

    It sounds like you want to automate the process? You might try using a macro function to do that, although im not totally sure thats possible with excell, I know matlab could handel a task like that if you were to write a program. I hope that at least points you in the right direction.

  4. Jul 13, 2005 #3


    User Avatar
    Science Advisor

    Have you tried selecting just the columns and running through the chart wizard? Even without having a selection, you can select the labels and add each "series" of values seperately.

    For example, if I wanted to plot a series of 15 samples taken at random times and with 3 levels of data like this:


    I'd start the chart wizard, choose the custom types tab, and choose lines on 2 axes and click next.

    Its highly likely you'll need to play around with the settings now, but for each data set I'd choose those cells as a "series" and choose the appropriate label.

    I used the 2 axes in case you needed to graph the time, its the only way I could make it graph the time as well as a numeric for multiple samples. Its odd though to do it, and it only let me add one more series to the graph. If you just wanted to use the time as a label, select the data and the wizard pretty much does that for you in the little sample I played with in Excel 2000.

  5. Jul 13, 2005 #4


    User Avatar
    Science Advisor
    Gold Member

    I think this would work, you could record a macro where you do it 'manually' for one series or so and then expand the macro you've did in the visual basic editor of excel to do the whole thing 'automatically', so could do it staying in excel.
  6. Jul 13, 2005 #5


    User Avatar
    Staff Emeritus
    Science Advisor
    Gold Member

    If your objective is to create one chart, make it all pretty, and then make the other 15 look exactly the same with minimal effort, the easiest way to do that is to just create copies of the first graph and then go into the "chart data" box and replace the reference cells with each column (if they're all columns with the same number of rows, you can just manually type in the letters designating the columns, which is probably faster than selecting the cells by clicking and dragging).
  7. Jul 13, 2005 #6
    The simplest is, lining up the data columns next to each other with the X-axis values on the left and the different y-axis value columns next to it. You can also give each column an header. Then select all the required columns, including headers and activate the graph function next. Select in the graph function the version "XY (scatter)". I prefer to select one of the line functions as "sub chart type" instead of the preset plot function.

    About the same idea as Moonbear suggests is creating the graphs seperately especially when the X-axis has different values, using the same method for each seperate graph. Then if you want to merge all the graphs, just select the source graphs one by one, hit copy, then select the target graph and paste.

    http://home.wanadoo.nl/bijkerk/Arctic.jpg [Broken] is a possible result.
    Last edited by a moderator: May 2, 2017
  8. Jul 13, 2005 #7


    User Avatar
    Science Advisor

    Also along the lines of what Moonbear mentioned, you could make one chart exactly the way it should be and save that as a user defined format (you can do it in the chart wizard). Then all you have to do is highlight the next two columns to add.

    Personally, if I had a lot of charts to do, i.e. more than 20 or so, I'd go the macro option. I had an analysis job that looked at gobs of data. I relied on macros quite a bit. If you can get used to them, they are lifesavers. Use the macro recorder when you create the first chart and then stop it when it is complete. Take a look at it. You'll see exactly where the column selections are. I would guess one for...next loop could loop you through all of your dependent variable columns.
  9. Jul 13, 2005 #8
    Much thanks

    Thank you all for the responses. For the sake of time I am probably going to go down the make the first graph, copy it, then throw the data into that first graph and continue making copies.

    I am very interested in working with Macros. I am going to do a search on how to use them effectively. If anyone knows of a site with a good tutorial, I would appreciate it.

    Thanks again for all of the help.

  10. Jul 13, 2005 #9


    User Avatar
    Science Advisor

    John Walkenbach has a great site. Also, googling on excel macros will bring up a bunch of sites thatwill provide free macros. That is the way I learned.


    Aaron Blood has a good site as well:
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook