In this Excel charting tutorial, you will learn how to break Y axis in chart. It is useful in the case of problems with the axis scale. When your data is really differential, just use this trick.
Prepare data for broken axis data
1. After entering data into an Excel sheet, select the data and form the desired chart through Insert > Charts.
2. Delete the grid lines through format grid lines options, select line and fill to no fill and no line through axis options. Also, delete the legends.
3. Now, right-click the vertical axis and go to format axis. In the format axis, enter minimum to 0 and in maximum enter the point after which you want to create the break in the graph.
Add a Break in Excel chart
4. Resize the chart by dragging down the inner handles.
5. Now make a duplicate copy of the chart. Select the chart and press ctrl +D to make a duplicate copy. Remove the borders of the chart by selecting no line under no border options. In format axis option, change the minimum to the point where you want to resume your chart after the break. Your chart will look like this:
6. Place this new chart over the old chart:
Also, insert the marker (through Insert >Shapes) to show a break between the data.
7. In the final step, add labels, legends and axis titles.
8. Adjusting the Gap: In your chart with the broken Y-axis, you can adjust the size of the gap between the two sections of the Y-axis to control how much of a break is shown. Simply click on the gap between the two sections and drag it up or down to increase or decrease the size of the gap. This allows you to fine-tune the chart’s appearance based on your preferences.
9. Adding a Label or Marker: To make it clear that there is a break in the Y-axis, you can add a label or marker. You can use a text box or a shape (e.g., a line or arrow) to indicate the break visually. This label or marker can include a brief explanation or note to inform viewers about the break and the reason for it.
10. Legends and Axis Titles: Ensure that your chart has clear legends and axis titles. This helps viewers understand the data and context, even with a broken Y-axis. Clearly label the Y-axis sections before and after the break to avoid confusion.
11. In some cases, breaking the Y-axis may not be the best solution. Consider alternative chart types, such as a dual-axis chart or a stacked column chart, that can present the data without the need for a break.
Using a logarithmic scale
Here are the alternative steps to break the Y-axis in Excel:
- Select the Y-axis of the chart you want to break.
- Right-click on the Y-axis and select “Format Axis” from the context menu.
- In the “Format Axis” dialog box, select the “Scale” tab.
- Under “Scale,” check the box next to “Logarithmic scale.” This will convert the Y-axis into a logarithmic scale.
- Set the minimum and maximum values for the Y-axis. These values should be appropriate for the data you are working with.
- Under “Logarithmic scale,” set the base value for the Y-axis. This value should be appropriate for the data you are working with.
- Set the tick mark type and the major unit for the Y-axis.
- Click “Close” to apply the changes.
Your Y-axis will now be broken into two sections, with a gap where the scale changes. The values in the gap will not be displayed on the chart, but the context of the full range of values will still be visible. You can adjust the formatting options to further customize the chart as per your requirement. Note that breaking the Y-axis can sometimes be controversial, as it can distort the visual representation of the data if not done carefully.