How To Create A Split Axis In Excel

Friday, Nov 18, 2011
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

If you're looking for a tutorial on breaking an axis scale, you won't observe information technology here. Instead you lot'll read why breaking an axis is a bad thought, and you'll get a tutorial in Panel Charts, which are a more effective (and easier) means to show your information.

The Problem

People frequently inquire how to show vastly different values in a single chart. Ordinarily they ask considering a few very large values (for instance, Paris in June or Madrid in May in the chart beneath) overwhelm the other, relatively much smaller, values.

Logarithmic Scale

One proffer is to employ a logarithmic calibration. For scientific information presented to scientific audiences, this is often an excellent proffer. For the general public, and for general data, this may not be then useful. Especially in a bar nautical chart, where the length of bars is of import to comprehension, not some mathematical abstraction of length.

Broken Axis

Another proposition is to "interruption" the axis, so that part of the axis shows the pocket-size values, then another part of the axis shows the large values, with a section of the centrality scale removed. Sounds practiced, only you've lost any correlation between the big and small values. Also our eyes are likely to see the two broken bars in the chart below as only about twice the value of the tallest of the unbroken values (despite our conscious brains "knowing" that the axis has been cut).

Another problem with this arroyo is that it's cumbersome to create and nearly incommunicable to maintain charts like this.

Console Chart

A ameliorate suggestion than either a log scale or a broken axis is to plot the data in a panel chart. This chart has two panels, one with an axis that shows all the data, the other with an axis that focuses on the minor values. I generally advise strongly confronting using whatsoever kind of slope in a chart, because the gradients are pretty much meaningless. In this chart, the gradient at the tops of the (truncated) large values are non meaningless, but are intended to show the large values extending high upwards into the clouds.

Making the Panel Chart (It's Easy!)

If you desire to play forth at dwelling house, the information is located in BrokenYData.csv.

Hither is the data for the chart. Columns Due east, F, and Grand have the same data every bit columns B, C, and D, except the two very large values (>30 one thousand thousand) have been replaced by cut-off values of 7,500,000 (shaded cells).

The showtime pace is to plot all of the data in 1 chart. Past default, all series are plotted on the primary axis.

The second step is to move the three extra series to the secondary axis. They cake the primary axis data…

… but if I format the secondary centrality serial with outlines and no fills, yous can run across the chief centrality data.

Back to solid fill colors. I take rescaled the vertical axes. The primary (left) axis now has a minimum of -40 1000000 and a maximum of +40 meg; the secondary (correct) axis at present has a minimum of 0 and a maximum of xvi million.

Add the secondary horizontal axis. Excel by default puts it at the elevation of the chart, and the confined hang from the centrality downwards to the values they represent. Pretty foreign, but we'll fix that in a moment.

Format the secondary vertical centrality (correct of chart), and alter the Crosses At setting to Automatic. This makes the added centrality cross at naught, at the bottom of the chart.

(The principal horizontal axis as well crosses at zero, simply that's in the middle of the chart, since the primary vertical centrality scale goes from negative to positive.)

Now we need to employ custom number formats to the vertical axes.

The chief (left) centrality gets a format of 0,,"M";  (nada, comma, comma, and upper-case letter Grand within double quotes). Each comma knocks a set of 3 zeros off the displayed value, making for example 1,000,000 appear as 1. The G volition be shown afterward the number of millions. The semicolon indicates that this format is for positive values, and nothing after the semicolon indicates that negative values are not to be shown. Since no special format is indicated for zero (which would exist later on a second semicolon), it is shown with the same format as a positive number.

The secondary (right) axis gets the trickier format of [<8000000]0,,"M"; (less than eight million enclosed in square brackets, naught, comma, comma, and capital Thousand inside double quotes). The first format in the cord is ordinarily for positive numbers, but square brackets indicate a non-default condition for the first string. This means that any values less than viii million volition appear every bit the number of millions folloewd by majuscule M. The semicolon with nada following means that any other numbers will not be displayed.

Now I've cleaned upward a bit. I've used a medium gray line for the plot expanse edge, and for both horizontal axis lines. I've also set the labels of the master horizontal axis (center of the chart) to No Labels, considering they are redundant and clutter upwards the chart. The primary and secondary axis scales conveniently have the correct spacing so that the primary horizontal gridlines piece of work for the secondary axis as well.

Now I've applied the aforementioned fill up colors to the secondary axis columns as are used for the master centrality columns.

Finally I've formatted the two large values separately. To format simply one indicate in a series, click once to select the serial, and so click again to select the detail point (column) to format.

I used a gradient that had white fill at 0%, and column's regular make full color at xv% and at 100%. This gradient makes the bars extend upwards, and fade as they reached into the clouds.

Finally I deleted the duplicate legend entries. To delete an unwanted legend entry, click once to select the legend, then click again to select the item legend entry, then press the Delete key.

This is the finished panel chart. The top console shows that the two outlying values are drastically larger than the others, while the lesser console allows comparison between the smaller values.

The Final Word

I know everybody'southward case is special, and everybody knows ameliorate than I exercise about why using improper techniques is correct in their particular situation. Your boss needs it this way, or it's a specialized scientific chart, or you don't see how anybody could exist confused, or information technology's really actually of import. However, I am nether no obligation to share something that I do non want to share. I do non even have the sometime tutorial, so I cannot send it to you, nor volition I recreate a new version of the tutorial.

Source: https://peltiertech.com/broken-y-axis-in-excel-chart/

Posted by: weiserthatrepasis.blogspot.com

Share this post