Tracking Project Portfolios in Excel

How to present a multi-project portfolio of projects tracked in Microsoft Excel.

Are you responsible for a portfolio of projects? Are you using Microsoft Excel to manage individual project schedules within your portfolio? Are you required to present schedule status periodically and prepare a schedule Gantt chart slide showing the schedule status of your portfolio of projects? If you have difficulty making project portfolio schedule charts from Microsoft Excel data, OnePager Express can help.

OnePager Express, an add-in to Microsoft Excel, provides a comprehensive schedule graphing tool to use to create and maintain a Gantt chart presentation slides. If you don’t already have OnePager Express, you can download a 15-day free trial to see how it works.

1. To start, you need to create a Microsoft Excel file representing the projects in your portfolio that will be used to maintain an up-to-date status of the projects as they are worked on and completed. Suppose you have three projects in the portfolio you are managing. They may be represented in one Microsoft Excel spreadsheet, across three spreadsheets in the same book, or between various Microsoft Excel files.



2. After you’ve created your Microsoft Excel file, make sure that you’ve inserted a flag column which will control what OnePager Express imports to make your project view and periodic snapshots. The figure below shows the Microsoft Excel file with “Show it1” used for this purpose:
   
   
   
   The Microsoft Excel file above shows the projects within the portfolio and the tasks within each project. All the necessary date information and associations with resources are also shown for this initial file representation of these projects.
   
   
3. Now just click on the OnePager Express button on the Microsoft Excel toolbar or Add-ins tab, and OnePager Express will do the rest. Give your Gantt chart a project view name and type in a status/snapshot date as shown below:
   
   
   
   In addition to telling OnePager Express where to look for the flag column (“Show It1”), this dialog box lets you give a specific name to the new Portfolio Project View and specify the date of this first snapshot.



4. In the figure above, the Show column mapping check box is checked, so when you click on the Next button, you are taken to the second page of the wizard:
   
   
   
   
5. The purpose of this page of the wizard is to give you additional flexibility in constructing your project portfolio view. Once you’ve verified that OnePager Express has properly referenced your desired information from the Excel spreadsheet, just click on the Create new project view button and the following graphic will appear:
   
   
   
   In this project portfolio view, the various projects in the portfolio are shown in the left-most swimlane column, and the resources are shown in the next column. Task names are shown and tasks are color-coded here by resources. Additionally, progress on tasks is represented by the small embedded bar in the task markers. It is well organized, colorful and annotated to support your schedule conversations.
   
   
6. You can now take this project portfolio view and copy it into a Microsoft PowerPoint presentation, email it to the team, or paste it into a Microsoft Word document for a customer.



7. Now let’s suppose that in another month, the various projects have made progress as shown by the colored cells and the Microsoft Excel spreadsheet is correspondingly updated to reflect such progress and on 4/15/2011 the spreadsheet looks like this:
   
   
   
   
8. It’s now time to make another project portfolio view to support the schedule presentation for the current month. Simply click on the OnePager Express button on the Microsoft Excel tool bar or Add-ins tab, and the following dialog box will appear:
   
   
   
   As this dialog box shows, we are going to update our Project Portfolio view with a snapshot dated 4/15/2011.
   
   
9. OnePager Express will import the updated data from the Microsoft Excel spreadsheet and produce the following project portfolio view snapshot when the New button is clicked:
   
   
   
   You can see where the changes are as shown by the note bubbles. Note further that the vertical blue line is now showing 4/15/2011 as the current snapshot date. Finally, OnePager Express has maintained (remembered) your color coding, swimlane and row labels, task names, notes and links from the previous snapshot. All this was done automatically, saving you time and reducing any potential for error. The snapshots are completely consistent from one to the next.
   
   
10. Now to get ready for the upcoming schedule conversation, just copy and paste the above snapshot into your Microsoft PowerPoint presentation and you are ready to go. It’s as easy as that. You’ve spent your time on managing your Portfolio of Projects and not on creating PowerPoint charts from scratch each time – a tremendous savings!

OnePager Express is extremely useful for conducting schedule conversations related to Portfolios of Projects. Also, OnePager Express' ability to take snapshots during a project enables project managers to monitor the actual project schedules visually against their baselines and prepare clear and accurate schedule slides for project status presentations.

Finally, OnePager Express helps you create dynamic, colorful, and informative project presentation slides with the click of a button and it eliminates the need to rebuild your slides by hand every time the project’s schedule changes.

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