Excel - Chart occurrences

Solution 1:

It looks like you are trying to build a Gantt chart in Excel. Excel doesn't have a built-in Gantt chart type, so the best way to do this is exactly like your example picture. You use the cells as a "chart" with conditional formatting to colour in the cells if it matches the date condition.

The quickest way to do this, is simply to google for a "free excel gantt chart template". Click on images search, and select one that looks closest to what you want.

If you want to do this yourself, try something like this

1. Add two columns for beginning (B) and end dates (C). If you aren't working with actual dates, but only terms, I'd suggest numbering them continuously across the years (so year 1 terms would be 0,1,2,3; year 2 terms would be 4,5,6,7 etc).
2. Now add a header row (3) "Term number" that runs 0..11
3. In the chart section, starting e.g. in D4 have something like =IF(AND(D$3>=$B4, D$3<=$C4), 1, 0)
4. Make the default text colour white so that you don't see a bunch of zeros and ones. Now add conditional formatting to make both the text and background colours black if the cell value is 1.
5. You can make it more advanced and colourful by adding a modifier column (A) and adding * $A4 to the above formula. Now based on a number you place in the modifier column (say 2), you can make the result in the chart section cells reflect that modifier number if it should be coloured in. Now set addition conditional formatting rules for each modifier number to colour the cells either blue or red etc.

Solution 2:

Excel has templates you can use for this.

File -> New -> search Gantt