How to Change the Intervals on an X-Axis in Excel
By Richard Gaughan
With each new version of Excel, the capabilities of the program grow. Frequently those improvements come in the form of semi-automated tasks -- common actions that Excel will do with limited intervention. For example, if you select a data range to plot, Excel will automatically draw the chart for you. But sometimes all those built-in capabilities don't give you what you want and you need to take control. When you don't want to accept Excel's default chart formatting options, a few simple steps will let you take charge of the X-axis intervals. The procedure is slightly different for scatter plots and all other types of plot.
Select the Plot. Move the cursor over the X-axis. A pop-up description should appear saying "Horizontal (Value) Axis." Double-click it.
Select the "Scale" option from the list on the left-hand side of the "Format Axis" dialogue box that appears.
Enter your desired label interval in the "Major unit" field. Click "OK" or press "Return" to accept your new value and look at the modified chart.
Repeat Steps 2 and 3 until the plot looks the way you want.
Other Chart Types
Enter your desired labels in a column that matches the length of your data.
For example, if your data is in B2 through B22, put labels in cells from A2 through A22. Those labels could be numbers like "0, 5, 10..." or names like "Asquith, Attleboro, Chrazny..." or any other appropriate identifiers.
Select the chart, then select the "Source Data" option from the "Chart" drop-down menu.
Click in the "Category (X) axis labels" field, then select your column of labels. Click "OK" or press "Return" to look at your chart.
Double-click the horizontal axis of the chart you want to select.
Select the "Scale" option from the menu, then enter your desired separation between labels in the "Interval between labels" field. Select "OK" or "Return" to look at your new chart. Repeat steps 4 and 5 until the chart looks the way you want it to.
The "Format Axis" dialogue box also allows you to change the interval and appearance of tick marks, the font of your labels and other aspects of the appearance of your chart.
When working with non-scatter plots, Excel's default labels are just the integers from 1 up to the number of data points you have. If you're happy with those labels, but want to change how many appear on the chart, you can start at Step 4 of the "Other Chart Types" instructions.
First published in 1998, Richard Gaughan has contributed to publications such as "Photonics Spectra," "The Scientist" and other magazines. He is the author of "Accidental Genius: The World's Greatest By-Chance Discoveries." Gaughan holds a Bachelor of Science in physics from the University of Chicago.