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

Excel Chart: How to Put Clustered & Stacked Column Chart Side by Side?

Ad Office Tab

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

---

Yesterday, I visit a forum about excel and found an issue: How to put a clustered column and a stacked column chart together and make them display side by side. In fact, the issue has blocked me for some time. Business week often publishes this kind of chart in its magazine. Here is an example from it.

At first look, we can overlap a clustered column chart with a stacked column chart to implement it. But how can we make them display side by side? We have to re-layout the raw data and add some blank cell into them. The following chart is completed in Excel which simulates the chart from business week very well.

Prerequest: Suppose we have the following raw data; and we want to draw the series "Visit" and series "Down" as a stacked column chart but the series "Pay" as a normal clustered column chart. In fact, we cannot create it via simple selecting and pasting. We have to re-layout the whole data.

Step 1: Re-layout the raw data.

The following data table is the result of re-layout. Column B is a place holder. It's content (*) will be removed at last. Maybe you also find there are two blank rows above and below the data row. These blank rows are used to separate column bars.

Step 2: Create Column Chart

Select the raw data A1:E20 and insert a "Clustered Column" into your workbook. You can get this draft chart.

Step3: Change clustered column chart into stacked column chart.

1. Select the series "Pay" and plot it on "Secondary Axis"

2. Change the chart type of the series "Visit" and "Down" to "Stacked Column".

Step 4: Make the "Stacked Column" and the "Clustered Column" side by side

 

Step 5: Beautify the result chart and add some decorations

If you re-layout the raw data like the following table, you can switch the position of stacked column and the normal clustered column.

Excel Chart: Always Markup the Cross Points of Two Line Charts

Click here to see Alphabetical Content of all posts 

[Notice: The chart in the post is created on Excel 2010] 

Last week I posted an article about "Markup the Maximal and Minimal Value from a Line Chart". Some friends asked if we could markup the cross points of two or multiple line charts in Excel. Here is an example from The Economist. A black dot highlights an important cross point of three line charts. This question is a little interesting, for this case we can do it manually and easily. But How to markup all cross points automatically in Excel?

After some practice, I find it can be created perfectly in Excel. But it needs a little mathematic knowledge. I prepare some raw datas and record my creating process step by step. The finial chart should like this.

Prerequest: Suppose a company has two departments and the output of each department is different with each other. We need highlight when they have the same output from line chart report.

Select Column A2:C13 and insert a line chart from ribbon bar into your week sheet. Your chart should like this:

Step 1: Assign the line chart a virtual X number value.

As we know, the horizontal axis for line chart only stands for categories in Excel. It is not a real number value. In order to add cross point, we need assign a virtual number value to them. For this example, the horizontal axis stands for 12 months, so we can assign 0 - 12 to it, which matches 12 tick marks on the horizontal axis. We've also found that the first point of both chart are located at the center of the first category "Jan". All other categories are also located at the center position of every category. In other words, if we assign 0-12 to these horizontal categories, the X value of the first point should be 0.5 and the second should be 1.5... Let's save them in column E.

Step 2: Does there exist cross point for each category?

If the data meets the following 3 conditions, we say there is a cross point for these two lines in the category.

	Case A: The current Y value of line A is greater than that of line B but the next Y values are reversed. An example is the category "Jan". For this example, we can use a formula to stand for this case. (B2>C2)*(B3<C3)
	Case B: The current Y value of line A is smaller than that of line B but the next Y values are reversed. An example is the category "Feb". For this example, we can use a formula to stand for this case. (B2<C2)*(B3>C3)
	Case C: Case C is simple, if both values are equal with each other; we say they have cross point. For example, category "Apr" and "Dec". We can also use a formula to stand it (B2=C2)

Combine above conditions; we can get an integrated formula to check if there is a cross point for each category. If there is a cross point, the formula will return true, or it will return false. The formula can be written as: 

=IF(((B3>C3)*(B4<C4))+((B3<C3)*(B4>C4))+(B3=C3),TRUE,FALSE)

Input this formula to column F and drag to fill F2:F12, we can get if there are cross points in all categories.

Notice: There is a special case. Because there is no data in F14, for F13, we cannot apply the above formula. It's an independent cell; we say there is cross point only when they are equal with each other. We can input the following formula into it like this.

=IF(B14=C14,TRUE,FALSE)

OK, you've got all information about if there is a cross point in a category. Please see column F from the previous data table.

Step 3: Calculate the X value for all cross points

We've got if there exist cross point for every category. But we cannot say all cross points are also located at the center of this category. We have to calculate X value for all cross points by ourselves. At this point, we need a mathematic knowledge. In math, we can use the following formula to get a cross point of two line.

Suppose two points (x1,y1) and (x2,y2) make a line and the point (x3,y3) and (x4,y4) make another one. Suppose they have a cross point and the cross point is (a,b), we can use the following two formula to get value for a & b.

(b-y2)/(a-x2)=(y1-y2)/(x1-x2)
(b-y4)/(a-x4)=(y3-y4)/(x3-x4)

Here I give out the final formula is Excel directly. You can research the above formula if you are interesting about it. All X values are saved into column G. If there is no cross point in the category, we will mark it as NA().

=IF(F3,(E4*B3-E3*B4-E4*C3+E3*C4)/(C4-C3-B4+B3),NA())

Notice: we also need to process the special case G13. The X value of G13 should be.

=IF(F14,E14,NA())

Step 4: Get Y value for all cross points

If we've got all X value for the cross points, we can apply them into the above mathematical formula and get Y values for these points. You need mathematical knowledge again. Here I give out Excel formula directly too. Fill column H with the following formula and process the special case for H13, you can get the correct result.

Formula in the cell H2:H12

=((B4-B3)*G3+E4*B3-E3*B4)/(E4-E3)

Formula in the cell H13

=IF(F14,B14,NA())

Step 5: Continue creating chart

God, mathematic also makes me crazy. But we've got all auxiliary data. Let's go back to that line chart. Select column H (H2:H13) and press Ctrl+V to copy it to clipboard and then select that draft chart and press Ctrl+V to paste data for cross points into chart.

 

Step 6: Change the chart type of the new added series to "Scatter"

Right click the new added series and select "Change Series Chart Types" from the context menu. Choose "Scatter" to change it to a scatter chart.

 

Step 7: Change the X value for the scatter chart

By default the X value for this series is column A, which means all cross point will be located at the center of every category. It's not our expectation. Right click the new scatter chart and choose "Select data" from context menu and change X value from column A to column G.

 

Reselect Column G as the X for the scatter chart.

Until now, we've add all data into the chart correctly. But the cross points are still not located correctly.

Step 8: Format the secondary axis to position scatter to correct location

Maybe you've found the reason why the cross points have not been located correctly. The maximal value for the secondary horizontal axis and the secondary vertical axis are not the same as the primary one, and this causes error position of the cross points. Change the maximal value the secondary horizontal axis to 12 and vertical one to 1000. (Notice, this value should be same as that from the primary axis.) Cool, all cross points are located to correct position automatically. The chart looks better than the draft one.

Beautify the result chart

Let's remove all secondary axes and change the color and theme for chart background and lines. Add some data label for cross points. A professional line chart with highlighted cross points appears.

Excel Chart: Always Markup the Maximal and Minimal Value in a Line Chart.

See all post here

Sometimes, we need markup some special values from a line chart such as the maximal or the minimal value. For example, we usually highlight the highest and the lowest quote from a stock price.

The question is How to markup the maximal and minimal value from a line chart automatically in Excel. 

We need two auxiliary data series to solve this issue. The first data series has relation with the maximal value. If raw data is not the maximal value, the relevant value in the first series will be "#NA", or the value will be the maximal value. After this rule, there are lots of "#NA" and one maximal in the first auxiliary series. The second data series is similar with the first one but for minimum.

Step 1: Build auxiliary data sequence. Suppose we have a raw data series A2:A6.

Select cell B2 and input the following formula:
   =IF(A2=MAX($A$2:$A$6),A2,NA())

Select cell C2 and input the following formula:
  =IF(A2=MIN($A$2:$A$6),A2,NA())

Drag the cell handle and fill B2:B6 with the same formula as that in B1. Fill C2:C6 with the same formula in C1.

Notice: The function NA() always returns "#N/A", which means the cell does not contain any valid value. It's reasonable, because there usually is only on maximal or minimal value.

Step 2: Create Line Chart. Select A1:D6 and insert "Line Chart with Markers" from ribbon bar. The following line chart appears. Try to update the raw data, you can find the maximal and minimal value always be highlighted automatically.

Step 3: Display data label for the maximal and minimal value. Click the maximal value and "Show data label". In the data label formatting dialog, check "Series Name" and "Value" at the same time. The final label is "Max,600".

Step 4: Let's go more. The example chart looks professional. Can we create it in Excel? The answer is sure. But we need two additional operations:

1)    Combine a line chart and area chart together to get an area chart with a highlight border.
2)    Customize data label with error bar and picture.
 

I just demo the first issue in this post. In fact, this issue is not very complex. We just need create a line chart and an area chart with the same data sequence.
 

Insert a blank area chart into the work sheet. Select the data series (Let’s use data from above example) and press ctrl+c to copy them to clipboard. Click the blank chart and press ctrl+v twice. Notice we need press ctrl+v twice to get two overlapped area chart.
 

Click series 2 and change its type to “line chart”. OK an area chart with highlight border appears.

If you like, you can apply some special color to it. I fill the following chart with these colors from above example. Does it look better?

Business Chart: Classical Color Combination for Charts in Business Magazines

Ad Office Tab

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

---

Many friends often complain their charts are much too poor style and it's hard to make them professional. I think one of the diffcult issues should be How to use correct color & style? It seems those charts from business magazine always have a set of carefully designed color & style. Sometimes, we can differentiate a business chart and a personal one only by it.

In fact, most of us are not expert on color and have no plan to dig it. How to promote our charts and make them more business style? Design a personalized color combination should be an effective method. I've collected some common used color combination. Maybe they can give you some inspiration.

Color 1. Dark Teal

As a popular business magazine, "The Economist" usually uses one color: Dark Teal. Darker or lighter Teal are also used to stand for different data series.

Red is usually used as an important indicator such as average value. Maybe, you've noticed there is also a small red box on the top-left corner. It makes these charts more vivid. Sometimes, small decorations in chart are important too.

In fact, this color has been used by many professional consultants or finance web sites, such as Roland Berger Strategy Consultants.

Color 2. Blue + White

This combination comes from Forbes. Forbes usually uses these charts in many finance reports. The color combination looks light.

Color 3. Dark Blue + Red

At its early stage, Business Week always uses dark blue and red as a special color combination in charts. I'm not sure where does this combination come from. But it seems the style gives us a feel of stable. Even though this style has been changed a little in recent magazine, Dark blue + Red are still a good color combination.

Color 4. Orange + Gray

I find many designers love this combination. This color combination has been used in many charts and even many companies' logo.

Color 5. Black + Yellow(Golden)

Black and white contrast is most strongly. Black and Yellow or Gold looks also noble and professional.

Color 6. Black, White + Gray

"The Wall street Journal" usually uses gray as chart's basic color. Even though the color is not rich, these charts are still very professional after it’s combined with good layout and comment.

Color 7. Dark Red + Gray

This combination comes from many finance magazines. Some big companies also use it as their special color combination such as Chinese Sinopec.

Color 8. Orange + Green

This color combination is a little bright and full of vitality. It’s also a common used combination in many finance magazines.

Color 9. Combine color with logo

Focus uses multiple color combination in its report. All colors come from its logo. We usually insert a logo into our report. It's a good idea to take your color combination from logo.