# Cropped chart: when some values are too big to fit

Charts and Graphs - 6 comments

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:

## 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.

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)
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!

### Awesome resources on charts

Raise above the rest with these awesome resources on charting:

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.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: Advanced Charting, broken y axis, charting, charting principles, column charts, combination charts, cropped chart, downloads, no-nl Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 6 Responses to “Cropped chart: when some values are too big to fit”

1. Don't let Jon Peltier see this!

• Micah Dail says:

Yeah, I get the feeling it might raise his eyebrows.

"Remember the cardinal rule about bar charts and axis scales? Because we judge the values in a bar chart by the lengths of the bars, not by the positions of the ends of the bars, the axis scale must include zero." - Jon Peltier

Although technically I guess he never said anything about them having to end at their real value....

2. Logarythmic scale also works for that kind of charts.

3. gossip_boi says:

how smart using shaps!

4. soheil says:

Hi guys, who know that how I can break Y axis on clustered column charts.

 « Case Sensitive Lookups CP044: My first dashboard was a failure!!! »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.