Excel Chart: Customize Axis Label to Create Personality Charts

Download Office Tab

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

---

We've created some useful charts in our previous posts. This time, let's do a small summary about axis labels. Even though axis labels are very common, you can customize them to promote your chart. I find 3 different usages from my collection.

Case 1: Put Axis Labels among Bars

When the axis labels are long, it's not a good solution to display them below axis. Professional magazines usually put them among bars. The following chart comes from the Business Week. Axis label is above bar items. Excel does not support this, but we can implement it very easy.

We need an auxiliary data series and the "Data Label Updater" tool for this case. The first column of the following data table is the raw data. The second column is axis label and column C & D will be used to control label position.

1.1 Select column A and insert a clustered bar into your workbook.

1.2 Select column C & D and insert a "scatter with only marks" into your chart.

1.3 Enable data label for scatter chart.

Your chart will look like this:

The above chart looks urge. How to control data label's width and display them in single line? Excel has no configure for this. But I find a solution for it. For scatter charts' data label, the width is controlled by the width of chart area. But other charts such as bar or column, the data labels' width is controlled by bar chart itself and plot area. This is the reason I choose scatter as my data labels controller. So if you expand the chart area, the data label will become single line at last. Notice you must keep the width of plot area when you expand the chart area. After that, the chart looks like:

1.4 Beautify your chart and add title and cut useless part away, your chart will become this one.

Case 2: Put Axis Label at random position of column chart

We can use the similar technology to put axis label at random position of a column chart. The following chart put axis label left edge of column bar.

Data Table: The raw comes from internet.

Draft Chart: Select column A & B to create a clustered column chart and add column D & E as scatter chart on secondary plot area. Enable data labels for the scatter chart.

Result Chart: Maybe you've found the axis labels are put at the left edge of column items. You can even move data label out of the column items now. But by default, you cannot control data labels' position in Excel directly.

Case 3: User Icon as axis label

When the chart has relation with country, we usually use national flag as part of axis label. The following chart comes from the Economist.

Let’s get all data from the above chart. The column C & D will be used for data label.

3.1 Select Column B and insert a bar chart into workbook

3.2 Select Column C & D and insert a scatter chart into workbook onto the secondary plot area.

3.3 Enable data label and update data label to countries

3.4 Select each mark in scatter chart and paste a national flag and label icon.

3.5 Apply different color and theme; Add title and sub-title; Append foot and comment.

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.