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:
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?
- Created 100% Stacked chart
- Pasted "Done" column into bar chart
- Change series to Secondary Axis
- Pasted "Bar" column into bar chart, set to Secondary axis, set "Done" series to "No fill" & changed Gap Width to 80%
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).