stacked column chart - how to overlay multiple columns and maintain "gaps"?

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
271
I've been asked by our new training manager to chart employee progress against training plan. There are multiple training elements, each of which can have up to 5 modules (some only have 2).

I'm thinking of setting up something like the image below, but can't figure out how to get each "box" to colour as each module is marked as complete (based on another column containing TRUE/FALSE for each course/module.

I created the chart below by setting a value of 1 (or N/A#) for each module and creating a stacked bar chart, then manually colouring it to give an example of the desired output, but I can't figure out how to do the data layout or create a chart to update automatically. I'm sure I'll need multiple columns overlaid on top of each other, but I can;t figure out how to keep a "gap" for any modules not completed.

Any suggestions greatly appreciated!

training_chart.png
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
One possibility is setting up the chart as followed.
Then format each series "Training" as grey fill with black border, and each series "Training Done" with green fill and black border.
1607857847228.png


Book1
CDEFGHIJK
2TrainingAlphaBravoCharlieTraining DoneAlphaBravoCharlie
3Module1#N/A#N/A1Module1 Done11
4Module2#N/A1#N/AModule2 Done11
5Module3#N/A#N/AModule3 Done11
6Module4#N/AModule4 Done1
7Module51Module5 Done
Sheet2
Cell Formulas
RangeFormula
E3:E4,D3:D7,F3:F5E3=IF(J3,NA(),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,241
Messages
5,595,021
Members
413,960
Latest member
ikkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top