| In Excel, it is difficult to create a chart from annual or | | | | Create a Clustered Stacked Chart |
| monthly data, so you can compare different products | | | | The Stacked Column chart may be close to what you |
| over several years, or different quarters per year. For | | | | want, but it doesn't highlight the years and the quarters. |
| example, you might need to compare production rates | | | | Ideally, you'd like to keep the summary information |
| per product, per quarter, over 2 years. Here is the | | | | that's available in the Stacked Column chart, but with |
| sample data, with hyphens used to space the data. | | | | the years clustered, or the quarters clustered. |
| Product--Yr1_Q1--Yr1_Q2--Yr2_Q1--Yr2_Q2 | | | | Unfortunately, Excel doesn't have a Clustered Stacked |
| Prod01----100-------125-------150-------190 | | | | chart type, so you'll have to create your own. |
| Prod02----200-------230-------250-------280 | | | | The secret to creating a Clustered Stacked chart is to |
| How could you make a chart that shows the | | | | arrange the data so there are blank rows where you |
| fluctuations in production for each product type. Did | | | | want columns separated, and put the data for |
| production change much from one year to the next? | | | | different columns on separate rows. |
| For all product types? Are there seasonal differences | | | | If you're working with data from different years, put |
| in production? | | | | each year's data in a separate row, with a blank row |
| Start with a Clustered Column Chart | | | | after each product type. Also put a blank row before |
| To start, you might create a Column chart, using the | | | | the first product type, to create space at the left in the |
| first 2-D Column chart type in Excel, which is a | | | | chart. The new arrangement has headings, then a |
| Clustered Column. With that chart type, the chart could | | | | blank row, then product 1, with each year on a |
| be a bit crowded, but you could compare the product | | | | separate row, another blank row, then product 2, with |
| types within each year and quarter, or follow one | | | | each year on a separate row. Hyphens are used for |
| product type across all the quarters. | | | | spacing in the table shown below. |
| If you wanted to focus on the product types, rather | | | | Product--Yr1_Q1--Yr1_Q2--Yr2_Q1--Yr2_Q2 |
| than the quarterly results, you could switch the layout | | | | Prod01----100-----125 |
| of the rows and columns in the chart. | | | | ------------------------------150--------190 |
| This would place the product types along the chart's | | | | Prod02----200-----230 |
| x-axis, and the quarters would become series names | | | | ------------------------------250--------280 |
| in the legend. | | | | Finish the Clustered Stacked Chart Create a Stacked |
| Try a Stacked Column Chart | | | | Column chart from the rearranged data, then set the |
| For a different view of the data, you can create a | | | | Gap Width to zero, and adjust the series, to include the |
| Stacked Column chart, instead of the clustered column | | | | first and last blank rows. The revised chart will have a |
| chart. The Stacked Column chart is usually less | | | | cluster for each product, with stacks comparing one |
| crowded, and lets you compare the total production | | | | year's total production to the next. |
| for each product. | | | | |