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.