0

I want to create an overlayed stacked percentage bar chart. My data (so far) has:

  • A column with 4 project names
  • Three columns (1 for each month (quarter)), with a number of work items
  • A column for the number of completed items
  • A "Bar" column for the thickness of the bar I'd like displaying progress

This is what my data looks like, so far:

Name    Jan     Feb     Mar     Done    Bar
IVS     27      42      31      60      0.75
GCS     23      41      36      55      0.75
CT      17      60      23      79      0.75
ATS     30      36      34      35      0.75

Here's the result I'm looking for:

Mock-up of desired results

This mock-up shows:

  • The number of monthly work items display in monthly segments
  • The total number of monthly work items represented as 100%
  • The bar represents the percentage completed of the overall total monthly numbers

I was hopeful when I found this post: Excel bar chart with gradient values (not percentages) and value lines. However it uses the fixed value stacked chart, not the percentage stacked chart. When I attempt to do the same steps with the percentage stacked chart, I don't get the same result.

Here are the steps I'm taking -- where am I going wrong?

As you can see, no "Bar" appears, but instead, the remainder of the rows are filled...

EDIT

Here's how I changed the setting (as you described by cybernetic.nomad): Change Chart Type dialog box

I get this: Chart with non-percentage based Bar position

I tested it with new data so the months would be in equal thirds and the bar at the 50% mark -- however, the bar isn't percentage, so gives odd results...

EDIT

I added a couple columns to create a total of the 3 columns and one to input the amount of work completed. Then I changed my "Done" column to "%". The bar now appears in the correct location, but again turned into a solid overlay (that extends beyond the chart).

Overlayed chart

1 Answer 1

0

Use "Stacked Bar" instead of "100% Stacked Bar" for the two series in the secondary axis:

enter image description here

The result should look like this:

enter image description here

If you absolutely want percentages, you will need to have your data match that:

enter image description here

Or introduce a new column with a calculated % of completion:

enter image description here

Create the chart as above, for the secondary axis, use 0 and 1 as the lower and upper bounds then format the numbers as percentages:

enter image description here

8
  • I didn't have the same menu as you, but was able to figure out how to change it. Why is the secondary scale different? Feb 21, 2020 at 21:47
  • You can change the bounds of the axis by right-clicking on it and choosing "Format Axis", Choosing an upper bound of 100 should fix the problem Feb 24, 2020 at 15:07
  • Please see my edits above -- the secondary axis is not working as a percentage as I'd expect... Feb 24, 2020 at 16:22
  • See edits to my answer Feb 24, 2020 at 16:31
  • Choosing 100 makes it a fixed scale. One project might have 130 items another 12. Making it a fixed axis won't work because 6 completed items is only 5% of the first project's work while it's 50% of the later one. Feb 24, 2020 at 16:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .