Microsoft Excel Chart - Clustered and Stacked

I spent some time learning how to do this yesterday, so I'm going to explain  the process before I forget.

Microsoft Excel has pre-built charts - one type of chart is clustered and another is  stacked.  I wanted to be able to do both.

First, I made a copy of my data on Sheet 2 of the workbook.

I wanted it to update in case the numbers change at some point.  I experimented with different paste options and found the "paste links" button.  This keeps the table cells linked to the original data which will keep the copy table up to date. 

Next, I thought about my ultimate goals.  The table I wanted to end up with would really have 9 columns, clustered (using the decrease gap option), but with two blank columns separate the three "clusters".

So I stretched out the table data to change the data from a 9x3 table to a 9x9 table, well really an 11x9 table.

 Since the data values are linked to the original table, using copy/paste will screw it up. Instead, highlight the the data to be moved, and click on the bottom right corner then drag it over and release.  The row names for the two blank lines need to be a space or two so that it will look nice in the finished product.

I then created the stacked chart, selecting the 11x9 table of data.

At first, this didn't look great. This image shows the chart without blank lines in the table.  I did a couple of things to clean it up.  First, I went through the "Select Data" menu (right-click anywhere on the chart) and changed the Series names to the correct values.  You can see I had done three already in this screen shot..

Depending on what you want as your final project, you can keep the legend, deleting all but one of each name.  You can change the names so they are more detailed (which ends up looking a little confusing to the viewer).  Or you can put the names on the chart, deleting the legend. I'll explain how in a minute.

 Let's remove the gaps first.  Click any of the columns and, without moving the cursor, right-click in the same place and select "Format Data Series..."

This will open a menu, usually straight to "Series Options".  For "Gap Width", I chose "10%" because removing the gap entirely made the columns really squish together. 

You can experiment and decide what looks best for you, of course.

Click "Close" on that menu and we're almost done.

Right-click each color in the first cluster.  There are several things you can do here.  Notice that selecting one, will select each box of the same color. If you only want to edit one single box instead of all the corresponding, you can click again.  It saves time to do them all at once though.

Click on the "Shape Fill" icon and choose your color.  Do this for each color box in the first cluster - I chose shades of green for this one but it's really whatever you like.

If you want each column to be a different color, you can do that and add data labels. 

Right-click each color box in the first cluster and choose "Add Data Labels".  This defaults to showing the data value.

When you've gone through them all, do the same process, but select "Format Data Labels".  This will give you several options, and you can experiment, but I chose to unclick "Value" and click "Series Name".  Then you can delete the legend.  (Doing this is not entirely unproblematic since the size of the data boxes might make the labels stack on top of each other.  For boxes with data value "zero", I delete the Data Label.)