Break Chart Axis – Excel

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on June 10, 2022

This tutorial will demonstrate how to create a break in the axis on an Excel Chart.

How to Break Chart Axis in Excel

Break a Chart with a Secondary Axis in Excel

Starting with your Data

In this tutorial, we’re going to show how to create a graph with a secondary axis. We’ll start with the example data below.

Data for Break in Chart Excel

 

Adding a Graph

  1. Highlight the Data to Graph
  2. Click on Insert
  3. Select Graphs
  4. Click on the first Bar Graph

Insert Bar Chart in Excel

 

Adding a Secondary Graph

  1. Right click on the graph
  2. Select Change Chart Type

Change Chart Type Secondary Graph Excel

 

3. Click on Combo

4. Change both Series to Line Graph

5. Check one of the Series for Secondary Axis

Customize Data Break in Excel Secondary Data

 

Changing Axis

  1. Right click on the Primary Axis
  2. Select Format Axis

Format Axis for Secondary Axis in Excel

 

3. Increase the Maximum Axis to bring this line to the bottom of the graph (decrease it in your trying to bring it to the top of the graph)

Adjust Maximum Bound for Secondary Axis in Excel

 

 

4. Decrease the Minimum Axis to bring this line to the top of the graph (increase it in your trying to bring it to the top of the graph)

Adjust Minor Bound in Secondary Axis in Excel

 

Final Graph with Secondary Axis

Final Graph with Secondary Axis Break in Excel

 

Break Axis on a Chart in Excel

If you have data that has a large swing in the numbers, the graph doesn’t always show it well. Instead, we want to show a break in the axis so that we can show the graphs easier.

Break Axis on Chart in Excel Graph

 

Create Axis in Graph

  1. Next to the original data, add a column for Before, Break, and After.
  2. Create a Table for the Max Number, where the new axis will Restart, where the axis will Break, and the Min number

Create Table for Before Break and After for Break Axis Excel

 

Fill in Table

Before: =MIN($C35,$C$52); Finding the minimum value between the Actual Item Sold and the Break

Before Formula for Excel for Broken Axis Excel

 

Break: =IF(C35>$C$52,125,NA()); Finding which items will go after the break. 125 signifies how large the break is

Break Formula for Excel for Broken Axis Excel

 

After: =IF(C35>$C$52,C35-$C$51-1,NA()); This is how much of the axis after the break will appear

After Formula for Excel for Broken Axis Excel

 

Create a Chart

  1. Highlight First Column
  2. Hold down Control and highlight data for Before, Break, and After
  3. Click Insert
  4. Select Bar Graph
  5. Click Stacked Bar Graph

Create Stacked Bar Graph from Table Axis Break Excel

 

Format Series

  1. Click on Middle Portion (This will be the Split)
  2. Select Format

Format Break in Axis for Stacked Bar Graph Excel

 

3. Change Shape Fill & Shape Outline to No Fill

Format Color for Axis Break in Excel Graph

 

Creating Dummy Axis

Create a table with the following information:

  • Labels: Create the Axis that you would like to show with the break in it
  • Xpos: Fill in .25 for the break
  • YPos: Create the current Y Axis Labels

Create Dummy Axis Data in Excel for Axis Break

Add Dummy Data to Graph

  1. Right click on graph
  2. Click Select Data

Select Data for Axis Break in Excel Graph

 

3. Click Add

Add Dummy Data for Axis Break in Excel

 

4. For the Series Name, select “For Broken Y Series”

5. For Series Value, select value under YPos

Update Y Axis for Break in Excel Graph

6. Right click on new series

7. Click on Change Series Chart Type

Change Series for Chart Break in Excel Graph

 

8. For Series called “For Broken Y Series”, click box

9. Select Scatter with Straight Lines

Create Scatter Straight Lines Break Dummy Data Y Axis Excel

 

Change Axis

  1. Right Click new Line
  2. Click Select Data

Select Data for Break Axis Y Axis in Excel

 

3. Select “For Broken Y Series”

4. Click Edit

Broken Y Series Axis to Break Axis in Excel

5. For Series X Values, Select Data under XPos. Click Ok.

Adjust X Values Graph with Dummy Data for Excel Chart

 

6. Select Hidden and Empty Cells

Adjust Hidden and Empty Cells in Excel for Broken Axis

 

AutoMacro | Excel Automation Made Easy | Free Trial


Changing Y Axis Range

  1. Click on the new line you just made
  2. Select Chart Design
  3. Click Add Chart Element
  4. Click Data Labels
  5. Select Left

Change Y Axis Range for Axis Break in Excel

6. Change Empty cells to Zero. Click OK.

 

Change Hidden Cells as Zero for Axis Break in Excel Graph

Changing Labels

  1. Click on each individual axis
  2. Click on the formula bar
  3. Select the Labels that you want the Y Axis to show

Note 1: Do this for each label

Note 2: Click Delete for the Gap and the other Y Axis

Change Label for Y Axis Break in Excel Graph Chart

 

Final Graph with Broken Axis

Your final result should look like below, showing the break in the axis.

Final Excel Graph with Broken Axis in Excel Chart

 

AI Formula Generator

Try for Free