Yesterday's podcast showed a tedious method for creating a chart with 5 XY series. Today, Episode 1032 shows a method for streamlining the creation of this chart.
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
This is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Basically we start out with massive amounts of data.
So how we're going to analyze as well plus let's fire up a pivot table.
Lets see if you can solve this problem.
Hey, all right welcome back to the MrExcel netcast.
Now yesterday we had peers question why I had to create this x y chart with five different series.
I had to create the chart with one series and then go through and add tedious like series 2 series 3 series 4 series 5.
you know I'm saying myself there must be some way to tell excel about all of these series at once and my initial attempt at this was to set up a data set where I had the x and y for series 1 and then the x and y 4 for series 2. the x and y for series 3, the x and y for series 4 and so on.
but unfortunately while any rational person would set up the data this way that's not how microsoft interpreted.
what they did was I said, okay?
Well, this is the x for all of the series.
This is the y for series 1, y 4 series 2, y 4 series 3, y 4 series 4 and I ended up with 9 series instead of the five that I wanted.
so we can fool excel, and you'll have to decide for yourself If this is an easier way to go.
I'm going to build a fake data set with the five series names that I went up here I now use insert, scatter chart bam and then here's what we get to do.
we're going to go through one by one and Choose each series.
Let's kind of arrange this so we can see the chart and see the data.
and so we go to Layout tab choose series 1.
Which is called series Red and we're going to grab the X and resize it down and then grab the blue outline and point to the Y for series 1. all right so good. That's done.
Now we can go on to series orange and again, they're going to redraw it back up see where they thought that that first column was the x for all of them will resize that down and then grab the blue and drag it down to the Y for series 2 and then up here the next one series green. Again, we're just going to keep repeating this process.
I think it's a little bit easier to drag these boxes around and resize them, then go through the add series and point to each series Individually.
At least this kind of gets them there. The names are there correctly.
To me, it's a slightly easier way to go and then once we complete this process.
of course we're still going to have to go through and use the exact same steps that we did yesterday, in order to change the markers to Circles and change the colors of the markers but that's still the same process that doesn't change at all but I think that this method here at least I guess the right number of series in Probably if I was a little bit smart I would have made most of the series be exactly 3 or all of the series to be exactly 3 because then I wouldn't have to resize here So whichever one you have the most of is probably what we would go through and do Now from this point, we're just going to repeat the same steps we did yesterday in order to change all those markers to circles.
Go back and watch yesterday's podcast rather than watch me go through that tedious bit again.
So you know peer wrote in and said I ended up creating this in Powerpoint because there didn't seem to be any way to do it in excel.
Well there are a couple of ways to do it in excel unfortunately neither one of them is at all Friendly or intuitive.
However, it can be done.
So there you have it.
Want to thank you for stopping by and We'll see you next time for another netcast from MrExcel.
Basically we start out with massive amounts of data.
So how we're going to analyze as well plus let's fire up a pivot table.
Lets see if you can solve this problem.
Hey, all right welcome back to the MrExcel netcast.
Now yesterday we had peers question why I had to create this x y chart with five different series.
I had to create the chart with one series and then go through and add tedious like series 2 series 3 series 4 series 5.
you know I'm saying myself there must be some way to tell excel about all of these series at once and my initial attempt at this was to set up a data set where I had the x and y for series 1 and then the x and y 4 for series 2. the x and y for series 3, the x and y for series 4 and so on.
but unfortunately while any rational person would set up the data this way that's not how microsoft interpreted.
what they did was I said, okay?
Well, this is the x for all of the series.
This is the y for series 1, y 4 series 2, y 4 series 3, y 4 series 4 and I ended up with 9 series instead of the five that I wanted.
so we can fool excel, and you'll have to decide for yourself If this is an easier way to go.
I'm going to build a fake data set with the five series names that I went up here I now use insert, scatter chart bam and then here's what we get to do.
we're going to go through one by one and Choose each series.
Let's kind of arrange this so we can see the chart and see the data.
and so we go to Layout tab choose series 1.
Which is called series Red and we're going to grab the X and resize it down and then grab the blue outline and point to the Y for series 1. all right so good. That's done.
Now we can go on to series orange and again, they're going to redraw it back up see where they thought that that first column was the x for all of them will resize that down and then grab the blue and drag it down to the Y for series 2 and then up here the next one series green. Again, we're just going to keep repeating this process.
I think it's a little bit easier to drag these boxes around and resize them, then go through the add series and point to each series Individually.
At least this kind of gets them there. The names are there correctly.
To me, it's a slightly easier way to go and then once we complete this process.
of course we're still going to have to go through and use the exact same steps that we did yesterday, in order to change the markers to Circles and change the colors of the markers but that's still the same process that doesn't change at all but I think that this method here at least I guess the right number of series in Probably if I was a little bit smart I would have made most of the series be exactly 3 or all of the series to be exactly 3 because then I wouldn't have to resize here So whichever one you have the most of is probably what we would go through and do Now from this point, we're just going to repeat the same steps we did yesterday in order to change all those markers to circles.
Go back and watch yesterday's podcast rather than watch me go through that tedious bit again.
So you know peer wrote in and said I ended up creating this in Powerpoint because there didn't seem to be any way to do it in excel.
Well there are a couple of ways to do it in excel unfortunately neither one of them is at all Friendly or intuitive.
However, it can be done.
So there you have it.
Want to thank you for stopping by and We'll see you next time for another netcast from MrExcel.
