Our guest host today is Jon Peltier. Jon maintains an excellent set of charting examples at Peltier Tech Blog - Peltier Tech Excel Charts and Programming Blog. In todays podcast, Jon discusses the best way to organize your data for charting. Episode 469 shows you how.
Transcript of the video:
Welcome back to the learn Excel podcast.
I'm Bill Jelen.
We're here at the Seattle museum of flight.
And we have John Peltier, the smartest guy in Excel charting I've ever seen.
Check out his website at "peltiertech.com".
All the cool charting things that I ever do, I've stolen from John.
Here is to talk about how to setup data.
John: Yeah, hi!
Bill asked me to talk a little bit about charting and I couldn't think of anything in two minutes, that I could cover.
Except that if you spend two minutes with your data, you're going to save yourself two hours of problems.
I have a sample data range here, and just very briefly the important things to do with data.
To make your life simple, you can do it the hard way if you want and spend those two hours.
But try to avoid any blank columns and blank rows in your data.
Make sure everything is contiguous and if you notice this range that I have, is a continuous range.
Column A, has a bunch of numbers which are going to be my X values or my category values and row 1, Alpha and Beta are going to be my series names.
You can arrange your data, so that the series are in columns or in rows.
But doing it in columns is better because it's consistent with the way that Excel handles databases, and I one other trick that's going to make your life simple.
As you notice that the top left cell, in cell A1 is blank.
There's no data in it.
That helps Excel to parse the data, when that cell is blank it knows to use the first column for categories and the first row for series labels, and I'll show you how that works.
Excel has a pretty smart data parser when it deals with things like data for charts, and if everything is contiguous it selects that all contiguous range and...
So, Excel will pick all them all the range that's connected and it will pick the series labels and the category values.
So, if I go over here and click on the Chart Wizard button and I'll just use the the default chart type here and click [ finish ].
And you'll see that my chart has 2 series on it.
My category labels are 1 through 10, just like in column A and the values go from 0 3.4 up to 5.2 in Alpha and they go from about 5.3 down to 2.3, in Beta.
I'll show you what happens to chart if we have any missing rows in here.
Let's say I have a blank row.
I'm just going to insert a blank row, you notice I didn't have to even in that example, select the whole range.
I just selected one cell and Excel expands that range as needed and here I go, and I make my default chart and look.
It only selects that the first 5 rows because there's a gap book, before the 6.
You also notice that the range that is in the chart is highlighted with blue for the values, purple for the category labels and green for the series names.
Those are nice because you could actually stretch that range and in flute it all.
Now, we have a gap but again, if we delete that row, back to the original chart.
Now, just remember if you keep your data simple arrange it ahead of time, you can save yourself a lot of hassles.
It's not only with charts but with data for pivot tables and data for that you need to analyze with formulas.
So, good luck in good charting.
All right!
MrExcel: Alright! Hey! John thanks very much.
Make sure to check out John's website, "peltiertech.com".
Great, great charting examples there.
Hey, we'll catch you next time for another netcast from MrExcel.
I'm Bill Jelen.
We're here at the Seattle museum of flight.
And we have John Peltier, the smartest guy in Excel charting I've ever seen.
Check out his website at "peltiertech.com".
All the cool charting things that I ever do, I've stolen from John.
Here is to talk about how to setup data.
John: Yeah, hi!
Bill asked me to talk a little bit about charting and I couldn't think of anything in two minutes, that I could cover.
Except that if you spend two minutes with your data, you're going to save yourself two hours of problems.
I have a sample data range here, and just very briefly the important things to do with data.
To make your life simple, you can do it the hard way if you want and spend those two hours.
But try to avoid any blank columns and blank rows in your data.
Make sure everything is contiguous and if you notice this range that I have, is a continuous range.
Column A, has a bunch of numbers which are going to be my X values or my category values and row 1, Alpha and Beta are going to be my series names.
You can arrange your data, so that the series are in columns or in rows.
But doing it in columns is better because it's consistent with the way that Excel handles databases, and I one other trick that's going to make your life simple.
As you notice that the top left cell, in cell A1 is blank.
There's no data in it.
That helps Excel to parse the data, when that cell is blank it knows to use the first column for categories and the first row for series labels, and I'll show you how that works.
Excel has a pretty smart data parser when it deals with things like data for charts, and if everything is contiguous it selects that all contiguous range and...
So, Excel will pick all them all the range that's connected and it will pick the series labels and the category values.
So, if I go over here and click on the Chart Wizard button and I'll just use the the default chart type here and click [ finish ].
And you'll see that my chart has 2 series on it.
My category labels are 1 through 10, just like in column A and the values go from 0 3.4 up to 5.2 in Alpha and they go from about 5.3 down to 2.3, in Beta.
I'll show you what happens to chart if we have any missing rows in here.
Let's say I have a blank row.
I'm just going to insert a blank row, you notice I didn't have to even in that example, select the whole range.
I just selected one cell and Excel expands that range as needed and here I go, and I make my default chart and look.
It only selects that the first 5 rows because there's a gap book, before the 6.
You also notice that the range that is in the chart is highlighted with blue for the values, purple for the category labels and green for the series names.
Those are nice because you could actually stretch that range and in flute it all.
Now, we have a gap but again, if we delete that row, back to the original chart.
Now, just remember if you keep your data simple arrange it ahead of time, you can save yourself a lot of hassles.
It's not only with charts but with data for pivot tables and data for that you need to analyze with formulas.
So, good luck in good charting.
All right!
MrExcel: Alright! Hey! John thanks very much.
Make sure to check out John's website, "peltiertech.com".
Great, great charting examples there.
Hey, we'll catch you next time for another netcast from MrExcel.
