4. Formatting the Horizontal Axis

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

The horizontal axis needs to be adjusted when you have many datapoints in your dataset. In this lesson, learn how to format the axis to cater for an oil shareprice chart.

Lesson Notes

When to format the horizontal axis

- Formatting this axis is typically needed when we have many data points
- Excel's automatic efforts at labelling the horizontal axis are often unreadable in these cases
- To make an axis more readable, reduce the number of axis entries and re-format the number type

Reducing the number of axis entries

1 Double-click on the axis to bring up the Format Axis dialog box
2 Change the major unit to fixed and increase its value
3 Exit from the dialog box and observe the impact of your changes

Change the number format of the axis entries

1 Double-click on the axis to bring up the Format Axis dialog box
2 Click the Number tab on the left-hand menu
3 Select a pre-made number type or create a custom type that suits your needs
4 Exit from the dialog box and observe the impact of your changes

Keyboard shortcuts

CTRL + Pg Down: Move to sheet on the right
CTRL + : Move to the last cell in the data region
SHIFT + : Select next cell
CTRL + SHIFT + : Select all cells within data region
ALT + N , N: Create line chart

Transcript

Formatting the Horizontal axis is typically only needed when we have many data points. In this example, we only have 11 entries in the horizontal axis and so there is no need for any wholesale changes.

However, when we have many data points, say when we're plotting stock prices or commodity prices, then we'll often need to make adjustments. In the Line Chart tab, I have a series of commodity prices for oil. From the 3rd of January 2011, all the way to the 6th of January 2014.

I have 760 data points which I'm going to plot on a line chart. So let's select the data with Ctrl + Shift + down arrow and then Alt + N N for a Line chart. And I'll select a 2D option.

I'll now make this chart bigger. And as you can see the horizontal axis is far from ideal. It doesn't include all of the entries in the horizontal axis, only the third day of each month. Because the first entry of my dataset was the 3rd of January, 2011.

Because there are so many data points, the entries are also written vertically which makes it very hard to read. What I'd like to do is reduce the number of entries in the Horizontal axis and also change to a more readable format. Let's start by double clicking on the Horizontal axis. And we'll change the major unit. And I'll change this to every two months which will have the number of entries in the horizontal axis.

When I click close, we can see that the axis text is now at 45 degrees and is easier to read. Now let's change the format of each entry.

I'll double click on the axis again and this time, I'll go to the number option. And here under custom, I'll select mmm and yyyy.

And this format converts our date into the three-letter abbreviation for the month and the year in question. When I click Close, you can see that again, this improves the readability of the horizontal axis. This doesn�t solve the problem on its own, however. And another change we could make would be to reduce the size of the font for all of these entries. To do this, we can go to the Home tab in the Ribbon and reduce the size of the font to say, 7. but unfortunately, they're too small to be read easily. So I'll undo with Ctrl + Z. The final option and probably the most effective way of having horizontal entries is to go to the Format Axis dialogue box again and increase the Major unit to 3 months. This reduces the number of entries in the horizontal axis, so the remaining entries can now be read horizontally. As you can see, this is now much more readable than the initial horizontal axis entries we had when first creating the chart. Once you're happy with your horizontal axis entries, you may want to add some grid lines for these entries. To do this, select the chart, go to Gridlines and select Primary Vertical Gridlines, selecting the Major Grid Lines. For most line charts, you'll need to make these adjustments to the horizontal axis. And just remember that by changing the major unit and also changing the format of the date entry, you can make the axis much more readable for your audience.