1

Ideally, I'd like to use an Excel spreadsheet to generate the following for me from columns of "User Story", "Start", and "End". Even if it didn't take it from the spreadsheet and I had to enter all the epics in manually, it would be better than manually creating all those dang boxes and moving them around.

enter image description here

In the diagram above, the columns titles indicate the bi-weekly sprint. "1507-2" just means 2nd half of July 2015, it's a shorthand used forever to communicate our entire company, to avoid ambiguity of saying "Sprint 17".

What I'm looking for is essentially a Gantt chart, but just a friendly version. How can I generate this type of chart from Excel?

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.