Displaying complex project data in an easy visual way

[damianjmcgrath]

I have a spreadsheet with a large amount of complex data, related to work projects.

There are 50 projects varying in size between Small, Medium and Large. Each project has 8 activities associated to it. Depending on the size of the project, each activity is assigned an amount of days. At the moment, these are a fixed number, for example, if the project is Small, then Activity 1 is 5 days. If the project is Medium, then Activity 1 is 10 days, etc.

Under each activity, there is a list of 18 teams. Some, not all, teams are assigned some work within each activity, and this work can be concurrent. For example, under Project 1, and under Activity 1, (so a total of 5 days), Team 1 might be assigned 2 days, Team 2 assigned 2 days, Team 3 assigned 2 days and Team 4 assigned 2 days. This equals 8 days in total, but because the teams can work concurrently, this fits within the 5 total days.

At the moment, my spreadsheet is split into 2 sheets.

Sheet 1 has the following columns:
- Project Name
- Size
- Start Date
- Activity 1 Length
- Activity 2 Length
- Activity 3 Length
- Activity 4 Length
- Activity 5 Length
- Activity 6 Length
- Activity 7 Length
- Activity 8 Length
- Total Length
- End Date

Sheet 2 has the following columns:
- Project Size
- Team 1 Length
- Team 2 Length
etc down to Team 18 Length


What I need is some sort of chart, like a Gantt chart maybe, that shows me all of the projects and activities, using the Start Dates and Lengths to show where each activities starts and ends. The activities are done one at a time, so when one ends, the next one starts.

On top of this, I need a way of plotting on the teams. Not sure how, but the objective is to see when each team is too busy. Perhaps by colouring the bars differently, so I can visually see how often it appears in the same week/dates? I have no idea how to start this.

I can probably put together a basic Gantt chart showing just the activities, but it's the teams information that's confusing me.

Does anyone have any pointers to give me? If anyone can mock something up, obviously using much smaller volumes, I can scale it up accordingly. I just need something to start with!

Any ideas?

 

[Steve=True]

Hi Damian,

This may be a real eye chart with 18 teams.

Here is my sample.

Here is more information on Gantt chart basics:
How-to Make a Basic Gantt Chart in an Excel Chart in 7 Easy Steps

Let me know if this helps

Steve=True

 

[damianjmcgrath]

That looks almost exactly what I wanted! That's ridiculously good. Is there any way at all I could get a copy of that spreadsheet so I can amend it with the correct details? Can I PM you my email address?

 

[Steve=True]

Thanks for kind words.

Glad you like it.

Steve=True