To recap, a value axis is used to plot numeric data on a scale, while a category axis is used to group text or dates into specific categories. In a previous video, we created a line chart to show average 30 year mortgage rates over a 5 year period. We already customized the value axis, so let’s make some changes to the horizontal category axis. First, let me point out that axis options are different depending on which axis type is selected. With the vertical axis selected, we see value axis settings. When I select the horizontal axis, we see category axis settings. Both value and category axes have settings grouped in 4 areas: Axis options, Tick marks, Labels, and Number. The axis type is set to automatic, but we can see that it defaults to dates, based on the bounds and units Excel has set as defaults. Notice bounds have been set automatically based on the 5-year date range, and units are set to years. If I temporarily switch to text, things change dramatically. I’ll undo that. To illustrate how this works, let me create another chart with a smaller set of the same data, but only include 1 year. Now when I check the axis settings for the horizontal axis, notice that units are now set to months, to better fit this range of data. I’ll go ahead and delete the extra chart Back in the first chart, let’s clean things up on the horizontal axis. First, I’ll change the labels to years using number formatting. Just select custom, under Number. Then enter yyyy. That gives us years on the axis, but notice this somehow confuses the Unit settings. To fix, just switch units to something else, then back again to 1 year. So, now we can see some other problems. The data starts in June so that’s where Excel has started the axis. To make the year-based axis work better, I’ll set the start to January 1, 2012. Now I can set the end date to January 1, 2018. These changes shift the line to fit date range shown in the horizontal axis. Finally, I’ll to bump up the text size of both axes at the same time by right-clicking the chart, and using the font menu to change the type size to 12 points.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
