Stacked bar Chart Formatting

I am working on a stacked bar chart that will display project forecasts for a 12 month period. There are multiple projects that are stacked together.... I have this setup and working great, however my boss would like the display weighted so the largest value is at the bottom of each bar working up through smaller and smaller values.

This is easy if Project A > Project B > Project C, etc as I can just sort the date before charting the results. The problem occurs when Project A has a larger forecast for January, but Project B has a higher forecast in February.

I know this is probably breaking the rules as the data series's are "crossing" - instead of each having the same color order (red - green - blue - yellow) there is the possibility that the colors would be swapped up (red - blue - yellow - green)

I know this is strictly a visual thing to make finding the largest monthly forecast easier -

This can be a "native" solution, or a solution requiring macro;s - I am very comfortable with Macros, somewhat comfortable with charting, but I have never really had the need to combine the 2...

I have toyed with pie charts, donut charts, etc, and I can get what I need, but the data is not quite as easy visually to interpret. If you have any suggestions on another chart type that might suite my needs I am open to suggestions

Using Excel 2003 (I know it is dated, but our company is slow on the upgrade path...)

Thanks in advance for any advice or comments.

Simple example workbook attached

-Lee

Hello,

Stacked column charts are not good for comparing values in the stack. Attached please find a suggestion using a Panel Chart. Jon Peltier's site has more tips on those, too.

Look at the bottom table starting in row 51 and the chart below that table. I created a number of gap series between each of the project series and formatted them with no fill. An overlaid XY scatter chart creates horizontal lines to supply a zero line for each project series, to simulate the grid lines. This is necessary since the Y axis grid lines do not always match the required grid.

Now each project series has one base line and it is easy to compare how each project is tracking month by month.

It is a little work to create the initial setup, but it is highly configurable and much more meaningful than the stacked columns.

Although I create the chart with Excel 2010 (which accounts for the colors), the same principles apply in 2003.

It all really depends on what you want to compare. Tracking several measures over time never really works with a stacked column chart. The panel chart, with each series on its own baseline, gives a much better view of the changes in an individual project.

Hope that helps.

cheers,

Hello,

I'd put the total cost into a separate chart. Integrating it into the panel chart would not make sense.

cheers,