2011年5月19日星期四

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

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



没有评论:

发表评论