1

I go nuts at Excel. So here's my problem: I have a table where the Y-values are projects that we are working on in that year on the x-values are project related properties. On of those properties is the month in which the project started.

So if you look into that column you'll find Jan, Feb, ... of which a single value might occur several times, because many projects might start in June and other in October. Now I could use a pivot table to see how many project started for instance in June.

But what I want is a stacked bar chart showing the names of those projects that started in Jan, Feb, Mar, ... And if possible the cash-value (another column of the table) used for the stack.

Here's a sample table. PS: I'm using Microsoft Excel 2011 for MacOS.

Project, Volume, Start

ProA, 80.000 Eur, Jan

ProB, 25.000 Eur, Jan

ProC, 12.000 Eur, Mar

ProD, 50.000 Eur, Sep

ProE, 35.000 Eur, Sep

The diagram should look like this:

Stacked Bar Chart

1
  1. Create table like this:

    Project | Volume | Month
    ------- | ------ | -----
    ProA    |  80000 | Jan
    ProB    |  25000 | Jan
    ProC    |  12000 | Mar
    ProD    |  50000 | Sep
    ProE    |  35000 | Sep
    
  2. Select all these cells, and select Insert > Pivot table > OK

  3. Now, select any cell inside added Pivot table, and in PivotTable Fields, drag corresponding columns:
    Month drag to ROWS box
    Project drag to COLUMNS box
    Volume drag to VALUES box.

  4. Check, if Volume fields in values box must appear as "Sum of Volume".

  5. Now, the fun part, - select any cell again in PivotTable, got to (In Excel 2013 for Windows) under PivotTable Tools > Analyze > PivotChart.

  6. Select "Stacked Column" table.

See The image:

enter image description here

Now you can add labels by select any part of bar and format it accordingly.

  • The PivotTable worked fine. But I couldn't find the Analyze-PivotChart menu. But I simply selected the PivotTable as a whole and added a stacked chart. But that chart can not be modified :( Thanks a lot. youtube.com/watch?v=1DpauY4J-p0 – Matthias May 26 '15 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.