Excel Chart: How to Add Broken Icon at Low Part of Y Axis

Ad Office Tab

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

---

I've reviewed all my collection and found we usually used broken icons for 3 scenarios. Last week, I posted an article for the first scenario "Broken bar chart to avoid browning from very large values". Today let's practice how to put a broken icon on Y axis at low part. The following chart gives us an example why we need broken icon at the low part. All data of the chart is a little large and the whole line chart looks a lamp suspended from the ceiling.

How to solve this issue? We can limit Y axis to [20-50]. But the Bloomberg uses a different method. The following chart comes from its website. A broken icon is added at the low part of Y axis to stand for [0-40]. Even though this chart looks complex, it gives us a different choice to solve this issue.

How to create this chart in Excel? I tried it in Excel 2003, 2007 and 2010 and found broken icon can be added in all version of Excel but if you want to fill with broken icon with color like the above chart, You need more work. The following chart is created in Excel 2010.

Pre-request: Suppose we have the following raw data.

Column A includes all raw data. We can find they are a little large for a normal line chart. We create an auxiliary data sequence for it and save then into column B. Column C will be used as background.

=$A2-20

Step 1: Create an Area Chart as background.

Select column C, column B and insert an Area chart into your workbook. Limit your primary axis to [0-40]. This is a basic background chart.

Step 2: Add broken icon.

As we all know, Excel does not support free shape filling. Or in other words, you cannot create a closure scatter line chart and fill it with solid color. But we have to add a small triangle as our broken icon. We can use a picture as a broken icon. But picture cannot resize itself according to chart. I find another solution for this issue. Use lot of scatter line to simulate filling. This solution is only suitable for small shapes, or the performances will not be high.

For triangle's filling, there are many auxiliary data. I just list part of them. If you want more, please drop me a mail. Please see the following data table. 

Select your chart and right click to launch "Select Data Source" dialog. Click "Add" button to add "broken" data into your chart. Right click the new added data and change its chart type to "Scatter with Straight Lines". You also need limit your secondary vertical axis to [0-40]. After that, your chart should like this.

Step 3: Add grid line, border and axis.

Grid line and borders will be a series of scatter chart. The following tables list axis data and grid line data. Add them one by one into your chart. You'd also notice this tip: Axis should be added as the last scatter line chart so that Excel can draw the axis over other charts.

Axis Data

Grid line & Border

After create all scatter line chart, your chart should be:

Step 5: Add real line chart

It's very easy. Select column B from the raw data table and insert as a "Scatter with straight line" into your chart. Cool, we've finished most of working.

Step 6: Beautify your chart

Your chart should be meaningful and attractive. Both are very important. You’d collect lots of examples or consult some experts.

6.1 Change background

6.2 Change line style and line color of grid line, border and axis

6.3 Change line style of real data line

6.4 Add chart title and chart footer

Excel Chart: Use Multiple Secondary Axes to Put >=3 Kinds of Data in One Line Chart

Ad Office Tab

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

---

I my previous post, I practice how to use broken icon to stand for very large values in a bar chart. In fact, this method is only suitable for those data with the same data type. All data of the previous post is internet users and the percent value (Users/global internet users, you can think it as the other data type) is listed as data label at the left side. If we want to draw the percent and users count in one chart at the same time. What will happen? You will get the following chart. How to solve it or How to put data with different type (say >= 3) into one chart?

We usually use "primary axis" and "second axis" to solve two data types.  Or we can create multiple charts and overlap them one by one. 

Here is another solution. Use scatter chart to simulate a multiple secondary axes line chart. In fact, this solution is not complex; we just need create some auxiliary data and convert different type of data into the same type. For example, in the previous post, we can divide internet users by 1000, now all data is at the same level. Add an axis for that series to restore readers’ feeling of the real data. The following chart demos this solution.

Pre-request: Suppose we have the following data. A factory has 10 product lines and each line has different output, cost and product defective rate. And we want to draw 3 lines on one chart.

Step1: Create auxiliary data to simulate multiple Y axes.

In order to put 3 kinds of data into one chart, we need 3 axes. In scatter chart, we usually use one or multiple data series to simulate axes. The above auxiliary data will be used to create 3 Y axes. Select the range [F2-I12] and insert a scatter line chart into your workbook. Your chart should be.

Switch X & Y value for each series to make 3 data series vertical orientation. We'd also limit the primary axis to [-5-11]. Your chart will look like this.

Step2: Convert all raw data into the same data type.

Maybe you've found, if you divide column B by 100, column C by 1000 and multiply column D by 100, all data will be formatted to the same level.

Add all these data into the draft chart as real line chart. Your chart will look like this. Now, there are 6 series. 3 of them are axes.

Step 3: Prepare data label for axes

In fact, there are 3 secondary axes here. And they will stand for different data type. The most left is cost($) and the most right one is Defective Rate. Let's prepare the following data as data label for these axes. Use our "update data label tool" to change all data labels' content to different values. We also need enable error bars for them at the same time so we can get tick mark for each axis.

Step 4: Promote your chart

4.1 Change chart color and style
4.2 Add legend for all real data series
4.3 Add Legend data label, which is a special data label. You can add 3 new scatter points into draft chart and show data label for them
4.4 Add Title

Excel Chart: Table Style Bar Chart Gives Your Different Feeling

Ad Office Tab

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

---

In our daily work, we usually use a table to store data and create an independent chart to stand for these data. This method is very useful. But if we always use it in our reports, I think, it will lose eyes at last. We will try another style today: Combining chart and table together or put chart into table directly. Excel 2010 has spark line chart, which is a good example on this area. This kind of chart can also be seen in business magazines now and then. The most common scenario is replacing one or multiple column of a table with bar charts. The following examples come from some popular finance magazines.

We have two methods to implement this kind of chart. Let's take the last one as example.

1. Create two bar charts and force their background transparent. All texts are input in Excel's cells. Overlap these charts above those cells. Use screen snapshot tool to save as the last the chart.

2. Put text into bar charts as data labels. But the Excel's data label does not support some advanced features such as changing width freely, changing alignment etc. This method is only for these charts with short or simple text.

I will practice the second method in this post and create a bar chart like the following picture.

Pre-request: The following raw data comes from the above wall-street chart.

Step 1: Identify how many bands we'd add to the chart.

I alternate all bands we will add into the chart. Among them, two bands are for the real bar charts and two of them are for year and data value. For each bar, we also need a band to simulate the blank part. So the last stacked bar chart should have 6 parts.

Step 2: Prepare auxiliary data.

I prepare the following auxiliary data for the raw data. The column D will be used for Year, which has been saved into Column K and Column I will be used for real data, which has been put into column L. Column E plus Column F should equal to 100, which is the width of the first bar column of the table. Column G and Column H are for another bar chart.

I also add "Len/day" and "Miles/day" and the row 6 into the auxiliary data. They will be used as table header.

Column M will be used as alternated background of the chart. You can check it in my previous post for how to alternate background.

Step3 : Select Column M to create alternate background. Your chart should like this.

Step 4: Add bar chart into the chart

Select column D and press Ctrl+C to copy data into clipboard and then select chart to paste data into chart.

Right click the new added bar and plot it the "Secondary Axis" and then change its chart type to "Stacked bar". Do the same operation for column E, F... You will get the following chart. The Column M is still plotted into the "Primary Axis", but all auxiliary data has been plotted into the "Secondary Axis" as stacked bar chart.

Step 5: Change the "Primary Axis" into alternated background

Select series 1 by mouse or ribbon bar. Launch "Format Data Series" dialog and change "Gap Width" to 0. Then you'd limit the "Primary Axis" to [0-1].

Step 6 : Add data label for all "Secondary Axis" bar chart and fill blank bar with transparent.

Here I need that small addin "Data Label Updater" again. Where to download that addin and how to use it. Please read my previous post.

After you change data label, your chart should like this. In fact, until now, all components have been added into chart.

Step 7: Beautify the result chart as the following steps. Your chart should like this.

7.1 Change table header's background to dark gray

7.2 Change alternated color to light gray

7.3 Hide grid lines and all axes.

7.4 Add Chart title and picture.

Copyright (C) by ucmapi. You'd not use it for commercial purpose. Keep all links when you re-post it.

Excel Chart: How to Change Data Label's Content Quickly and Easily

Ad Office Tab

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

---

By default, the data label of Excel charts can only display "Series Name", "Category Name" or "Value". Can we change data label's content to any selected range? There is a small plugin for it.

You can download and re-distribute it without any limitation. 

Get 32-bit version from here

32-bit version is for Excel 2003, Excel 2007 and Excel 2010 32-bit version

http://www.ucmapi.com/download.php?id=5c6c1c84-d079-4b5a-acac-abfc7a284f3e

Get 64-bit version from here

Only for Excel 2010 64-bit version
http://www.ucmapi.com/download.php?id=7f562fd5-1189-4211-9ac3-f79e137482c0

Where to find the addin

Find it on Chart's Context ribbon bar (Beside normal Axes setting) for Excel 2007/2010

Find it here for Excel 2003

How to use it?

Copyright (C) by ucmapi. You'd not use it for commercial purpose. Keep all links when you re-post it.

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