How Do I Insert a Break in the Y Axis in Microsoft Excel?

[spstelly]

Greetings,

I have a data set that I am wanting to represent in Microsoft Excel in a column chart. There are two data series. One series has small numbers (1,1,1,1), and the other series has a range of numbers where all but one are smaller (3,5,7) and the one is very large (400). Both series are related to one another and need to utilize the same Y axis. I want to insert a break in the Y axis so that all columns representing the various data points can be clearly visualized, and I want to avoid the very large number overwhelming the chart.  I am currently using Microsoft Excel 2010.

How do I insert a break in the Y axis?

Thank you.

[#2]

A break in the Y axis would distort your chart and make it impossible to compare relative sizes by just looking at the height of the bars, which is what a bar chart is designed to enable the viewer of your chart to do. You could consider setting the Y-axis to use a log scale perhaps. But I have no insight in the nature of your data and how both series relate to one another.

[#3]

@spstelly 

Hi

I just want to provide a general answer as I do not see any sample data.

Plotting 2 data series on the same Vertical (value) axis, while one has large numbers and the second has small numbers may not allow you to view the small one (although it's there).

With the chart selected >> click on the Format or Layout Tab >> in the upper left corner there is a drop list for all chart elements >> select the small Data Series >> click on the format option just below the drop list >> a dialog box opens >> Select Secondary Axis

Then, right click on the new data series that popup in the chart and change the chart type to a line chart.

Hope that helps, I am trying to provide help blindly as I do not have Excel 2010, last time I used it was 6 years ago.

Good Luck

Nabil Mourad

[#4]

@JKPieterse 

Jan, 

Thanks for the response.  I understand that the break in the Y axis would change how the graph looks, but I would employ other graph elements (Y axis numbers, data points for each column, error bars, significance markers, etc) to help the viewer understand what the graph is representing.  The data I have is two groups of 4 numbers.  The specific numbers are listed in my original post, and they represent relative gene expression of specific genes from a control and treatment sample (hence the relation of the two groups) expressed as fold change.  The idea of a log scale is interesting, thank you.  I hope this helps.  Take care.

Steven

[#5]

@nabilmourad 

Nabil,

Thanks for the response.  The data that I am using (8 numbers total) is listed in my original post, and it is two groups of four numbers each that represent relative gene expression of specific genes in a control and treatment sample.  I appreciate your information about the secondary axis, but unfortunately this would not be helpful in my case because one of the groups of number is (3, 5, 7, 400) and even if just that group had its own axis I would still have the same problem because 400 is so much larger than the other numbers.  I also have access to other versions of Excel if need be.  I am just wanting to know if it is possible to insert a break in the Y axis of a graph in Excel, and if so, how I would do that.  Take care.

Steven

[#6]

Well, in short: No, Excel cannot do that. But there are probably ways to fake it using an additional chart series and a secondary chart axis anyway.

[#7]

@spstelly I see this post is from 2019, but I also ran into this issue. What I did was created two charts. However, with the second chart (that housed the larger dataset), I selected "no fill" on the Shape Fill Format, and then made sure that the the Y axis were both equal distances so the gridlines would match, then I copy and paste it on top of the first chart with the smaller numbers. It works. Note that this is simply for presentation purposes. Updating the data may require you make further formatting changes.