Cropped chart: when some values are too big to fit

We know that column charts are excellent for presenting information. But what if some of the columns are too tall and hijacking the rest. In a previous article, we discussed few of the approaches. Today let’s learn how to build a cropped chart (broken y-axis chart) using Excel, something like this:

Looks interesting? Read on.

Tutorial to create a cropped chart in Excel

Before we begin: Is this the best chart for this data?

Cropped charts or broken Y-axis charts can be misleading and confusing. That said, in some very rare cases, you may need to use them. My suggestion is simple:

• See if you can use a regular column chart
• See if you can use a regular column chart, crop the tall columns at a certain point and fade them using gradient fills. Then apply labels to them so people know which ones are too tall to show on the chart.
• See if there is any other alternative representation for this data (may be just the numbers in a table?!?)
• If your boss / client / spouse is adamant about broken y-axis chart / cropped chart, then make one.

Step 1: Arrange your data

Let’s say we have the numbers as shown aside.

We will have to set up some extra calculations to make this  chart. We need to split each column in to 2 portions.

• Below crop
• Above crop

But we can do this only for columns that are too tall. Also, we need to know 2 things:

• At what point we should crop the value – let’s call this crop
• What is the size of crop – let’s call this size

We also need to print a cropped symbol (2 zigzag or slant lines) at the location of crop, if we crop a column.

First, take a look the the calculation setup.

The formulas for 3 extra columns are:

Remember, our data starts from cell B10.

• Crop: =IF(B10>crop+size,crop,B10)
• Above: =IF(B10>crop+size,B10-crop-size,0)
• Marker: =IF(B10>crop+size,crop,NA())

Step 2: Create a stacked column chart

Select both Crop & Above columns and create a regular stacked column chart. We should get something like this:

Step 3: Add marker series as a line to the chart

Add the marker series (select all the values, copy and paste in to the chart – or use Chart > Select Data > Add option).

Marker series will be added as a stacked column by default.

Right click on it and select change series chart type option.

Change the series to line with markers.

Now, set the line properties to no line so that only markers show up.

At this stage, our cropped chart looks like this:

Step 4: Replace markers with crop symbol

Draw a crop symbol. Here is one I used:

1. Draw a box. Fill it with pale white color and remove borders.
2. Draw 2 horizontal lines and align them to top & bottom edges respectively.
3. Select all three shapes (2 lines and one box) and group them (right click and group).
4. Rotate this grouped object a bit.

Copy this object / symbol.

Select the markers on the chart. Press CTRL+V.

Excel replaces the markers with your symbol. (more: use shapes to enhance your charts)

At this stage, our chart looks like this:

Step 5: Format the chart

This is easy. Set both crop & above portions to same color. Adjust gap width between columns if necessary.

Play with both crop and size values until you get the perfect chart.

Step 6: Add labels to your chart

As you have cropped the columns, the axis is no longer relevant. We either need to replace the axis labels with two sets of values (before crop & after crop) or remove the axis & set data labels.

Setting different axis labels requires a bit more tweaking of the chart.

So, let’s go with data label route.

First remove the vertical axis. To set the labels:

1. Select the bottom series of the column chart. Right click and choose data labels option.(Click here for a screenshot of this step)
2. This adds default labels.
3. Select the labels and press CTRL+1 to format them
4. From label options pane, select “Value From Cells” as the source for labels. Note: This is available only in Excel 2013 or above. For older versions use XY Labeler add-in by Rob Bevey.
5. Select the original data (in B10 cell onwards) as the source.
6. Set up label properties (location, font, font size, color as you see fit)
7. Done!

That is all. Your cropped chart is ready.

Download cropped chart template

Click here to download cropped chart example workbook. The workbook contains all the calculations, full chart and all intermediate steps so that you can learn more.

Awesome resources on charts

Raise above the rest with these awesome resources on charting:

• 5 Simple rules to make awesome column charts – podcast
• 6 Best charts to show progress against a goal
• Column / bar chart with lower and upper boundary
• Interactive sales chart using Excel

Struggle with charting? Excel School is for you:

If you are mystified by the Excel charts and spend way more time on them, then consider enrolling in our Excel School program. This will help you learn how to create awesome charts, interactive workbooks, complex dashboards in a structured way.

Visit Excel School to know more about this program and enroll.