Excel Chart: Broken bar chart to avoid browning from very large values

Ad Office Tab

Tabbed View for Word, Excel, PowerPoint, Project & Visio

---

As we all know, the internet users of China and United State are much greater than those in other countries. China has many people and USA has a large economic. But does this issue have relation with Excel chart? If we create a bar chart to compare the internet users from different countries, you will find the number from China and USA has drowned out the other data.

How to solve this issue? There are many solutions such as using a logarithmic scale. But they are not perfected. Let's see how The Economist solves this issue. The following chart comes from Economist finance magazine.

Two broken bars have been added to stand for those ultra-values. The ultra-value has also been displayed as text label directly. The chart is very professional. In fact, today's question is: Can we create a broken bar chart in Excel to avoid browning from very large values? The answer is definite. The following is my chart from Excel, which is a > 90% simulation of the Economist one.

Pre-request: The following data come from the popular magazine: The Economist.

Step 1: Trim and prepare auxiliary data.

We want to remove two very large values in the table. So we need decrease the value of China and United State. Let's use the following formula to re-calculate Users as virtual Users'. The result is saved into column E. We also need add two place holders for axis label: Column F & column G.

=IF(B3>100,B3-80,B3)

Step 2: Create draft stacked bar chart

Select E2:G16 and insert a stacked bar chart into your worksheet. Limit the primary horizontal axis to [-40 – 110]. Your chart should like this.

Step 3: Display countries and percent label

It’s easy to display countries but it’s some difficult to display to percent value. Select one of bar series and choose "select series data” from context menu.  Change “Horizontal (Category) Axis Labels” from 1, 2, 3… to Column A. Now the “Category Name” for all series is country’s name.

Select the series for “Country” (The column F) and display “Data Label”. Do the same operation for the series “Percent”. Your chart should be.

Now the issue occurs. Maybe you’ve found. Excel’s “Data Label” can only display “Series Name”, “Category Name” or “Value”. You cannot refer it to other data range. I think Microsoft will change this in future version.  We’ve developed an addin for this. This addin can help you change data label from any data range. We are optimizing code now. You can download it from our website http://www.ucmapi.com next month.

Here we have to do it manually. Click one “Data Label” with the number “-9” twice carefully. You will find you can edit its content. Input real value into it, for example, “17.8” for China… Your chart should like this. You’ve finished half job until now.

Step 4: Add our own grid line

We’ve found the grid line in Economist’s chart is some different with the default one from Excel. We have to change it. At first, remove the default one and select Column I (The grid column from data table) and paste this column into the draft chart. Your chart should like this.

Select the purple one and change its type to “Scatter”.  The chart should be this

Change the scatter series’ X and Y value to column I and Column J. At the same time, you need limit the “Secondary Vertical Axis” to [0 - 15]. To display grid line, you have to show error bar for this scatter points. The result chart looks like this:
 

Step 5: Add broken icon into chart

Copy cell $I:11 and $I:12 and paste them into the draft chart. Change its series type to “Scatter” too. And change the X and Y value of the series to $I:11, $I:12 and $J:11, $J:12.

Group two arrow shapes as out broken icon and select those two scatter points for broken icon. Press Ctrl + V to paste broken icon to it. What about the following chart. In fact, we’ve finished 80% of it. All elements have been added into the chart. The next step will be beautifying.

 
Step 6: Beautify the result chart. Your chart will become as professional as that from the Economist.

6.1 Change background color of chart area and plot area to black.
6.2 Remove the filling for “Percent” series and add border to its data label.
6.3 Change the font color for countries’ name
6.4 Change color for bar char and change font color for broken label
6.5 Add decoration and chart title.
6.6 Add background

Copyright (C) by ucmapi. You'd not use it for commercial purpose