How to Break the Axis Scale in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, I am going to show you 3 effective ways to break the axis scale in excel. You will need these methods quite often while working with various excel datasets. Also throughout this tutorial, you will learn some valuable formulas and techniques which will be helpful in other excel related tasks.

Table of Contents hide

Download Practice Workbook

What Is Axis Break in Excel?

3 Effective Ways to Break the Axis Scale in Excel

1. Using Secondary Axis in Chart

2. Adding Dummy Axis

3. Duplicating First Graph

Conclusion

Download Practice Workbook

You can download the practice workbook from here.

Break the Axis Scale.xlsx

What Is Axis Break in Excel?

In a dataset, if there are any data points that are out of the ordinary range by a big margin, then some sections of the chart will not be visually precise. In this situation, it is better to break the vertical axis into small parts to present all the sections clearly on the chart.

3 Effective Ways to Break the Axis Scale in Excel

We have taken a relatively concise dataset to explain the steps clearly. The dataset mainly consists of the Product column and their Quantity or Amount column. There are also approximately 6 rows in this dataset.

1. Using Secondary Axis in Chart

In this method, we will use a secondary axis to break the vertical axis in excel. Let us see how to do this.

Steps:

First, right-click on the lower data series and select Format Data Series.

Then, in the new window, select Secondary Axis.

Now, right-click on the vertical axis and click on Format Axis.

Then, set the Maximum value to 160.

Next, in the Number section, insert [<=80]0;;; as Format Code.

Finally, this will break the vertical axis scale into two sections.

2. Adding Dummy Axis

We can also break the axis scale in excel using an additional dummy axis. Although this method is somewhat long, it is great to understand some key concepts. Follow the steps below to achieve this.

Steps:

To begin with, go to cell D5 and insert the below formula:

=IF(C5>1000,1000,C5)

Here, press Enter and copy the formula to the cells below using Fill Handle.

Now, go to cell E5 and type in the following formula:

=IF(C5>1000,100,NA())

Then, press Enter and copy this formula below.

Similarly, double-click on cell F5 and enter this formula:

=IF(C5>1000,C5-6000-1,NA())

Also, copy this formula in column F.

Now, select cells B4 to B10 and cells D4 to F10.

After that, right-click on the portion of the chart as in the image below and select Format Data Series.

Now, in the new window, click on Fill & Line and select White as the Color.

Now, make a table as in the image below.

Then, right-click on the chart and click Select Data.

Next, in the Select Data Source window, click on Add.

Here, in the Edit Series window, insert the following value and click OK.

Now, right-click on the new data series chart and select Change Series Chart Type.

After that, in the new window, go to Combo then For Broken Y Axis.
Here, from the drop-down select Scatter with Straight Lines and click OK.

Next, right-click on the new series line and choose Select Data.

Then, in the new window, select For Broken Y Axis and click Edit.

Now, insert the following Series X values in the Edit Series window and click OK.

As a result, this will give us a linear vertical series.

Next, while still having the vertical line selected, go to Add Chart Elements from the Chart Design tab which should be just beside the help tab.
Now, under Data Labels select Left.

Here, select the old vertical axis and press Delete.

Finally, you should break the axis scale after doing all the previous steps.

3. Duplicating First Graph

One of the simplest ways to break the axis scale in excel use to actually use a duplicate chart alongside the original chart. Let us see how we can do this.

Steps:

To start with, right-click on the vertical chart axis you have and select Format Axis.

In the new window, set the Maximum value as 15.

Next, resize the existing chart using the handles as in the image below.

Now, press Ctrl+D to make a duplicate of the existing chart and for that set the minimum value as 30.
Then, place the duplicate chart on top of the old chart.

Finally, to notify the break in axis, insert any shape into the empty space between the two vertical axes.

Conclusion

I hope that you were able to apply the methods I showed in this tutorial to break the axis scale in excel. Try to choose the method that best suits your needs. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.