Multi-Project Gantt Charts in Excel

How to use Excel and OnePager Express to combine multiple schedules into a single Gantt chart

If you keep track of projects using an Excel spreadsheet, you need an easy way to present your project schedules to executives and to your team. If you manage a portfolio of projects in Excel, summary presentations become even more important. Trying to force Excel into creating Gantt charts is tedious and time-consuming, and trying to do so for multiple projects is even more complicated.

This article describes how to create a multi-project Gantt chart in OnePager Express, using data you've already been tracking in an Excel spreadsheet. OnePager Express is an project management add-in for Microsoft Excel. If you don't have OnePager Express yet, you can download a 15-day free trial to see how it works.

1. Start with your multi-project Excel spreadsheet. In the example below, you will see a column for each task of the project, the name of each task's parent project, start and finish dates, and additional project-specific information:





2. To select which individual tasks and milestones you want to import into OnePager Express, insert a column into your Excel spreadsheet ("Add to OnePager" in this example). Place a "Yes" or "No" in the column to indicate which tasks you want to include in the multi-project schedule.
3. Click the OnePager Express button on your Microsoft Excel toolbar (Excel 2003) or Add-Ins tab (Excel 2007, 2010).
4. When OnePager Express launches, you'll want to name your Project View. In this example, we'll call it "Multi-Project Gantt Chart".
5. Now, you can specify which Excel column you want to use to filter your Gantt chart's tasks and milestones. Click on Select tasks with 'YES' in column and then choose the correct Excel Column (in this example, "Add to OnePager").
6. Specify a Snapshot (status) date for your multi-project schedule. You can use this later to version your Gantt charts and show changes over time.
7. Your OnePager launch screen should look like this:





8. Click the Next > button, which will take you to a wizard where you can specify which Excel columns you want to use in your Gantt chart.
9. OnePager Express gives you added flexibility by letting you use any Excel column to build your project schedule. To create a multi-project Gantt chart, make sure that the Swimlane titles column is set to "Parent Project". In this example, we'll also color-code by "Parent Project", though you could select a different column to drive color, like phase, resources, or budget. Your wizard should look like this:
   
   
   
   
10. Click Create new project view. OnePager will import your selected tasks and milestones and create a high-level portfolio view. Now let's make few minor modifications.
11. Go to Project View->Properties of the project view and go to the Swimlanes & Colors tab. Uncheck the Order swimlanes the same as Excel box at the top of the screen. Press Apply:
    
    
    
    
12. Before closing the form, go to the Main tab and choose the Best fit left/right option in the Task/milestone text position section.





13. Now, press OK and your Gantt chart should look like this:





14. With a little additional formatting, like repositioning task labels, and dragging Gantt bars up and down to save space, you can create a multi-project view that looks like this:
    
    
    
    

It's that easy! By using different filter columns in your Excel spreadsheet you can create separate project views using the same Microsoft Excel master file. For example, instead showing a detailed multi-project chart like we did using "Add to OnePager", we could insert an "Executive View Filter" column and select a different set of tasks (e.g. summary level) that were important to a particular team or audience.

This means OnePager Express can help you create a portfolio-level project plan for executives, while creating separate schedules for your engineers, designers, and marketing team, all using the same Microsoft Excel spreadsheet.

Get started today by downloading a free trial or attending one of our demonstration webinars.