How to Break Axis Scale in Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working in Excel, we may need to break axis scale in the charts. Charts provided by Excel become difficult to see when certain data is exceptionally huge compared to the rest. Because Excel does what it should and displays all the data points in a single chart. The smaller data points in the chart shrink dramatically because one data point is significantly greater than the others. Therefore, we must break the axis scale to make our charts more visible in these kinds of circumstances. In this article, we will learn about 3 simple methods to break axis scale in Excel.


How to Break Axis Scale in Excel: 3 Suitable Ways

In Excel, there is no standard technique to break axis scale. However, we have listed some of the best methods for doing it. These procedures are a little lengthy. But do not sweat. Each step is clear-cut and simple to carry out.

Not to mention that we have used the Microsoft Excel 365 version for this article; you can use any other version according to your convenience.


1. Adding a Dummy Axis

Adding a dummy axis is a smart option to break the axis in Excel. Let’s say, you want to show Sales of different Products of a store. But you noticed that Sales of Custom PC are way higher than that of the others. For this reason, you decided to break axis scale to represent them properly in a single chart. Let’s follow the steps mentioned below to do this.

how to break axis scale in excel

Step 01: Adding Break Value and Restart Value

  • Firstly, create 3 new columns named Before, Break, and After respectively.
  • Following that, name 2 cells as Break, and Restart. In these 2 cells, we will store the Break Value and our Restart Value.

Adding a Dummy Axis to break axis scale in excel

  • Now, enter the Break Value in cell C11. It is the value, from where the column will start to break. Here, we have used $800 as the Break Value.
  • Similarly, enter the Restart Value in cell C12. This is the value where the break ends. In this case, we used the Restart Value as $1900.

Step 02: Using Formula to Prepare Dataset

Here, we will use the IF function of Excel to prepare our dataset to break axis scale.

  • Firstly, use the following formula in cell D5.
=IF(C5>$C$11,$C$11,C5)

Here, cell C5 refers to the cell of the Sales column, and cell $C$11 indicates the cell of Break.

  • Then, press ENTER.

Using Formula to Prepare Dataset to break axis scale in excel

Consequently, you will see the following output on your worksheet.

  • Subsequently, use the AutoFill option of Excel to get the rest of the outputs.

Using AutoFill feature to break axis scale in excel

  • Following that, enter the formula given below in cell E5.
=IF(C5>$C$11,400,NA())
  • Next, hit ENTER.

As a result,  you will have the following output as shown in the following picture.

  • Now, use the AutoFill feature of Excel and you will have the remaining outputs.

Using AutoFill option to break axis scale in excel

  • Subsequently, use the following formula in cell F5.
=IF(C5>$C$11,C5-$C$12,NA())

Here, cell $C$12 refers to the cell of Restart.

  • Next, press ENTER.

Consequently, you will have the output for the first cell of the column named After.

Then, use Excel’s AutoFill feature to get the rest of the outputs.

Using AutoFill feature to break axis scale in excel

Step 03: Inserting Column Chart

  • Firstly, press CTRL and select the data of columns named Product, Before, Break, and After respectively.
  • After that, go to the Insert tab from Ribbon.
  • Then, choose the Insert Column or Bar Chart option.
  • Next, select the Stacked Column option from the drop-down.

Inserting Column Chart to break axis scale in excel

As a result, you have a Stacked Column Chart as shown in the following image.

Step 04: Formatting the Chart

  • Firstly, rename the Chart Title as shown in the image below. Here, we used Adding Dummy Axis as our chart title.

Formatting the Chart to break axis scale in excel

  • After that, click on the Chart Elements options and uncheck the box of Gridlines.

Subsequently, the gridlines from the chart will be removed.

Step 05: Creating Break in Chart

  • Firstly, right-click on the marked region of the following image.
  • Then, choose the Format Data Series option.

Creating Break in Chart to break axis scale in excel

As a result, the Format Data Series dialogue box will open on your worksheet.

  • Now, select the Fill & Line tab from the Format Data Series dialogue box.
  • Then, choose the No fill option under the Fill Section.
  • After that, in the Border section, select No line.

Editing Format Data Series dialogue box to break axis scale in excel

Consequently, a break will appear in your chart as demonstrated in the following image.

  • Similarly, right-click on the marked region of the following picture and select Format Data Series.

  • Now, go to the Fill & Line tab in the Format Data Series dialogue box.
  • Then, choose Solid Fill in under the Fill section.
  • Following that, click on the Color option and choose your preferred color from the drop-down.

Subsequently, you will have the following output on your worksheet as shown in the following image.

Step 06: Constructing New Y Axis

  • Firstly, create a table on your worksheet as shown in the following image.

Constructing New Y Axis to break axis scale in excel

  • Now, right-click on anywhere inside the chart area and choose the Select Data option.

Consequently, the Select Data Source dialogue box will open.

  • Now, choose the Add option from the Select Data Source dialogue Box.

As a result, the Edit Series dialogue box will be available, as shown in the following image.

  • Then, click on the Series name box and choose the cell that contains New Y Axis as marked in the following image.
  • After that, click on the Series values box and select the range D17:D25.
  • Now, click on OK.

  • As a result, it will redirect you to the Select Data Source dialogue box and select OK.

Consequently, a new set of column charts will be added to your Stacked Column Chart as shown in the following image.

  • Now, right-click on any section of the newly created chart and choose the Change Series Chart Type option.

Subsequently, the Change Chart Type dialogue box will open as shown below.

  • Now, in the Change Chart Type dialogue box, click on the drop-down icon beside the New Y Axis series name.
  • Then, choose the Scatter with Straight Lines option in the X Y Scatter section.
  • After that, click on OK.

As a result, you will have a straight line going through the center of the chart area as shown in the following image.

  • Now, right-click on the straight line and choose the Select Data option.

  • Following that, select the New Y Axis option as marked in the following image.
  • Next, click on the Edit option.

Subsequently, the Edit Series dialogue box will open on your worksheet.

  • Next, in the Edit Series dialogue box, click on the Series X values box and choose the range C17:C25.
  • Then, click on OK.

  • After that, you will be redirected to the Select Data Source dialogue box and select OK.

Consequently, you will have the New Y-Axis as demonstrated in the following picture.

Final output of step 6 of method 1 to break axis scale in excel

Step 07: Editing New Y-Axis

  • Firstly, right-click on the newly created Y axis and select the Format Data Series option.

Editing New Y Axis to break axis scale in excel

  • After that, go to the Fill & Line tab in the Format Data Series dialogue box.
  • Then, choose Solid Line under the Line section.
  • Following that, click on the Color option and choose your preferred color from the drop-down.

As a result, the color of your Y axis will be changed to your selected color like in the following image.

Step 08: Adding Labels to New Y-Axis

  • Firstly, click on any portion of the chart area.
  • Following that, go to the Chart Design tab from Ribbon.

Adding Labels to New Y Axis to break axis scale in excel

  • Subsequently, select the New Y-Axis.
  • Now, from the Chart Design tab, choose the Add Chart Element option.
  • Then, select the Data Labels option from the drop-down.
  • After that, choose the Left option.

Consequently, labels will be added to the left of the New Y-Axis as shown in the image below.

  • Now, click on the top label as marked in the following picture.
  • Then, go to the Formula Bar and type in =.
  • Following that, select cell B25 as it is the highest value.

Editing text boxes to break axis scale in excel

Subsequently, the top label will be changed as shown in the image given below.

  • Use the same steps to change the remaining labels above the break line as marked in the picture demonstrated below.

  • Now, select the label beside the break line and press DELETE.

  • Following that, select the axis of the chart as marked in the following image.
  • Then, hit DELETE from your keyboard.

Consequently, you will have a chart that has a broken axis scale as demonstrated in the picture below.

Final output of method 1 to break axis scale in excel


2. Using Format Shape Option

Using the Format Shape option is one of the easiest methods to break axis scale in Excel. For instance, let’s say you need to show the Monthly Sales of XYZ Company. But the Sales of 1 month is unusually large than the others. So, you decided to break axis scale to display all the Sales data in a chart. Let’s use the steps mentioned below to do this.

Using Format Shape Option to break axis scale in excel

Step 01: Inserting Column Chart

  • Firstly, create a new column named Adjusted Sales.

Note: In the Adjusted Sales column, enter exactly the values of the Sales column except for the cell of large Sales amount. For that cell, enter a value close to the maximum value of the remaining cells. Here, we used the value of $800.

Inserting Column Chart to break axis scale in excel

  • Following that, press CTRL and select the cells of the columns Month and Adjusted Sales.
  • Then, go to the Insert tab from Ribbon.
  • After that, choose the Insert Column or Bar Chart option.
  • Next, select the Clustered Column option from the drop-down.

Consequently, you will have the following output on your worksheet.

Final output of step 1 of method 2 to break axis scale in excel

Step 02: Formatting the Chart

  • Now, use the steps mentioned in Step 04 of the 1st method to format the chart.

Formatting the Chart to break axis scale in excel

Step 03: Inserting and Formatting Shape

  • Firstly, go to the Insert tab from Ribbon.
  • After that, choose the Shapes option.
  • Then, select the Parallelogram shape from the drop-down.

Inserting and Formatting Shape to break axis scale in excel

  • Now, left-click and then hold and drag your mouse to specify the size and shape of the parallelogram shape.

  • After that, select the shape to make the Shape Format tab visible.
  • Then, go to the Shape Format tab from Ribbon.
  • Subsequently, choose the Shape Fill option.
  • Now, choose the White color as shown in the following image.

  • Following that, again go to the Insert tab and choose the Shapes option.
  • Then, select the Line option.

  • Now, draw a line over the parallelogram as shown in the following image.

  • Now, click on the line and go to the Shape Format tab from Ribbon.
  • After that, select the Shape Outline option.
  • Then, choose the Black color.
  • Next, click on the Weight option and select the 2¹/⁴ pt option as marked in the following picture.

Formatting shape to break axis scale in excel

As a result, you have a dark black line on top of your parallelogram shape.

  • Now, copy the line and paste it onto your worksheet.
  • Then reposition the lines on the two opposite sides of the parallelogram as shown in the image below.

  • Following that, click on the parallelogram shape and go to the Shape Format tab from Ribbon.
  • Subsequently, choose the Shape Outline option.
  • Then, select the No Outline option.

  • After that, press CTRL and select the lines and the parallelogram shape altogether and do a right-click.
  • Now, select the Group option so that you can move and resize them as a unit according to your needs.

Final output of step 3 of method 2 to break axis scale in excel

Step 04: Repositioning Shape to Break Axis

  • Firstly, click on the rotate option as marked in the following image and rotate the shape.

Repositioning Shape to Break Axis in Excel

  • Then, reposition the shape on the large column so that it looks like a break in the column.

  • Then, copy and paste the shape on your worksheet.
  • After that, resize the copied shape and reposition it between the two labels named $500 and $600.

Final output of step 4 of method 1 to break axis scale in excel

Step 05: Inserting and Formatting Text Boxes to Add Label

  • Firstly, go to the Insert tab from the Ribbon.
  • Then, choose the Shapes option.
  • After that, select the Text Box option from the drop-down.

Inserting and Formatting Text Boxes to Add Label to break axis scale in excel

  • Now, click on the Text Box and type in $1600 as shown in the following image.

  • Next, select the Text Box and go to the Shape Format option from Ribbon.
  • Subsequently, choose the Shape Fill option.
  • Then, choose the White color as marked in the picture given below.

  • Now, reposition the text box so that the label from the axis of the chart gets hidden.

If you can do it correctly, you will see the following output on your worksheet as shown in the image below.

  • Similarly, add 3 more text boxes by following the same steps to get the following output.

Congratulations! You have done all the steps to successfully break axis scale in Excel and your final output should be looking like the following picture.

Final output of method 2 to break axis scale in excel


3. Overlapping 2 Column Charts

Overlapping 2 column charts is another smart way to break axis scale in Excel. In this method, we will use the same dataset that we used in method 2. Let’s use the steps discussed in the following section.

Step 01: Preparing Dataset to Break Axis

  • Firstly, create a new column named Outlier as shown in the following image.

Overlapping 2 Column Charts to break axis scale in excel

  • After that, enter the following formula in cell D5.
=IF(C5=MAX($C$5:$C$11),C5,NA())

Here, cell C5 refers to the cell of the Sales column and the range $C$5:$C$11 represents all of the cells of the Sales column.

  • Now, hit ENTER.

Preparing Dataset to Break Axis in Excel

As a result, you will have the following output on your worksheet.

  • Subsequently, you can get the remaining outputs by using the AutoFill option of Excel.

Final output of step 1 of method 3 to break axis scale in excel

Step 02: Inserting 2 Column Charts

  • Firstly, select the cells of the columns named Month and Sales.
  • Then, go to the Insert tab from Ribbon.
  • Following that, click on the Insert Column or Bar Chart option.
  • Now, select the Clustered Column option from the drop-down.

Inserting 2 Column Charts to break axis scale in excel

Consequently, the following Column Chart will be visible on your worksheet.

  • At this stage, follow the steps used in Step 04 of the 1st method to format the chart.

  • Following that, press CTRL and select the columns named Month and Outlier.
  • Next, go to the Insert tab from Ribbon.
  • After that, click on the Insert Column or Bar Chart option.
  • Then, select the Clustered Column option from the drop-down.

As a result, you will have the following chart which shows only the value which is unusually large.

final output of step 2 of method 3 to break axis scale in excel

Step 03: Editing and Repositioning 2 Column Charts

  • Firstly, reposition the Outlier chart on top of the first chart in such a way that it looks like the following image.

Editing and Repositioning 2 Column Charts to break axis scale in excel

  • Now, select the Horizontal Axis of the Outlier chart and hit the DELETE key from your keyboard.

  • Following that, right-click on the Vertical Axis of the Outlier chart and select the Format Axis option.

Subsequently, the Format Axis dialogue box will open on your worksheet as shown in the following image.

  • Now, in the Format Axis dialogue box, go to the Axis Options tab.
  • After that, in the Minimum box, type in 1200, and in the Maximum box, type in 1800.
  • Under the Units section, in the Major box, type in 200.

As a result, you will get the following output as shown in the following image.

  • After that, resize the Outlier chart by dragging the marked portion of the following image in such a way so that the gap between the labels of the Outlier chart matches with the first chart.

Subsequently, your combined chart should look like the following image.

  • Then, right-click on the marked portion of the following image.
  • After that select the Format Data Series option.

  • Subsequently, from the Format Data Series dialogue box, go to the Fill & Line tab.
  • Then, under the Fill section, choose the Solid Fill option.
  • Next, click on the Color option and choose your preferred color from the drop-down.

Consequently, the top part of the combined column chart will look like the image demonstrated below.

final output of step 3 of method 3 to break axis scale in excel

Step 04: Formatting the Combined Column Charts

  • Firstly, right-click on the marked area of the following image.
  •  Then, click on the Outline option.

Formatting the Combined Column Charts to break axis scale in excel

  • Following that, select the No Outline option from the drop-down.

Consequently, the outline from one chart will be removed as shown in the following image.

  • Follow the same steps to remove the outline from the other chart.

  • After that, click on the Chart Elements option.
  • Then, check the box of Data Labels.

Subsequently, you will have the following final output on your worksheet.

Final output of method 3 to break axis scale in excel


How to Break X-Axis in Excel Scatter Plot

While working in Excel, sometimes we need to break x-axis in Excel scatter plot. A Scatter plot is a diagram that displays the relation between 2 variables. In Excel, we can create a scatter plot quite easily. But if one data of the x-axis of the scatter plot becomes unusually big compared to the others then it becomes quite difficult to show all the data points in a compact chart. To solve this issue we can break the x-axis in Excel scatter plot.

Let’s say you have to create a Profit vs Sales scatter plot diagram. But there is one data in the Sales column that is unusually large. So, we will break the x-axis here. Let’s use the steps demonstrated below.

How to Break X-Axis in Excel Scatter Plot

Step 01: Using Formula to Prepare Dataset

  • Firstly, create a new column named Outlier in the given dataset.
  • After that, enter the following formula in cell D5.
=IF(B5=MAX($B$5:$B$10),B5,NA())

Here, cell B5 represents the cell of the Profit column, and the range $B$5:$B$10 refers to the range of the cells of the Profit column.

  • Now, press ENTER.

Using Formula to Prepare Dataset to break axis scale in excel

Consequently, you will see the following output on your worksheet.

  • Then, use the AutoFill feature of Excel to get the remaining outputs as shown in the following image.

final output of step 1 to break axis scale in excel

Step 02: Inserting First Scatter Chart

  • Firstly, select the columns named Profit and Sales and go to the Insert tab from Ribbon.
  • Following that, select the Insert Scatter (X, Y) or Bubble Chart option.
  • After that, choose the Scatter option from the drop-down.

Inserting First Scatter Chart to break axis scale in excel

As a result, you will have a Scatter Chart like in the following picture.

Step 03: Formatting First Scatter Chart

  • Firstly, follow the steps mentioned in Step 04 of the 1st method to format the chart.

  • Next, double-click on the rightmost data point of the chart.
  • After that, right-click on the data point.
  • Subsequently, choose the Format Data Point option.

As a result, the Format Data Point dialogue box will open as shown in the following image.

  • Then, in the Format Data Point dialogue box, go to the Fill & Line tab.
  • Following that, choose the Marker option.
  • Next, select the No fill option under the Fill section.
  • Now, choose the No line option under the Border section.

Consequently, you will see that the rightmost data point is no longer visible on your worksheet like in the image below.

Final output of step 3 to break axis scale in excel

Step 04: Inserting Second Scatter Chart

  • Firstly, select the columns named Profit and Outlier and follow the steps mentioned in Step 02 of this method to get the following scatter chart,

Inserting Second Scatter Chart to break axis scale in excel

Step 05: Formatting Second Scatter Chart

  • Firstly, use the steps used in Step 04 of the 1st method to format the chart.
  • After that, resize and reposition the second chart on top of the first chart.
  • Then, right-click on anywhere on the chart area of the second chart.
  • Following that, click on the Fill option.
  • Now, select the No Fill option from the drop-down.

Formatting Second Scatter Chart to break axis scale in excel

Subsequently, you will see the following output on your worksheet.

  • Next, select the vertical axis as shown in the following image, and then press DELETE from your keyboard.

As a result, the vertical axis will be removed from the chart like in the image below.

Similarly, remove the horizontal axis and your chart will look like the following picture.

Final output of step 5 to break axis scale in excel

Step 06: Adding Text Box and Break Shape

  • Following that, use the steps mentioned in Step 05 of method 2 to add text boxes as shown in the following image.

Adding Text Box and Break Shape to break axis scale in excel

  • Subsequently, follow the procedure discussed in Step 03 of method 2 to add the break shape to your chart.

  • Finally, resize and reposition the shape like in the following picture.

Consequently, you have your desired broken X-Axis in Excel Scatter Plot.


Download Practice Workbook


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to break axis scale in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy learning!


Related Articles


<< Go Back to Excel Axis Scale | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo