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.

...
Clustered and Stacked bar charts aren't a formal type of chart Excel does, but creative organization of the numbers that drive it allow them to be made.

Step

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.

Step

Insert two blank rows between each row of data in the table in step one.

Step

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.

Step

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.

Step

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.

Step

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.