How to change X axis values in Excel histogram

Most histograms made in Excel don’t look very good, because the category labels describing the bins are centered below the bars.

How to change X axis values in Excel histogram

In Histogram with Actual Bin Labels Between Bars I showed technique that centered bin edge values between the bars of a histogram.

How to change X axis values in Excel histogram

Much nicer.

In this tutorial I will go one further and show how to plot your histogram on a value-type horizontal axis.

Data for Histogram and Labels

Fifty random values between 4.95 and 6.45 are summarized in the table below. The gold shaded range is the list of bin values, corresponding to the value at the top of each bin. The blue shaded range is the result of the FREQUENCY function, which tells us the number of values below the first bin value, the number of values between each successive pair of bin values, and the number of values above the top bin.

How to change X axis values in Excel histogram

We can expand the column of bin values so we know the overall upper and lower limit of the data.

How to change X axis values in Excel histogram

By inspection of these upper and lower limits, coupled with decades of experience in labeling chart axes, I will use the following values for my histogram chart axis labels. The first column are the values, the second column is a bunch of zeros, because I will plot these points along the bottom of the chart, and hang the axis labels on them.

How to change X axis values in Excel histogram

Constructing the Histogram

Start by selecting the blue range in the data range above. Ignore any of the other data.

How to change X axis values in Excel histogram

Those are pretty spindly bars for a histogram. Change the gap width to a small number (10-15% works well). Purists will make their gap widths zero, but then you need a border around the bars. We’ll let the thin white gap serve as our border.

How to change X axis values in Excel histogram

Copy the labels data (the last data range above), select the chart, go to the Home tab of the Ribbon, click the down arrow on the Paste button (the Copy button in older Excel ribbons), and choose Paste Special. Add the data as a new series with rows in columns and with category (X) labels in the first column.

How to change X axis values in Excel histogram

Doesn’t look like much, but the values were zero, so the bars have no height. They did push the histogram bars aside to make room, though.

Select the added series (select the histogram bars and press the up arrow key), click the menu button (between the Alt and Ctrl buttons to the right of the space bar) to pop up the context menu, and choose Change Series Chart Type. Select XY Scatter with markers and no lines.

How to change X axis values in Excel histogram

Excel automatically puts this XY series onto the secondary axes, and draws the axes for us. Select the secondary (right) vertical axis, and choose Horizontal Axis Crosses at Automatic Position (zero).

How to change X axis values in Excel histogram

Select the primary (left) vertical axis, and choose Horizontal Axis Crosses at Maximum Axis Value.

How to change X axis values in Excel histogram

Select the primary (left) vertical axis and press the Delete key, to turn those histogram stalactites into stalagmites.

How to change X axis values in Excel histogram

Format the secondary (bottom) horizontal axis, and choose Vertical Axis Crosses at Automatic Position (zero).

How to change X axis values in Excel histogram

Format the primary (upper) horizontal axis so it uses no labels, no tickmarks, and no line.

How to change X axis values in Excel histogram

Format the secondary (horizontal) axis scale so its minimum and maximum are the same as the outermost bin limits, that is, 4.95 and 6.45. If the axis has visible tickmarks, change the tickmark settings to None.

How to change X axis values in Excel histogram

If you hide the secondary (horizontal) axis tick labels but choosing None for labels, Excel will remove the space below the chart for these labels. Instead, change the number format of the secondary (horizontal) axis to ”   ” (three spaces within double quotes) to preserve this spacing.

How to change X axis values in Excel histogram

Add data labels to the XY series. Use the Below option for position and X Value option for what the label contains.

How to change X axis values in Excel histogram

Finally, change the marker of this XY series to small plus symbols, and use a gray color that matches the axis line. These plus symbols now mimic axis tickmarks. Unfortunately there is no easy way to add tickmarks outside the plot area, which is my preference and should be yours, but we’ll put up with tickmarks that cross the axis just this once.

How to change X axis values in Excel histogram

And our new histogram is finished.

More About Histograms on the Peltier Tech Blog

I'm charting the frequency of a range of numbers in a histogram in Excel 365. The numbers range from 236 to 1736 and I've got the bins at 250. Excel is automatically starting the histogram bins at the 236-486 range. I don't see an option anywhere to make it start at 0-250. Can anyone help me change it?

Formatting options:

How to change X axis values in Excel histogram

What I want it to look like:

How to change X axis values in Excel histogram

The above was created in a different program that I have access to only through my school, using the same set of data. I'm trying to figure out how to do it in Excel because that's what I use in real life.

Here are a subset of my input data and the chart I get now:

How to change X axis values in Excel histogram

(Ignore Columns A and B.  Columns Y and Z represent the frequencies of the numbers in Column M; e.g., there are two values between 0 and 250, four values between 250 and 500, and so on.)