Project Portfolio Dashboard in Excel [Part 2 of 2]

Posted on November 19th, 2012 in products , Project Management , VBA Macros - 42 comments

In this 2 part tutorial, we will learn how to design a project portfolio dashboard. Part 1 discussed user needs & design. Part 2 will show you Excel implementation.

Project Portfolio Dashboard Pack is now available.
Click here to get your copy.

Background: Designing a Project Portfolio Dashboard

As discussed in part 1, the biggest challenge when it comes to designing project portfolio (program) level dashboards is that, End users want it very concise yet powerful.

We have identified important needs of our end users & come up with a mock up design that meets all these. Refer to part 1 for that discussion.

Final Implementation – Project Portfolio Dashboard

First lets take a look at the finalized dashboard implementation. Click on it to enlarge.

Project Portfolio Dashboard using MS Excel - Download now

Construction of Project Portfolio Dashboard

Design philosophy for the dashboard

First let us understand the design philosophy for this dashboard, because that is what drives all the Excel work. Here is a mind map that explains how I approached the design of this dashboard.

Designing Project Portfolio Dashboard - Mindmap

Data Entry

Majority of data in this dashboard is captured using Excel Tables. This has several advantages:

  • Users can easily add more rows of data without worrying about the formulas.
  • Formulas are self-explanatory, thanks to structural references.
  • Data entry is easy, thanks to banded rows, headers & table styles.

There are 6 important tables:

  1. High level project details table
  2. People details table
  3. Each project’s plan details go in to a table, named plan1, plan2…plan10
  4. Risks table
  5. Issues table
  6. Holidays table

Calculations

Explaining each and every formula in this would take us until next years Christmas. So I will highlight key formulas & challenges faced:

Fetching relevant project plan from all plans:

This was one of the trickiest things. Since each plan has its own table, getting selected project’s table is necessary to drive all calculations. This is how its done.

  1. Define lstPlans as a list of all tables =plan1,plan2…,plan10
  2. Use INDEX to fetch one of the tables from this array like this =INDEX(lstPlans, activity-row-number, column-number, plan-number)

Sorting Projects:

This is done using 3 steps:

  1. Extract relevant data for all projects based on sort criteria (for example, sort by done % means we need done %s for all projects)
  2. De-duplicate this data by adding a small running fraction to them
  3. Sort using RANK formula

This is essentially same technique Robert taught us in 2008 in KPI Dashboards article.

Showing Daily, Weekly or Monthly Gantt view:

This is achieved by using below logic:

  1. For Daily gantt, see if date in the column is between start & end dates (more: Between formula in Excel)
  2. For Weekly gantt, see if the week start & week end in the column fall between start dates’ week start and end dates’ week end. (more: Date overlap in Excel)
  3. For Monthly gantt, see if the month’s start & end in the column fall between start dates’ month start and end dates’ month end.

Other important formulas:

  • WORKDAY for all date related calculations so that holidays & weekends are omitted
  • INDEX for all dynamic ranges so that dashboard remains responsive.
  • No UDFs .
  • Very few array formulas so that users can understand what is going on.
  • Structural references as much as possible so that formulas are readable, editable & dynamic. [More on Excel tables]
  • Used lots of named ranges to keep formulas readable.

Dashboard Display

This dashboard display follow box layout with simple colors, easy charts, picture links & lots of conditional formatting goodness.

To understand the important Excel features used in this, see below image & following list.

Excel features used in Project Portfolio Dashboard - Explanation

  1. Hyperlinks: for accessing other parts of the workbook & data
  2. Boxes & Text boxes: to show data & provide layout.
  3. Thermometer chart to show budget vs. actual performance
  4. Simple Column charts to show distribution of values
  5. Combo boxes for selecting sort & view options
  6. Scroll bars for seeing more
  7. Conditional formatting for icons, highlighting & gantt chart
  8. Picture links to embed project summaries & gantt chart views
  9. Option buttons to select a particular project

Dashboard Usability

Color Scheme:

Use default Office 2007 (2010) theme. This provides very good contrast, excellent color mix & does not surprise many people.

Fonts:

Only 2 fonts are used thru out dashboard. Franklin Gothic Book for content & Franklin Gothic Demi for headers.

Fonts used in Project Portfolio Dashboard

These fonts are specified in dashboard’s theme so that they apply by default when opened in any computer.

Printable:

The dashboard is optimized for print. All form controls & links are disabled for printing. This ensures that you get a clean printout with just data & charts.

Tip: You can disable printing for any object by right clicking > format > properties and un-checking the print option.

Macros:

Since the workbook uses macros, I have added a warning message that shows up when macros are disabled. A technique I picked up from Mike Alexander.

How this dashboard works [Video]

Since all this explanation might not do justice to the work, I made a short video [12 mins] explaining how the dashboard works. See it below:

Download Project Portfolio Dashboard

Now you can get this and 4 other project portfolio management templates (including simplified portfolio dashboard, time line chart, gantt chart templates). All these files are easy to use, beautiful to present, fully customizable, unlocked and designed to make you awesome.

Click here to get a copy of the project portfolio portfolio dashboard.

How do you like this dashboard?

I really enjoyed making this dashboard. It was challenging & entertaining experience for me. I think the final workbook summarizes performance of a bunch of projects in a concise yet powerful way.

What do you think? Do you like this dashboard? Do you often work with project portfolio / program level dashboards? How do they look and behave? Please share your feedback, suggestions & ideas using comments.

Thank you for your suggestions & feedback

Thanks everyone who sent suggestions thru emails & comments. I feel very happy about the way this dashboard has turned out. Thanks for your continued support for Chandoo.org.

PS: Go ahead and pick up Project & Portfolio Management template pack today, because you want to be awesome.

Your email address is safe with us. Our policies

42 Responses to “Project Portfolio Dashboard in Excel [Part 2 of 2]”

  1. […] Few days ago, we learned how to design a project portfolio dashboard. The next part talks about how to create this dashboard using Excel. […]

  2. […] That is all for this installment. In the next part, Learn how to create a project portfolio dashboard using Excel. […]

  3. romelsb says:

    What do you think? Do you like this dashboard? Do you often work with project portfolio / program level dashboards? How do they look and behave? Please share your feedback, suggestions & ideas using comments.
    Hi Chandoo,

    Thanks for sharing. Your portfolio dashboard with mindmap is Awesome !

    Please explain more about the dynamics/changes/results (# of project ends, etc) on status box i.e.

    Next 6 months in below projects
    in relation to input taken from 

    Settings

    Sort Types

    % Done (0 -> 100%)
    Due Date (Now -> Later)
    % Budget Spent (100 -> 0)
    Open Issues (a lot -> a few)
    Risks (a lot -> a few)
    Original Order

    —–
    thank you very much !

    • Chandoo says:

      The basic logic of this is explained in the KPI dashboard sort article here:

       

      In this dashboard, the logic is like this:

      • Based on the sort parameter, we extract one of the several columns of data for all projects and load in a large list (100 rows or so)
      • Then, deduplicate all items by adding a very very small unique fraction to them.
      • And apply rank() to extract ranked projects 1 thru n
      • Extract the first 5 corresponding Project IDs and feed them to calculations for rest of the display.
      • romelsb says:

        thanks for your reply,
        When I like to show to my GM
        “relevant data for all projects” in the Dashboard Header, besides or other than the KPI sort criteria…Can we insert another row of Header boxes as Top summary for All “n” project being tracked in the portfolio.
        How can we do this ?
        thanks again

  4. Michel says:

    Holy Nice-moly! This is awesome! Going to put lots of this to use! 
    Thanks in advance! 

  5. Red Shirt Ensign says:

    Received the Email with discount for previous PM purchases after I already placed my order, can you refund 20%   wasn’t sure where to contac tyou directly  I can provide Transaction information when you contact me!

    Thanks Dan

    • Chandoo says:

      Hi RSE.. Thanks for your purchase. I have issued 20% refund to you :)

      • Red Shirt Ensign says:

        Thanks for your quick attention, the templates are awesome… I plan to change mine up to focus on Sales/Business Development projects and how to track them to our needs…. includes more verbage and periodic status updating to track progress of each initiative
        Will be happy to share with you once I progress (Hoping to start revamp during the weekend
        Thanks again
        RSE

  6. Michael Lucas says:

    I love it! The only issue I’m having is, when I change an activity to 100%, it’s showing ##### on the Dashboard Gannt. Am I missing something?

    LOVE IT though. I can’t wait to show these off!

    • Chandoo says:

      Hi Michael… Thank you.

      To fix this issue follow below steps:

      1. Right click on any sheet and choose unhide
      2. Unhide gantt view tab
      3. Go to it and make sure the column width for column D is enough to fit in 100%. It is set up to show 100% value. But due to your computer settings, you may have slightly larger fonts.
      4. Once adjusted, follow the same steps for column CZ and EO as well
      5. Hide the gantt view tab again.
  7. Matei says:

    Is a demo available ?

  8. Thanks for offering a demo. This is really amazing work. Applications half this good cost well into the thousands of dollars (US). I think the roll-up KPI view is worth the price alone. Do you recommend this be owned by 1 manager or could this be stored on SharePoint and be updated by several people?
    Thanks!

  9. Amit Gupta says:

    Awesome….I am quite impressed with the Mind map Presentation. It seems like the foundation for each project. Could you share the software used

  10. Adam G says:

    Hi Chandoo,Your PM dashboards impressed me so much that I’ve downloaded the Portfolio and Project Management package. All of the documents look very professional.I was going through the Portfolio dashboard and I had a question.When I enter in additional holidays they are highlighted in the gantt chart. Is it possible so that the name of the holiday shows up in the highlighted area of the gantt chart.ThanksAdam

  11. smax says:

    Can the portfolio management dashboard  be translated to spanish?   can the translation affect the functionality?
     
    thanks
     

  12. lstanley says:

    I have two questions:
     
    1. In Project Plan tab, Is the Duraction column in hours or days?
    2. In Dashboard tab, the scroll bar displays each project, but in my case,  the entry Months displays ZERO. Why ? Which data is missing?
     
    Thank you for your answers.

  13. Renato says:

    Hi,
    I acquired a project portfolio dashboard, but i have a doubt:
    The Duration is in hour ou day ?
    I´m asking because i have activities in my projects in hours.
    Tanks.

  14. Martyn says:

    Hi,

    I want to know if I will always have to convert the reports to pdf before I sent them to the project team or I am able to e-mail them on an excel format?

    Martyn

    • Hui... says:

      @Martyn
      This is not really an Excel question, but more of a Company Policy style question.
      You can email any files including Excel and PDF files to others.
      With PDF the end user can only browse the worksheets, they cannot change/edit the data or results

  15. manu says:

    This is awesome .. Nice work chandoo !
    Is there a integration with mpp? I put all my data in mpp and would be nice if there is away to integrate this with mpp. thanks !

  16. James says:

    Hi Chandoo,
    Great spreadsheet, but how do i change the currency in the dashboard?
    thanks.

  17. manolito says:

    Hi Chandoo,

    Very useful and powerful tool!

    I’m trying to add an 11th. project and I followed your instructions of copy-paste the table and rename it as plan11. However, the dashboard is not bein updated with the activities… doing some research looks like in the Calculations tab, the IFERROR formula is getting an error from the values in the lstPlans Matrix (starting on cell D29).

    Don’t know how to move forward, could you please give some advice on it?

    Thanks for your help!

    • Dale Braden says:

      Have you been able to resolve this? I am having the same issue with adding additional projects. I believe this is a great product and very easy to digest from an executive point of view but unfortunately support related emails have gone unanswered for me since running into this issue.

  18. Amit Roy Choudhary says:

    Hi Chandoo, I have made the payment a few hours back for your dashboard. I koow you have mentioned that it takes upto 24 hrs to send the link but could you send it to me at the earliest. I need to use it tomorrow for a presentation

  19. Nicolas says:

    Hi Chandoo,
    I have bought the entire pack and I am very happy with it. I was not looking at the project dashboard portfolio.
    On the gantt daily view, the “today” line does not appear.
    Besides that, I understand there is conditional formatting but I was wondering how the reference to the cell valGantstart links to show the “today line” for the gantt chart when the formula to show the “today line” on conditional formatting refers to this valGantstart.

    Thank you
    Kr
    Sacha

  20. Mrs N says:

    Hello Dear Shandoo,

    Ineeed to know more information about how to purshase this dashboard.

    Thank you.

  21. Zahir says:

    Hi Chandoo, your portfolio template is great, the one issue is the progress on teh project, if 100% is added in progress, it does not fully display, All that I see is ##### on teh dashboard. I would like to show your example to my colleagues and management. Is there a way to fix this issue?

  22. Paul says:

    Hi

    Have downloaded the trial portfolio template to see how the various elements work but the ‘calculations’ worksheet is hidden so I am unable to establish how the data is calculated? How can I access the hidden worksheets?

    Thanks
    ps. great job by the way!!

  23. kenneth says:

    Dear Chandoo,

    Kindly explain how your percentage of Projects done on the dashboard is calculated as this seems to depend on the budget?

  24. vijay says:

    I just purchased this tool and am trying it for my immediate need. It looks lean and effective. Have a few critical questions to be able to use this.
    1. project status is indicated
    using Green, Amber & Red colors based on many many factors. such as scope, cost and schedule status. The excel seems to use colors to indicate the percentage complete. A project could have been 75% complete but still be over budget, delayed in
    timelines etc. And such a project should be indicated in RED and not Green. How can I set the status color without linking to the
    percentage complete?
    2.How do i do this for Activities too?
    3.How do i map the team members to the
    projects?
    4. My portfolio has more than 5 projects. I added 5 more. But when i preview the print it only displays 5 projects NOT 10. How do I get to print all 10?

  25. Shiva says:

    Do you have contact number to reach you over the phone

  26. Rakesh says:

    Chandoo,

    Is there way to display a line to show the ‘Baseline’ within the dashboard under the Plan section? Or can you do this??

    How we log and track Dependencies??? May be like Risk and Issue you need sow Dependencies.

    I want to show RAG status for Risk & Issue (e.g. RED = High, AMBER = Medium and GREEN = Low)

    Chandoo…please respond to my query as I want to purchase.

    Thanks

    Rakesh Mistry

  27. Agustin says:

    I try downloading the trial from
    http://img.chandoo.org/pm/project-portfolio-dashboard-VBA-v1-2010-trial.xlsm to evaluate a potential using for my proyects but
    the downloaded files have no xlms format (only xml and bin files) extension so is no posible for me to test it…..maybe I am doing somethin wrong…

  28. Dan K says:

    I purchased, nice presentation on the Port Dash, seems basic on the data entry side of things. I was a bit thrown back on the entire data package contents, as there are some old templates in there, and repetitiveness in form. I was expecting a bit more, in comparison to what else is out there. Also in the fact that you have a great rep… I love your site, and input… so great job on that side of things. This is usually my first stop for examples….!

  29. Karl says:

    Hi Chandoo,

    Are there any issues in running this template in Excel 2007? Has the template held up good with Excel updates?

Leave a Reply