How to Create a Clustered & Stacked Chart in Excel
Excel has the ability to make clustered bar charts, where the bars for related items are placed adjacent to each other for visual comparison, and stacked bar charts, where information is layered on bars for ease of viewing. What Excel doesn't have is an easy way to make a clustered chart out of stacked bar charts. It is possible to make this kind of chart in Excel, but it requires careful arrangement of the values in Excel.
Start Excel, with the data you want to graph in a set of adjacent cells. For example, if you're comparing production yields of four different types of component over eight calendar quarters, you'd have a grid that had the four types of components listed in cells A2 through A5, and the eight calendar quarters listed from cells B1 through I1. The numerical values of what components were made in which quarter would be entered in cells C2 through I5.
Insert two blank rows between each row of data in the table in step one.
Select cells F2 through I2. Right-click on the range of selected cells and select "insert." When prompted for what to insert, insert cells and select "Shift contents down." This will shift the last four data values in each row down by one row.
Select cells A1 through I13, and then click on the "Insert" tab. In the charts area of the tab, select "Column," and from the chart types, select "Stacked Column" from the 2D chart types. A chart will appear that has each of the columns of data displayed as a stacked column chart, but with a gap between columns.
Click on the chart that's created -- this will select it, and make the Chart Tools tabs appear. Right-click on one column of the chart; a "Format Data Series" dialog box will open up, with options on the left pane and controls on the right.
Select "Series Options" on the left pane, and on the right pane, slide the "Gap Width" slider all the way to the left. This will eliminate the gaps between your columns. Click on the "Close" button when you're done.
Tips & Warnings
- This is a very generalized description; if your data series has more than two types of bar charts to cluster, step three, shifting the data that shows up on the right hand column of the final chart, can be repeated multiple times - the key is that each data series to be clustered has to be on its own row for charting.