fbpx
Search
Close this search box.

Project Portfolio Dashboard in Excel [Part 2 of 2]

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

68 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?

  30. Venkata says:

    Hi Chandoo,

    I would like to know apart from the Dashboard usage, can we have a facility to extract as a report showing all Projects status report as read only to all Stakeholders, I am not interested in sharing the whole Dashboard as it comprises of complex code.

    All I am looking is a button, which extract whole dashboard as a report in PDF or jpg format to share it with all stake holders.

    Please advise?

    • Chintua says:

      Dear Chandoo,

      Please can you reply to the question above (by Venkata).

      I'm about to get the dashboard but will need to have that feature as the Stakeholders will not be needing all the info the Prog Mgr sees.

      Thanks.

  31. Zafar says:

    Thanks Chandoo for sharing your excellent portfolio template with us.

  32. Phil says:

    Dear Chandoo,

    I purchased Portfolio dashboard yesterday; I am trying to modify it by adding Customer in front of Project name, estimated hours, and hours spent - I tried replacing Budget and spent title, and $$ format to number - the dashboard choked.

    Then I just tried to add columns in Project details, Customer, estimated hours, and hours spent. Obviously, I knew it wouldn't show up, so I tried modifying calculations table by adding these columns to the similar table spaces - yes, the page says, please do not edit.

    My need on this dashboard is more time, than money, although the money is secondary.

    How can I add these columns so I can view them on dashboard? Replacing the $$ in the left box would be acceptable; making it swappable would be even better

    Best Regards

  33. Phil says:

    One more thing. When I make an activity in Project Plan 100% complete; the dashboard shows ####

    By the way; this dashboard is brilliant...

  34. Phil says:

    Has anyone tried Publishing this on a SharePoint website? If you have, what steps did you take to have the dashboard open without reverting opening with excel?

  35. Nick says:

    Just realized that on the Dashboard screen under "Projects" it says 8 Projects and instead of saying "% Done" it says "DIV/O! Done". any clue how to fix it so it says overall % Done?
    Regards

  36. John says:

    Can the template be customized to be used for other purposes or other fields added to the dashboard? For example I want to track project value instead of project budget

    • Connie says:

      Dear John,

      I was wondering if you got a response to your question and if so whether you could share the answer.

      Thank you

  37. Johan Dekker says:

    I purchased the whole package online however the dashboard only allows me 10 projects. Is it possible to get one that can handle 50 projects or more?

    • Matthew Coffin says:

      Johan - If you have not seen , the video explains how to add in more projects . However , my approach would be to try and group the projects , so that I would have a master Project dashboard for the groups, and then a group master dashboard for the projects.

  38. John Fomusa says:

    Mr. Chandoo,

    I have a friend that is building the same Dashboard as your "Project Portfolio Dashboard" ( https://chandoo.org/wp/2012/11/19/project-portfolio-dashboard-excel-template/ )

    My friend needs help implementing some of the features which requires one on one phone and remote desktop consultation. Are you available and what would be your rate?

    Regards,

    John Fomusa
    (T) 980.263.2051
    Fomusa Consulting Services, LLC
    http://www.FomusaConsulting.com

    Fomusa Consulting Services | IT Solutions | Website Solutions

    An IT solutions company for networking, software, computer, and website services tailored to small and medium size businesses.

  39. Markus says:

    Hi

    I am using your Excel file for high level Project Controlling and Portfoliomanagement.
    I really like it a lot but I have a small issue:
    I can not change anything on the dashboard itself as the page is protected.
    For me the Budget needs to be formatted differently: Smaller Font and no decimal places are needed. My budgets for (ERP) Projects are relatively high and it is very difficult to read the figures in the Budget box.
    How could I change this?

    Thx a lot for any answer in advanced.

    Viele Grüße / Best Regards

    Markus

  40. Padmaraj S says:

    I had purchased unprotected version today, EBS Payment ID: 140686093. Do let me know when would i receive the pack

    Regards,

    Padmaraj.S

  41. Christophe Leribaux says:

    Hi,

    to accomodate our project managers and the portfoliomanagers in the company (JSR, Belgium) I'm interested to test version of the portfoliomanagement files you present in the PDF.
    It the one with an overview of all projects and visibility of the details when you click on a project. (Page 5 of the PDF). I wonder if is possible to send me a BLOCKED version. If this would be acceptable for the users, I'd order the full package.

    best regards and thank you for you quick response,
    Christophe

  42. Freddie Wofford says:

    I just purchased the project portfolio templates. On the dashboard in the Ganett chart view the scroll bar on the right hand side does not appear. How can I fix that.

    Thank you.

  43. Freddie Wofford says:

    I am having another problem on the dashboard. The block for the "next 6 months in the below projects" is not calculating any information. How can I fix it?

  44. Marco says:

    Hi.

    Great template, however, there should be an option for the traffic light.
    Currently you can set the colors only by completion ratio which makes not much sense.

    The traffic lights should be used to show if the project is on track, potentially delayed or in delay. If a project is scheduled to start only next month the progress is 0% it deserves a green light....

    Suggest to have a option for traffic lights:
    - based on completion ratio
    - based on task delay
    - based on risk status

  45. George says:

    Hello Chandoo,

    This looks to be exactly what I need to establish a portfolio framework for my organization. With my affinity with Excel and Mind Mapping I am looking forward to using the software.

    I have already purchased the full bundle pack. However, I would like to be able to view your 12 min overview video as I start to unlock the tool features. The video link on the website is not available for me. Can you please send me the video url or other means to access the video.

    Thank you,
    George

Leave a Reply