How to create a 100% stacked column chart
The 100% stacked column chart lets you graph values in a group. Each value in a group is a stacked column and the total of the stacked columns is always 100%.
The image above shows numbers for 2010,2011 and 2012 grouped for each region. The values are relative to each group meaning you can't compare values for 2010 between, for example, North America and Europe.
I tell you why, the column for 2010 in group North America is bigger than the corresponding column for Europe, however, if you look at the values below the chart you will see that the number is actually larger for Europe than North America.
Why is that? Because the columns are relative in size to each region, not to each year. This makes the chart useful for comparing values to other values in the same stacked column.
Obviously, the chart is pointless if you have many data points in each group making the chart really hard to read.
Build the chart
- Select the cell range you want to chart.
- Go to tab "Insert" on the ribbon.
- Click "100% stacked column" button.
Make the chart easier to read
What can you do to make the chart easier to read? Display the values as data labels on the columns.
- Right click on a stacked column
- Click "Add Data Labels"
- Repeat step 1 and 2 for each column color.
- Select the data labels and change the font size and color if needed.
Arrange values
You may need to rearrange values in order to build a 100% stacked column chart.
Building a pivot table might be what you are looking for before you create a 100% stacked column chart.
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Color chart columns based on cell color
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may click on a button or multiple buttons and […]
One Response to “How to create a 100% stacked column chart”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thank you very much!