The Basics of Excel Charting: Part III
This is the third of a Four (4) Part series on the Basics of Charting with Microsoft Excel 2010. Previously, we'd set up our Data Set and proceeded into Creating our Chart; today we will look at changing our Chart Scale. Excel uses a setting called 'Auto' to figure out the low and high point along the edge of the chart. Many charting gurus suggest that you need to take more control over those settings. Today, in Episode #1407, Bill will show us how to change our Chart Scale or Chart Axis settings in Excel.
...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
This is the third of a Four (4) Part series on the Basics of Charting with Microsoft Excel 2010. Previously, we'd set up our Data Set and proceeded into Creating our Chart; today we will look at changing our Chart Scale. Excel uses a setting called 'Auto' to figure out the low and high point along the edge of the chart. Many charting gurus suggest that you need to take more control over those settings. Today, in Episode #1407, Bill will show us how to change our Chart Scale or Chart Axis settings in Excel.
...This episode is the video podcast companion to the book, "Charts And Graphs: Microsoft Excel 2010", by Bill Jelen a.k.a. MrExcel.
For more Excel 2010 knowledge, check out "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions.
Transcript of the video:
This Excel podcast is sponsored by Charts and Graphs. Thank you. Learn Excel for MrExcel, Episode 1407 -- Fix The Chart Scale Hey, welcome back to the MrExcel netcast.
I'm Bill Jellen.
This week, episodes 1405 through 1408, we're taking a look at creating a chart from scratch.
In the first episode, 1405, we set up the data; second episode, 1406, created the chart; now, we want to fix one problem that happens frequently with charts.
You'll see here they ran the scale, not from 0 to 100% but from 74 to 90%.
In essence, they’d zoomed in; which, while the people-- the red people-- did a lot better than the blue people, they didn't do that much better.
When you look at it, I mean, the red is almost 30 percent larger than the blue but that's not really the situation.
83 to 89; you know, 6 points better, but not that much better.
And why does Excel do this?
Okay, Excel does this automatically.
Take a look at this data here, where they're tightly grouped around 5,000.
If you actually just did this chart from 0 to 6,000, you could see no variability at all.
So, in this case, it certainly makes sense to zoom in here from the, you know, from 4999.5 up to 5000.5 or so.
And you can see the variability between the points.
You know, this is if you're in a tightly controlled situation; maybe these are measurements from the machine and we're measuring variability and you need to be able to see that variability.
That makes a lot of sense, you know, but here I'm not convinced this is as tight enough that they should have zoomed in.
At this size, data is about 16%-- if everything's within 16%, you know-- around 100%, or thereabouts, they decide to zoom in.
Now, at larger data sets, the percentage is different-- so it's some algorithm that's out there.
Now, to fix this-- to fix this-- we can right-click on the numbers, if you're in Excel 2010 you can double-click the numbers-- or you can come to the layout tab, and then axis, primary vertical axis and more.
There's a few things here but not all we need so, always be ready to go to more and more.
These are the settings that we actually need, okay?
So, the minimum is set to automatic; the maximum is set to automatic.
So let's change that to fixed and we want to go from zero, fixed, go to 100%-- so, 100% is 1.
All right, now, a couple of other things we can do here: You'll see that when we went from 0 to 100, we're getting a grid line every 10%.
That's controlled by the major unit.
If you wanna have more control, you can actually come here and say, “Oh, I want that every .25”-- let's see how that looks-- or, “every .2”, you know, or whatever you think looks good to you.
Now, .1 might have been the right thing-- yeah, probably .1, so they got it right there-- but, still, you want to go through and have some control.
Minor unit-- well, I never change that.
That's if we set something up called minor grid-lines and you see that, well, by default you never get the minor grid-lines and, so, I don't have to change that one.
Now, some charts, Excel will go from zero to a hundred but then, if your data changes, they'll decide to zoom in.
So my recommendation: It's really worth the time to come in here and change this from automatic to fixed every time.
All right, now, tomorrow.
In our last in this four-part series, we’re gonna take a look at some other chart fixes like the chart title and the legend and the colors.
All right, well, hey I wanna thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
I'm Bill Jellen.
This week, episodes 1405 through 1408, we're taking a look at creating a chart from scratch.
In the first episode, 1405, we set up the data; second episode, 1406, created the chart; now, we want to fix one problem that happens frequently with charts.
You'll see here they ran the scale, not from 0 to 100% but from 74 to 90%.
In essence, they’d zoomed in; which, while the people-- the red people-- did a lot better than the blue people, they didn't do that much better.
When you look at it, I mean, the red is almost 30 percent larger than the blue but that's not really the situation.
83 to 89; you know, 6 points better, but not that much better.
And why does Excel do this?
Okay, Excel does this automatically.
Take a look at this data here, where they're tightly grouped around 5,000.
If you actually just did this chart from 0 to 6,000, you could see no variability at all.
So, in this case, it certainly makes sense to zoom in here from the, you know, from 4999.5 up to 5000.5 or so.
And you can see the variability between the points.
You know, this is if you're in a tightly controlled situation; maybe these are measurements from the machine and we're measuring variability and you need to be able to see that variability.
That makes a lot of sense, you know, but here I'm not convinced this is as tight enough that they should have zoomed in.
At this size, data is about 16%-- if everything's within 16%, you know-- around 100%, or thereabouts, they decide to zoom in.
Now, at larger data sets, the percentage is different-- so it's some algorithm that's out there.
Now, to fix this-- to fix this-- we can right-click on the numbers, if you're in Excel 2010 you can double-click the numbers-- or you can come to the layout tab, and then axis, primary vertical axis and more.
There's a few things here but not all we need so, always be ready to go to more and more.
These are the settings that we actually need, okay?
So, the minimum is set to automatic; the maximum is set to automatic.
So let's change that to fixed and we want to go from zero, fixed, go to 100%-- so, 100% is 1.
All right, now, a couple of other things we can do here: You'll see that when we went from 0 to 100, we're getting a grid line every 10%.
That's controlled by the major unit.
If you wanna have more control, you can actually come here and say, “Oh, I want that every .25”-- let's see how that looks-- or, “every .2”, you know, or whatever you think looks good to you.
Now, .1 might have been the right thing-- yeah, probably .1, so they got it right there-- but, still, you want to go through and have some control.
Minor unit-- well, I never change that.
That's if we set something up called minor grid-lines and you see that, well, by default you never get the minor grid-lines and, so, I don't have to change that one.
Now, some charts, Excel will go from zero to a hundred but then, if your data changes, they'll decide to zoom in.
So my recommendation: It's really worth the time to come in here and change this from automatic to fixed every time.
All right, now, tomorrow.
In our last in this four-part series, we’re gonna take a look at some other chart fixes like the chart title and the legend and the colors.
All right, well, hey I wanna thank you for stopping by.
We’ll see you next time for another netcast of MrExcel.
