Making a series of graphs from a dataset

May 5, 2015 4:37 AM

I need to make a series of 40 graphs (all of the same format) from an excel dataset and I'm having trouble figuring out how to do this efficiently. Basically, I would want something that would cycle through all the rows producing a separate graph for each row that could be copied into the appropriate section in the report. Although, I would prefer to do this in excel so that others in my dept could also use it, I do have R available if there is an easier way to do this in that environment. Any ideas? Thanks!
posted by buttercup to Computers & Internet (6 answers total)

Do it in R. Use a for loop.
posted by oceanjesse at 5:06 AM on May 5, 2015

Depending on what you're doing with these graphs, it might be a candidate for the joy that is sparklines.
posted by soma lkzx at 5:28 AM on May 5, 2015 [1 favorite]

You can use VBA for this, here is pretty comprehensive answer to a related problem that should get you started (haven't ran it though - YMMV). That being said, if you don't have some VBA experience already the time it will take you to get this to do what you want will probably be more than the time it will take you to copy and paste 40 datasets into a template file.

Also gnuplot kills this kind of problem. Note that your output will look distinctly non-excel-like unless you fiddle with the formatting a bit - the presets are really inspired by scientific journal graphs.
posted by Dr Dracator at 5:39 AM on May 5, 2015 [1 favorite]

If you make a reference cell telling which line goes to which graph, you can do this in Powet BI easily. It is a new product that works with Excel but is available for free.
posted by munchingzombie at 6:24 AM on May 5, 2015

Agreed with oceanjesse that the easiest way to do this is in R. You could do it in a for loop or maybe use some of the apply functions.
posted by anthropophagous at 10:47 AM on May 5, 2015

Seconding sparklines. You can do this in R very easily.
posted by a lungful of dragon at 1:54 PM on May 5, 2015