I've built this a number of times in Excel. The easiest way I found to do this was to give each tasks (in your case: epic) a start and end date. (each task/epic in one row)
Next, create columns for each day in the time span you want to cover. You can narrow them down to single character width. Give these columns a header row with all the dates (insert actual date values).
In every cell of the matrix formed by the rows with your tasks/epics and the columns with all the dates, you need to implement the same formula, checking
whether the date of the current column is greater than or equal to the rows start date
whether the date of the current formula is smaller than or equal to the rows end date
If the above hold true, give the cell a value of 1, otherwise 0.
You can now, as a last step, apply conditional formatting to the entire matrix, coloring a cell if it has a value of 1 and setting text/background color to white if it has a value of 0.
Voilà, you have your GANTT chart.
You may want to add checks for empty values and weekdays/weekends and other exceptions.
The formula may look something like this:
=IF(OR(WEEKDAY([column date])=7;WEEKDAY([column date])=1);2;IF(ISBLANK([start date]);""IF(AND([start date]<=[column date];[end date]>=[column date]);1;0)))
Everything in brackets (e.g., [column date]) are just placeholders for the actual cell references.
If you want to only display your sprint names at the top of the "chart", you'll have to do some extra work, but that can probably be done by
- defining the start and end date per sprint sperately
- adding an extra row above the one with the column dates
- doing a lookup for each column for the name of the spring for the given date
- only showing the name when the same name is not contained yet in the cell to the left
- apply some formatting to make it all pretty
That way, sprint names will only show to the left of a "stretch" - but at least it would be somewhat automated.
Note that with this approach, one task/epic is always only ever in one dedicated row, so you will not be able to have the type of representation that you show in your sample, where you stack each sprint's lane dynamically. The above will create a waterfall (at least of you sort your records based on their start/end dates) and a lot of emtpy space.
Anything else can only be done in Excel with VBA IMHO.
If you're doing rather complex stuff or want to use this tool over a long period of time, you might want to reconsider doing this in Excel.
