Values on the Vertical (Y) Axis of a chart are automatic, reflecting the scope of the source data. Double-click the axis to change the minimum and maximum values and also adjust the intervals, causing changes in gridline displays. Occasionally you will need to adjust scaling on the Horizontal axis (X) Axis also.
- [Voiceover] In Excel charts you can control the display of axes. The vertical axis down the left-hand side of a chart like we see on this worksheet called Profits is sometimes called the value axis. The horizontal axis across the bottom, sometimes called the category axis. We see these on many chart types in Excel, and usually what we see is what's called automatic scaling. For example, in this green chart, the scaling automatically kicks in. If the June number here got adjusted from 340 to be 410, for example, as soon as I press Return watch the chart to the right.
The rescaling kicks in, we automatically see a new scale. And the chart below, which is covering the same data, it happened there too. Both charts top out at 450. Now I'm going to press Command Z. You'll see how it kicks back and goes back to its previous setting. Maybe these are the real numbers, but we don't want the chart to go to 400, we want it to go to 350. We can double-click the left axis. Now first thought might be, why don't we select the chart and then use Chart Design in the menu, and add Chart Element because we've got an axis choice out here.
Now there's nothing wrong with going down this path, but if we go to More Axis Options, and by the way, rarely will you want to turn these off, so I think for the most part you pretty much ignore those, More Axis Options activates a dialog box on the right-hand side with lots of different choices. But if we want to cut to the chase and get there quickly, simply double-click one of the numbers down the left-hand side, and watch that Format Axis dialog box on the right. It gives us some choices related to bounds. Here are the automatic scaling numbers that we see here.
I'm going to change the maximum here to be 350. And either clicking somewhere else or pressing Enter we see how the chart has reacted immediately. Now we can also change what are called the major and the minor units. These also relate to grid lines. Minor doesn't really have much meaning unless you are using minor grid lines. So the green chart is using them, the white chart isn't. But for the moment we're only changing the green chart. We could change the major grid lines to be 100, and it automatically caused a change to minor, see what's happening there? And notice along the way something else strange that happened now.
It has introduced negative entries in the scaling. Down the left-hand axis we see a minus 50. Nothing wrong with that of course, but do we really want to see that? So I'm going to change that minimum to be zero, and we see what's happening now. So we've got some control over the display of those. I guess the question would be how often do you want to do this? Now, suppose you never anticipated that the numbers would ever change again, and yet they do. What happens if this 340 number, which is at the top of the chart right now, what if that number becomes 375? What's going to happen to the column? Nothing at all, we won't see any change, and the number will not be reflected properly.
So you'd be thinking out that idea about changing the automatic scaling. Now another idea you might pursue too, a not necessarily good idea, but you could make the case for saying well, why don't we just start the chart at 100? So I'm going to double-click that vertical axis again on the green chart, and make the minimum be 100, and there we see what's happening. Now you can make a case for saying that, it's accurate in many respects, but when column charts don't start at zero, your eye might be telling you one thing and the number is telling you something else.
Look at March, for example. It looks like the blue entry there is more than twice as big as the orange entry. The blue entry is 250, the orange one is 150. That's because the column chart is starting at 100 now because I changed the scaling there, so that probably isn't a great idea, although in a certain case you could say well, it's not really wrong. People can see it, they can read the numbers and so on. But nevertheless, that's another option you occasionally will choose. Now the chart here below the others, this one here has a time interval across the bottom, and those are a little bit tricky.
Most of the time when you're adjusting scaling it's likely to be on the vertical axis, but here it's on a horizontal axis. But the same general approach. I'm going to double-click one of the times at the bottom, and off to the side we see bounds. But the minimum here is going to be a little bit different. First of all, as we look at the chart, the chart is based on the data that we see right here, and we're tracking sales from times shortly after eight AM, until just before six PM, and yet the chart is showing times starting at midnight going to two AM and four AM and so on.
We really don't want that in the chart, so we want our chart to begin at eight AM and end at six PM. So, going back to the chart and double-clicking there again. Eight AM is one-third of the day, so for the minimum here we put in .3333, one-third of the day. And for the maximum we want that to be at six PM, that's .75. Now sometimes when you put in entries, Excel doesn't respond, and it kind of throws you, and I wish it did respond immediately here, but although I put in .75 and it seems to have kept it there, that six PM, we're not seeing that in the chart.
The other aspect of this that we want to change would be the units. Now we might have to get our calculators here, or simply use Excel to do this, but let's say we want the major unit here to be one hour. That's one twenty-fourth of a day. So jumping out into Excel here into the worksheet, I'm going to type equal one slash 24. A fractional representation of that, .0416666. So again, going back to the chart and double-clicking the horizontal axis, I'll make the major unit here be .0416666, and Tab or Enter, and there we are.
We finally have the display straightened out the way we want. We've overridden the automatic scaling. Now there will be times when you want to revert to it, but certainly not here. This chart is much more readable and easier to handle now since it's got the scaling that we want. We're controlling the interval, we're controlling the beginning and the end here. If you ever do need to revert to automatic scaling, for example in the green chart up above, which I changed earlier, again double-click the vertical axis, and off to the right I'm going to click the counterclockwise arrow here for reset, and the one above it as well too.
Possibly the others, maybe not. But we see how to make changes there. So there will be times when you want to change the axes. More often that not, it's going to be the vertical axis down the left-hand side, sometimes the horizontal axis. The easiest way to do it usually is by double-clicking. If you want to explore other options regarding formatting, remember you can start here as well too, exploring some of the many options under Effects that we see here, Shadow, Glow. Also under Fill options here for colors and lines and so on.
- Selecting data for a chart
- Creating charts with keystrokes and ribbon icons
- Selecting the right chart type
- Adding titles, labels, and gridlines
- Choosing a chart layout
- Changing a chart's location
- Adding gridlines, data tables, and trendlines
- Formatting charts
- Creating column, bar, line, pie, and scatter charts
- Changing a chart's source data
- Creating Gantt and frequency charts
- Printing and sharing charts
- Converting a chart to a graphic