How to Change the Axis Scale on an Excel Chart

To change the axis scale in Excel, open Format Axis and type your own minimum, maximum and unit values instead of the automatic ones. It takes under a minute and can make a flat-looking chart readable, or stop a chart from exaggerating small changes. This guide shows the steps on Windows and Mac, how to pick sensible bounds from your data with MIN and MAX, and how to fix the common axis problems.

How to change the axis scale in Excel#

The axis scale is controlled from the Format Axis pane. Here are the steps for the vertical (value) axis:

1. Click the numbers on the vertical axis to select it.
2. Right-click and choose Format Axis. You can also press Ctrl+1 (Mac: ⌘1) with the axis selected, or double-click it.
3. In the pane, open Axis Options (the column chart icon).
4. Under Bounds, type a new Minimum and Maximum.
5. Under Units, type a Major value. This is the gap between gridlines and labels.
6. Press Enter after each value. The chart updates straight away.

When you type a value, the word Auto next to the box changes to Reset. Click Reset to give control back to Excel.

On a Mac, the steps are the same: Control-click (or right-click) the axis, choose Format Axis, and use the pane on the right. In Excel for the web, the Format pane may offer fewer options, depending on your version.

What each axis setting does#

The Axis Options section has several settings. These are the ones you'll use most:

Once you type a bound, it stays fixed. If next month's sales jump to 19,000 and your maximum is 17,000, the top of the line is cut off. Check the bounds whenever the data changes, or click Reset.

Pick good bounds with MIN and MAX#

Good bounds sit just outside your data and land on round numbers. You can work them out with formulas, then type the results into Format Axis.

Take six months of sales in A1:B7:

Round the smallest value down and the largest value up to the nearest 1,000:

=FLOOR(MIN(B2:B7),1000)
=CEILING(MAX(B2:B7),1000)

The results are 12,000 and 17,000. With a major unit of 1,000, the axis shows six neat labels and the line fills the chart. FLOOR rounds down to a multiple and CEILING rounds up to one. The MIN function guide and MAX function guide explain both functions, and the guide to AVERAGE, MIN and MAX shows them on a full summary.

The bound boxes don't accept formulas, so you can't link them to these cells. If the bounds must update on their own, leave them on Auto or use a macro.

Should the axis start at zero?#

For bar and column charts, yes. The length of a bar is the message. If the axis starts at 12,000, a bar for 13,000 looks twice as tall as one for 12,500, even though the difference is tiny. To fix it, type 0 in Minimum. The guide to bar charts in Excel covers this in more detail.

For line charts, starting above zero is often fine. Readers look at the shape of the line, not its height, and a tighter scale shows the ups and downs more clearly. Say in the title or axis label that the axis doesn't start at zero if readers might miss it. The line chart guide has more tips.

Show thousands or millions on the axis#

Long numbers like 15,000,000 crowd an axis. Use Display units instead of changing the data:

1. Open Format Axis > Axis Options.
2. Set Display units to Thousands or Millions.
3. Keep Show display units label on chart ticked, so readers know the scale.

To change how the numbers look, such as adding a currency sign or removing decimals, open Number further down the same pane and choose a format.

Use a logarithmic scale#

A logarithmic (log) scale makes each step 10 times bigger than the one before: 1, 10, 100, 1,000. It is useful when your values range from very small to very large, such as website visits per page.

In Format Axis > Axis Options, tick Logarithmic scale. The base is 10 by default. A log scale can't show zero or negative numbers, so Excel won't plot them correctly. Label the axis clearly, because many readers assume a normal scale.

Change the horizontal axis#

The horizontal axis on column and line charts is usually a category axis. It lists labels such as months in order, so it has no minimum or maximum. Instead you can:

• Set Interval between labels to show every second or third label.
• Tick Categories in reverse order to flip the order, which is handy on bar charts.
• Choose where the vertical axis crosses.

If the labels are real dates, set Axis Type to Date axis. Excel then gives you Bounds and a Base unit of days, months or years. For two number columns, use a scatter chart, whose horizontal axis is a value axis with full bounds.

When two series have very different sizes, such as revenue and margin %, one axis isn't enough. See how to add a secondary axis in a combo chart.

Common axis scale problems#

These are the issues that come up most:

1. Part of the line or bars is cut off. The maximum is too low. Raise it or click Reset.
2. The chart looks flat. The axis starts at 0 on a line chart with values close together. Raise the minimum.
3. Bounds don't change with new data. They were typed in. Click Reset, or update them.
4. Too many labels. Make the major unit bigger.
5. The axis numbers show many decimals. Change the number format under Number in Format Axis.

A chart is only as clear as the summary behind it. The guide to Excel chart basics covers choosing the right chart, and the pivot-style summary lesson shows how to build the totals a chart is made from with formulas.

Practise it#

Try the exercise on this page to find a neat axis minimum with FLOOR and MIN. Then practise AVERAGE, MIN and MAX in a live spreadsheet, and build a chart-ready summary table in the pivot-style summary lesson. Each lesson checks your formulas instantly.