Join Dennis Taylor for an in-depth discussion in this video Modifying axes, part of Excel for Mac 2011: Charts in Depth.
- View Offline
- When you create a chart, Excel uses what's called Automatic Scaling. If we look at the chart below the data here on the sheet called Profits we see how the numbers along the X axis, that's the vertical axis on the left side, top off at 400. Notice that the tallest column doesn't even reach 350. Now, this doesn't bother me. Probably doesn't bother you either. But on the other hand there could be times where you say, "I wanna change what I actually see here." So, if we select a chart and go to the Chart Layout tab we do have an option called Axis.
Notice that that's plural, just click it. If we're concerned about that vertical axis, of course, we will go to Vertical Axis, and the Default setting is in place. Now, possibly there will be times where you might want to display the numbers in thousands or in millions or billions. Based on the data in this worksheet and what we can see in row 2 off to the left these numbers are already displayed in the way that we might want them. But we can go to Axis Options and sometimes what you wanna consider here is changing the actual values that you see on the scale.
Here's Scale here. Automatic Scaling, Default Scaling, is based on an algorithm that considers the range of the numbers here. If we want this Maximum to be 350 let's change it to 350. Now, sometimes this causes some surprises. Already in the background what's happening? It's every 50 and that's not wrong. I wouldn't call it that. If we change this to something like, and this would be a little bit unusual granted, but if I change this to 375 look what happens. The scaling actually starts below 0 and the intervals are 50 but they start at a very unusual and unorthodox.
25, 75, 125. So, even though you've got control over this sometimes you get some usual results. Now, I've got control over the minimum as well so I could change that to 0. What if it's 0 and 375? Now, look at the entries but notice that it doesn't go to 375. It goes to 350. So, you'll have some surprises. Major Unit and Minor Unit occasionally you might wanna change these. We don't have minor grid lines here so that's not an issue just yet but we could make the Major Unit be 100.
That, of course, will mean fewer lines and sometimes that's acceptable. But you've got control over it. Now, in the chart to the right, the green chart, we've also got some scaling in effect. We can change that too. Sometimes you want the scaling to go the opposite direction because maybe you're about to put in a text box here. You want more empty space above a chart. And a faster way to get in here is to simply double click the axis. I'm going to double click the green axis here, the numbers, then it takes up right into the dialogue box this way.
I want the Maximum to be 500. I always give Excel a chance to change it and see if you like the intervals there. If it looks too crowded to you. In other words, it's every 50, change the Major Unit to be 100. We saw how to do that earlier. And we can see the effect of that as well too. So, you've got some choices here and it will make sense to override these at times. Now, notice that the display of the numbers here is 500.0. That simply is reflecting the actual data. These numbers out here contain a single decimal and the description row is millions of dollars.
So, for example here, these are all even but the total, the average out here, that's 257.5 as an average. So, it does look a little unusual in the way these are being displayed. So, what if we come back and double click one of the values out here, brings us back into the dialogue box, and we get a number here. You'll see this choice. It says Linked to Source. Let's not link it to source. Meaning we wanna change the format. We're not changing the value or the actual linkage of the values but we're changing the format of it, perhaps.
And maybe we want these numbers here to be displayed without a single decimal. So, we could get a number, or currency, or counting. Whatever we think works best here. Perhaps Currency, but let's not show 2 decimals or 1 but 0, and you see what's happening in the background already. So, even though the source data itself has that decimal in it, this doesn't. So, you've got control over that as well too. Now, in the chart below this, this is a scatter chart. You might not be too familiar with these. These are based on time. And time's those special problems in Excel when working with charts.
Do recognize that these are times of day and on these different times of the day a sale is made and maybe this is a large equipment company or a company that doesn't have that many sales throughout the day but here a sale was made and these are possibly dollars or items. Let's say they're dollars. But look at the scaling on the chart. Across the bottom we're seeing times of day but the intervals are really strange. There's two hours and 20 minutes and we're starting at midnight here, and we don't have any sale until eight o'clock in the morning.
So, if we were to double click this axis down here, and remember, another way to get here, a more standard way, is to make sure the chart is selected, Chart Layout, Axis, in this case we'd be looking at the Horizontal Axis, and we go to Axis Options. Double clicking would've gotten us here faster. So, we see some choices here. Let's go to Scale. And we see Maximum .8, Minimum 0.0. Now, a slight digression here as we talk a little bit about time. In Excel, if you work with dates and times perhaps you know the unit of measure is a day equals one and within a day times are represented fractionally.
So, half a day is .5. Six AM, a quarter of a day, is .25. So, we might start this as six AM. Granted, that's before when we really need to start it but just to show what happens here. Let's make the minimum be .25. And OK, let's take a look at that. It starts at six AM now. The interval is still two hours and 24 minutes and our last sale is at 5:48, so we might wanna end this. So, as you start to experiment with this and it might require, at least the first few times, a little bit of experimentation.
If we want the Maximum to be six PM that's three quarters of a day, so we'll make that be .75. This might automatically change the interval to be something that we want to use. And we can see in the background what's happening there. That's still two hours and 24 minutes. So, what would be a sensible interview, every two hours? That's a 12th of a day. Are you familiar with the fraction for that? That's eight and a third percent so .08333. We're getting a little geeky here perhaps but let's try this and see how that works.
So, then we have every two hours. That would probably be acceptable for most people. If we wanted to put in every hour we could do that as well too. Use a number half of that .08333 and so on. But we've got control over the scaling. And I think particularly with time-based charts you wanna consider re-scaling here because what we saw earlier just wouldn't fit most people's needs. We do have control over scaling and there's another option here you wanna be alert to and I've seen this happen on charts at times. Here's a column chart and every value here is well above 100, and so, can we start the chart at 100? We could.
Let's do recognize what we're getting into, though, something that could be a little hazy. Once again, to get into the dialogue box a bit faster I'm going to double click this axis, double click, and I'm going to change the Minimum here to be 100. Now, if I want Automatic Scaling to take over there I'll simply click this box here and here and for the moment we're seeing what's happening, and that looks like it's okay. Now, any time you see a column chart that starts above zero, you wanna look at it with a different eye. For example, I'm looking at the March columns and I see the sales are three times the expenses.
At least visually that's what I'm seeing. Now, I can read the numbers and the expenses here are 150 and the sales are 250. My eye, my glance, is telling me one thing. The values are somewhat different. And I'm looking at January. It looks like the expenses are more than half of the, a little, about half, a little bit more than half of the sales. The sales are 200 and January looks like it's about 160. We can also see the numbers up here, of course, too. But be a little bit careful with that. Now, is that truly wrong? You can make the case for saying, "Well, it's okay. I mean, everything starts above 100.
"Why not just start the chart at 100?" But whenever you see that be alert to the way these are appearing to your eyes versus the real values. And sometimes maybe someone's trying to fool you. Other times, I mean, it was just an innocent mistake. Recognize, too, the scaling that negatives are possible. Usually we don't have negative sales but based on adjustments possibly. So, I'm going to jump into February here and make that a negative, minus, I'll make a -230. The chart reacts immediately. Now, remember, I don't have automatic scaling here so I'll go back to the chart, double click that left axis, and by clicking the box here we'll go to Auto Scaling.
It just now comes up as -300. So, that's a viable chart. Makes sense and sometimes we will have negatives there. Notice the green chart to the right also based on the same data reacted there as well too. So, we've got control over scaling. I think much of the time you won't bother with it, you won't touch it. Automatic Scaling kicks in nicely. Here's another thought too. What if you, month by month, update a chart? If you do and the numbers start to grow the scaling will change.
And so if you're comparing your sales, or your profits, or whatever the numbers might be, looking at your January, February, March chart and then in April, maybe everything goes up a bit, the scaling on your April chart that you then create might change if you have Automatic Scaling. But sometimes when you're comparing two different charts be sure to check out the scaling. Now, I'm gonna go back to the data here and simply make that positive again, and create a line chart simply with the top set of data here for just sales. Just sales.
I'll create a line chart and we see that. Now, you recognize the slope of that. First of all, we don't need a legend here. I'll click the legend, press delete, it's redundant. Recognize the slope here. So, if you were talking about sales, you say it was a pretty good first half. We went up a bit in February, March. Slight dip in April and then we recovered and things climbed even faster and better. Looks good. Sales went up. If I change the scaling here. I'm gonna double click the left axis again, anywhere along in here. I'm gonna change the scaling so that 200 is the Minimum 'cause that's the actual lowest value there.
So, I'm overriding Automatic Scaling, putting in 200 here, and OK. Now, as I press cmd + z to undo, recognize the slope now. You could be saying the same things. From January to March, steady upward trend, slight dip in April, and then we really improved a lot for May and June. I'll press cmd + z, there's the same chart, same data, but it really isn't the same chart, and call it emotional, call it a feeling, it doesn't look so dramatic here.
Then I just press ctrl + y to undo the undo, now we're looking at the data this way. So, at different times when you're considering this keep an eye on this scaling that we see down the left-hand side. Again, we're not saying that people are always trying to fool you or anything, but if you're making the presentation and you're trying to share the data in the certain light consider the options here of changing the scaling and how the data might look. We saw that earlier with a column chart and we see it here with a line chart. So, you're in charge of Automatic Scaling. Again, from the Chart Layout tab you can begin all of this by going to Axis, possibly one of these paths, or as is often the case with some of these different features from this Chart Layout tab, a simple double click activates the dialogue box that we need.
- Creating charts with one click
- Selecting the right chart types
- Changing chart type or source data
- Choosing a chart layout and style
- Saving charts as templates
- Editing titles, legends, and labels
- Adding a data table
- Analyzing data with trendlines
- Formatting the chart
- Customizing different chart types
- Creating Gantt and frequency charts
- Creating in-cell charts
- Moving charts