2011年5月5日星期四

Excel Chart: Table Style Bar Chart Gives Your Different Feeling

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.

没有评论:

发表评论