In the opening Change Chart Type dialog box, go to the Choose the chart type and axis for your data series section, click the For Broken Y axis box, and select the Scatter with Straight Line from the drop down list, and click the OK button. Now get back to the chart, right click the new added series, and select Change Series Chart Type from right-clicking menu.ġ0. Now in the opening Edit Series dialog box, select Cell I1 (For Broken Y Axis) as series name, and select Range K3:K13 (Ypos Column) as series values, and click OK > OK to close two dialog boxes.ĩ. In the popping up Select Data Source dialog box, click the Add button. Right click the chart and select Select Data from right-clicking menu.Ĩ. (3) In the Ypos column, type numbers based on the labels of Y axis in the stacked chart.ħ. (2) In the Xpos column, type 0 to all cells except the broken cell. (1) In the Labels column, List all labels based on the min value, break value, restart value, and max value we listed in Step 1. We list the data in the Range I1:K13 as below screen shot shown: Now we need to figure out a source data for the dummy axis. Click Fill in left bar, and then check No fill option, at last close the dialog box.)Īnd change the After series' color to the same color as Before series with same way. Tip: I you are using Excel 2007 or 2010, it will open the Format Data Series dialog box. In the opening Format Data Series pane, click the Color button on the Fill & Line tab, and then select the same color as background color (White in our example). In the new chart, right click the Break series (the red one) and select Format Data Series from the right-clicking menu.ĥ. Select Range A1:A7, then select Range C1:E7 with holding the Ctrl key, and insert a chart with clicking the Insert Column or Bar Chart (or Column)> Stacked Column.Ĥ. (2) In Cell D2 enter =IF(B2>$B$13,100,NA()), and drag the Fill Handle to the Range D2:D7 (1) In Cell C2 enter =IF(B2>$B$13,$B$13,B2), and drag the Fill Handle to the Range C2:C7 We need to refigure out the source data as below screenshot shown: In our example we get four values in the Range A11:B14.Ģ. To break the Y axis, we have to determine the min value, break value, restart value, and max value in the new broken axis. Supposing there is an extraordinary big data in the source data as below screen shot, we can add a dummy axis with a break to make your chart axis precise enough.ġ. Then you will see there are two Y axes in the selected chart which looks like the Y axis is broken.
#Microsoft excel 2011 break in axis code
Tip: If you are using Excel 2007 or 2010, right click the primary vertical axis in the chart and select the Format Axis to open the Format Axis dialog box, click Number in left bar, type 0 into the Format Code box and click the Add button, and close the dialog box.) In the Format Axis pane, type 160 into the Maximum box in the Bounds section, and in the Number group enter 0 into the Format Code box and click the Add button, and close the pane. In the chart, right click the secondary vertical axis (the right one) and select Format Axis from the right-clicking menu.ĥ.
In the opening Format Data Series pane/dialog box, check the Secondary Axis option, and then close the pane or dialog box.Ĥ. In the chart, right click the below series, and then select the Format Data Series from the right-clicking menu.ģ. Select the source data, and add a line chart with clicking the Insert Line or Area Chart (or Line)> Line on the Insert tab.Ģ. Supposing there are two data series in the source data as below screen shot shown, we can easily add a chart and break the chart axis with adding a secondary axis in the chart.
0 kommentar(er)
