chartsmicrosoft excelmicrosoft-excel-2013
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
There are two possibilities to get the appearance you've described:
1) As you noted (but dismissed), you can "force" the category to line up. You'll need to line your column chart data to match their equivalent XY series X data. You'll end up with 8000 column data points (all categorical, but matching your X data), with lots of #N/A entries to prevent errant data from being plotted as a column.
2) You could also plot the column data as a separate XY series, and use a formatted Y Error bar to represent your column. This is far easier than option #1 above, but the formatting options are somewhat limited compared to a true column chart.
In the image below, the Blue columns are based upon Option 1 above, while the Red columns are based upon Option 2 above.
Best Answer
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.