To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Separate graphs from one dataset with few steps?

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
Ralfbergs
FISO Knight
Posts: 10075
Joined: 25 Jul 2011, 09:09
Location: LVA
FS Record: Murfs Premiership Predictions Knockout Cup 13/14 Winner

Separate graphs from one dataset with few steps?

Post by Ralfbergs »

Not sure if the subject of the topic makes sense, but anyway, my question is:

Is it possible to take time series data, select them all and plot them on separate graphs for each item instead of plotting all of them in one? Of course it is possible to plot separate things on separate graphs, but for me that would mean selecting each item and its time series, plotting a graph, then taking next item and doing all again etc etc, that would be too long.

To illustrate the idea, I'll make an example:
Lets say I would have time series type data about inflation in all EU countries about each month starting from 2010_01 until now in Excel. If I select everything and try to make a line graph it would of course plot all countries in one graph. Now my question is - is it possible to plot each country on a separate graph instead of this? For example, Germany would have its own graph, France its own etc. Of course I could take and select each country separately and plot them separately and get the result, but I need a faster way to do that but not sure if it is possible in Excel?

(what is an even bigger problem is that I actually need each two items in a separate graph, but for now it would be enough with just this)

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Separate graphs from one dataset with few steps?

Post by Surprised »

I think you can do it with a pivot table and then create pivot charts from that table.
Not totally sure on the process though.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Separate graphs from one dataset with few steps?

Post by Surprised »

A way i know works is to highlight whole table and create graph.
Then press Alt+F1 to create copy of the current chart and then edit source data by removing what you don't need.
Repeat until done

User avatar
Ralfbergs
FISO Knight
Posts: 10075
Joined: 25 Jul 2011, 09:09
Location: LVA
FS Record: Murfs Premiership Predictions Knockout Cup 13/14 Winner

Re: Separate graphs from one dataset with few steps?

Post by Ralfbergs »

Surprised wrote:A way i know works is to highlight whole table and create graph.
Then press Alt+F1 to create copy of the current chart and then edit source data by removing what you don't need.
Repeat until done
Ok, that sounds like a way to at least simplify the process. Thank you very much! :)

Edit: Even though it's almost the same as copying the graph and editing the lines I guess :D
Found a way to do it with a pivot graph - won't be separate graphs, but at least I'll be able to filter what appears on the graph and that will do.

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Separate graphs from one dataset with few steps?

Post by crispybits »

Another way (and it's not too complicated but may take a while to explain) that may or may not be suitable is that it would be possible to set up a graph wth a dropdown at the top to select the country you want and when you select each country on the dropdown the relevant country information pops up. That means you only have to have a single graph to present all the info but it very much depends on how you're then using the graph (as in it won't work if it's for something you have to print, anyone using it would ned to keep it open in excel to change the data they are viewing)

Let me know if this sounds suitable and I'll spend a bit more time expanding on how it's done.

Edit - this is pretty much what you've already found by the looks of it - I should read more closely - there are two ways of doing it though, the pivot way you've found and a non-pivot way

Edit 2 - you could also do this through VBA if the data you're actually using comes in in the same format each time and you're looking to do the same thing to it each time - that's something that I couldn't really explain very well in a post on here but would be happy to help you set it up with a more in depth discussion via PM - the drawback of this is you'd need to learn a bit of VBA coding to troubleshoot if things do ever go wrong because obviously I can't be on call during the working day to help fix things.

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Re: Separate graphs from one dataset with few steps?

Post by Mystery »

I don't have a better answer but if you record a macro when creating the graph of all the data, the underlying code created might give you some clues how to amend it for just parts of the data.

But personally I'd still use the pivot chart.

User avatar
Ralfbergs
FISO Knight
Posts: 10075
Joined: 25 Jul 2011, 09:09
Location: LVA
FS Record: Murfs Premiership Predictions Knockout Cup 13/14 Winner

Re: Separate graphs from one dataset with few steps?

Post by Ralfbergs »

crispybits wrote: Edit 2 - you could also do this through VBA if the data you're actually using comes in in the same format each time and you're looking to do the same thing to it each time - that's something that I couldn't really explain very well in a post on here but would be happy to help you set it up with a more in depth discussion via PM - the drawback of this is you'd need to learn a bit of VBA coding to troubleshoot if things do ever go wrong because obviously I can't be on call during the working day to help fix things.
Yeah I thought it might be possible with VBA, but it seems that pivot graphs will do good enough. Anyway, thanks for offering help :)
Mystery wrote:I don't have a better answer but if you record a macro when creating the graph of all the data, the underlying code created might give you some clues how to amend it for just parts of the data.

But personally I'd still use the pivot chart.
That's what I decided too, pivot charts will be good enough as I don't need to print the graphs, just make comparisons between different things and it's quite easy to do the filtering through pivot.


Thanks to everyone for advice, using Fiso gave me answers much faster than using google, as I could only find questions about how to make a graph with more than one item (which is basically just making a graph) instead of the other way around :D.

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Separate graphs from one dataset with few steps?

Post by crispybits »

Good luck Ralfbergs. My offer to help out with VA coding stuff remains open if you ever feel like it could be a good solution in the future. I largely taught myself but I had help from various people at times and I know that if I was just struggling along alone with problems I wouldn't have solved half of them.

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”