Pasting data on an Excel chart is one way to compare two different-sized populations on an XY Scatter chart. Recap:
X-Y Scatter Charts with 2 series are difficult to create
Create a one-series chart
Select the new data, including the headings
Click the chart
Paste Special
Choose X setting
Add a legend to tell the series apart
X-Y Scatter Charts with 2 series are difficult to create
Create a one-series chart
Select the new data, including the headings
Click the chart
Paste Special
Choose X setting
Add a legend to tell the series apart
Transcript of the video:
Learn Excel from MrExcel Episode 1991 - 2-Series Scatter Chart!
I'll be podcasting the whole book, go ahead and click that "i" on the top-right hand corner and subscribe to the playlist.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday, we talked about pacing new data on a chart, and it's funny, the only way that I know to create a comparative scatter chart, where we're comparing two different populations, particularly where those populations have a different number of members, is to use the paste trick, and I'm going to show you this: So, we're comparing years of experience along the bottom with salary, right, so you would expect, the longer you've been employed, the higher you're going to work, but I have two populations, the people who own the MrExcel Excel book, and the people who don't.
I'm going to start out creating the chart for just one of those populations, the first series, Insert will choose a Scatter Chart, Regular Scatter Chart, and we get the first series in the chart, let me resize this so it fits between the two.
Alright, now to add the second series to the chart.
I'm going to very carefully select the new data, including the heading.
I'm going to copy that data, CTRL+C and then click on the chart, and then go up here to a very special version of Paste Special.
I learned this from John Pelletier the chart goofer who hikes LM VP(?).
We're going to Paste Special, and choose "Categories (X values) in First Columns".
So it's going to be a new series, the values are in columns, and we don't want to replace existing categories.
And click OK.
Alright, now we get our second series there, but you can't tell which is which, so you need to Add Legend, and I want to show the Legend at Top.
Now we can see: dark blue - book owners, light blue - no book.
And then we come back here and change the color of these to be red.
Alright so there you go, you could in theory keep adding more populations, just like this if you had 3 or 4 different groups to compare.
Scatter charts - a whole different type of chart, they have an extra component that most other charts don't have.
So it's tricky, at least for me, to be able to create multiple series in the same chart.
Hey, this is tip #10B, this is even one of "The 40 Greatest", that's just a bonus tip, between tip 10 and tip 11, there's so much good stuff in this book, go ahead and order our online right now, click that "i" on the top-right hand corner, I would really appreciate it.
Episode Recap: alright so we created X-Y Scatter Charts, with 2 series that are difficult to create, you have to start with a 1 series chart, and then select the new data from the second population, including the headings, copy that, and then, use the special version of the Paste Special dialog box.
Saying that the X values are in the first column and then, add a legend, to tell the series apart.
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting the whole book, go ahead and click that "i" on the top-right hand corner and subscribe to the playlist.
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday, we talked about pacing new data on a chart, and it's funny, the only way that I know to create a comparative scatter chart, where we're comparing two different populations, particularly where those populations have a different number of members, is to use the paste trick, and I'm going to show you this: So, we're comparing years of experience along the bottom with salary, right, so you would expect, the longer you've been employed, the higher you're going to work, but I have two populations, the people who own the MrExcel Excel book, and the people who don't.
I'm going to start out creating the chart for just one of those populations, the first series, Insert will choose a Scatter Chart, Regular Scatter Chart, and we get the first series in the chart, let me resize this so it fits between the two.
Alright, now to add the second series to the chart.
I'm going to very carefully select the new data, including the heading.
I'm going to copy that data, CTRL+C and then click on the chart, and then go up here to a very special version of Paste Special.
I learned this from John Pelletier the chart goofer who hikes LM VP(?).
We're going to Paste Special, and choose "Categories (X values) in First Columns".
So it's going to be a new series, the values are in columns, and we don't want to replace existing categories.
And click OK.
Alright, now we get our second series there, but you can't tell which is which, so you need to Add Legend, and I want to show the Legend at Top.
Now we can see: dark blue - book owners, light blue - no book.
And then we come back here and change the color of these to be red.
Alright so there you go, you could in theory keep adding more populations, just like this if you had 3 or 4 different groups to compare.
Scatter charts - a whole different type of chart, they have an extra component that most other charts don't have.
So it's tricky, at least for me, to be able to create multiple series in the same chart.
Hey, this is tip #10B, this is even one of "The 40 Greatest", that's just a bonus tip, between tip 10 and tip 11, there's so much good stuff in this book, go ahead and order our online right now, click that "i" on the top-right hand corner, I would really appreciate it.
Episode Recap: alright so we created X-Y Scatter Charts, with 2 series that are difficult to create, you have to start with a 1 series chart, and then select the new data from the second population, including the headings, copy that, and then, use the special version of the Paste Special dialog box.
Saying that the X values are in the first column and then, add a legend, to tell the series apart.
OK, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
