When you summarize data in a chart, Excel adds markers and labels to the chart’s horizontal and vertical axes to help viewers interpret the chart’s contents. As with other elements of your chart, you can control the appearance of those markers.
- [Instructor] When you summarize data in a chart, Excel adds markers and labels to the chart's horizontal and vertical axes to help viewers interpret the chart's contents. As with other elements of your charts, you can control the appearance of those markers. I'll demonstrate how to make those changes in this movie. My sample file is the chart axes workbook, and you can find it in the chapter seven folder of the exercise files collection. The chart I'm working with here displays orders by category in year, and it's based on the data in the worksheet, some of which is obscured by the chart, but you can see that we have four different categories, and also values for 2016, 2017, and 2018.
The vertical axis displays the numbers, and those are the order counts for each of the years and each of the categories. And the x axis, or horizontal axis, has the category data. And those are batteries, landscape, lighting and light bulbs, and solar panels. If you want to format either of the axes, double click any value on that axis. So, for example, to work with the X-axis, I will double click batteries. And doing so displays the format axis dialogue box and you can see that I have access options below.
There are a number of options that I can use here. The first is to set the axis type based on the type of data and that usually works well automatically. I could, however, set it for text, which is what my values are, the category names. You can also select where the vertical axis crosses the horizontal axis. In this case, it's automatic and it happens at a zero, which you can see here, that's what the dash there means. Or I can have it cross at a category number, for example, the lowest value for solar panels which would be considered zero, or at the largest category.
I can also have the axis position, either between tick marks or on tick marks. The final option would be to display the categories in reverse order. So if I wanted to reverse alphabetical order, I could check that box and it would go solar panels down to batteries and you also notice that the axes labels removed to the right side. I'll clear that box. Obviously, I have text options available. So if I click that, you'll see that I can change the text fill, which would basically change its color and also the text to outline.
If I wanted to change the attributes of the text, for example by making it bold, then I could select either the entire axis or just one of the values and then use the controls on the home tab of the ribbon to format the text. Now, I'll close the format axis task pane here and I will double click the vertical or y axis. Doing so displays the format axis task pane, but you can see that I have different options.
The bounds which is the minimum number, in this case is zero, which seems reasonable and the maximum is 18,000. I also have major units which are set at 2,000 and those are the 2,000, 4,000, 6,000 and so on labels. I could also change the miner units. In this case, that would be too small of a ratio so I don't really worry about it. Let's say that instead of having major units be 2,000 I'll change it to 5,000 and press enter.
And now you see that I have values of 5,000, 10,000 15 and 20. The horizontal axis crosses are the same attributes I explained before. I can also, if I want, display units. It's currently set to none, however, I have options for hundreds and thousands, that sort of thing. So if I were to select thousands, then I would see five, 10, 15 and 20. In this case, I'll just set it back to none. I can also set tick marks. So if I click that option here and scroll down, then I can have major type, minor type.
For major type, let's say that I click and I say inside and there you can see that I have tick marks that just appeared in the chart area. And if I click on the chart area, I display the format chart area task pane so I double click on the vertical axis again. There we go. And I can change alignment and see. So I believe I'm currently here. If I change back to there and then tick marks, then I get major type and minor type.
I can also change the labels. By clicking there, I can change the position. In this case, it's set to next to axis. I can set it to high, low or none, let's set it to high. There we go. I can also change the number format. So if I click number, then I change it to custom, in this case, that's an accounting value, or a number value with no decimal points, or I can select from anyone of a number of built-in datatypes.
When I'm done formatting my axes, I can close the format axis task pane and I'm done. I usually find that the axes numbering and marking Excel creates by default serves my purposes well. If you want to change the appearance of your charts' axes, you can use the controls in the format axis task pane like I showed you.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks