Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.


Download Practice Workbook

You can download the practice workbook from here.


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.

how to break the axis scale in excel


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.

format data series to break the axis scale in excel

  • Then, in the new window, select Secondary Axis.

new secondary axis to break the axis scale in excel

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

format axis to break the axis scale in excel

  • 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.

Read More: How to Change Axis Scale in Excel (with Easy Steps)


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.

before formula to break the axis scale in excel

  • Now, go to cell E5 and type in the following formula:
=IF(C5>1000,100,NA())
  • Then, press Enter and copy this formula below.

break formula to break the axis scale in excel

  • Similarly, double-click on cell F5 and enter this formula:
=IF(C5>1000,C5-6000-1,NA())
  • Also, copy this formula in column F.

after formula to break the axis scale in excel

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

creating chart to break the axis scale in excel

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

format data series to break the axis scale in excel

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

format color to break the axis scale in excel

  • Now, make a table as in the image below.

dummy axis data to break the axis scale in excel

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

select data to break the axis scale in excel

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

adding data to break the axis scale in excel

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

edit series to break the axis scale in excel

  • 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.

Read More: How to Change Y Axis Scale in Excel (with Easy 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.

format axis to break the axis scale in excel

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

maximum point to break the axis scale in excel

  • 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.

Read More: How to Scale Time on X Axis in Excel Chart (3 Quick Methods)


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.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo