I need to create an excel column chart where one of the series has exceptionally big value while the rest have smaller value. I am using the same unit for all values. Here is part of the data I want to plot.
My data
I have this plot
I want this
I want the chart to give emphasis on the smaller data series while showing only max value for the big series
32 Answers
Excel does not offer this functionality out of the box and for good reason. The human eye will judge and compare the visible length of the columns and a broken scale axis will significantly distort the relationship between the shortest and the longest bar.
I know of two Excel charting experts who have removed their tutorials about how to create a broken axis from their web sites, because it simply is not good data visualisation.
The technique to do this involves using a graphic with the break lines overlaying the chart and manipulation of the data to shorten the tallest columns in the chart. The approach is cumbersome and hard to automate, especially with regards to the placement of the graphic on the chart.
If you want to focus on the shorter columns, build another chart without the high columns.
5If you use a log scale, the values of the bar chart will look more distinguishable. But it doesn't create a scale break chart per se.
Right click on the Y-axis and select format axis. Then select the option for log scale.
You can change the base of the log scale until the chart looks how you want it. The default base is 10.
To make it even more visually appealing, delete the axis so it doesn't show on the graph. If you don't, the axis numbers just look too weird.
