Quick, clean, and to the point

How to customize axis labels

In this video, we'll look at some examples of formatting axis labels.

Here we have a simple set of generic shipping data. We have a date, quantity, and a field to indicate batch number.

Let me insert a standard column chart, and let's run through some options in adjusting the labels that appear in the horizontal category axis.

The first thing you probably notice is that there are some gaps in the columns.

This happens because Excel automatically sets the axis type to date, which makes sense since we have dates in the data.

So, the first thing I'll do is set the axis type to text. This immediately gets rid of the gaps, since Excel is no longer plotting these dates across the full date range.

The dates still appear, but now they're plotted at equal intervals.

Now let's customize the actual labels. Let's say we want to label these batches using the letters A though F.

You won't find controls for overwriting text labels in the Format Task pane. Instead you'll need to open up the Select Data window.

Here you'll see the horizontal axis labels listed on the right. Click the edit button to access the label range. It's not obvious, but you can type arbitrary labels separated with commas in this field. So I can just enter A through F.

When I click OK, the chart is updated.

So that's how you can use completely custom labels. But, since we have some suitable labels in the batch column, we could just use those instead. I just need to use select data again and point to that range.

Next, I'm going to create a new label that concatenates the batch with the date. For this, I'll use the TEXT function and the ampersand for concatenation.

Then I'll update the chart to use that label instead.

Now, on the vertical axis, one change we can make is to use commas for thousands.

To make this change, format the axis and go to the Number area, then apply a number format with commas for thousands, and no decimal places.

Finally, I'll select the chart, and bump up the font size.

This affects all text labels at the same time.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.