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.
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.
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.
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:
- High level project details table
- People details table
- Each project’s plan details go in to a table, named plan1, plan2…plan10
- Risks table
- Issues table
- 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.
- Define lstPlans as a list of all tables =plan1,plan2…,plan10
- 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:
- Extract relevant data for all projects based on sort criteria (for example, sort by done % means we need done %s for all projects)
- De-duplicate this data by adding a small running fraction to them
- 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:
- For Daily gantt, see if date in the column is between start & end dates (more: Between formula in Excel)
- 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)
- 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.
- Hyperlinks: for accessing other parts of the workbook & data
- Boxes & Text boxes: to show data & provide layout.
- Thermometer chart to show budget vs. actual performance
- Simple Column charts to show distribution of values
- Combo boxes for selecting sort & view options
- Scroll bars for seeing more
- Conditional formatting for icons, highlighting & gantt chart
- Picture links to embed project summaries & gantt chart views
- 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.
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.
- Download locked version of this dashboard and see how this works – Excel 2007 & above.
- Download unlocked version of this and more templates.
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.
68 Responses to “Project Portfolio Dashboard in Excel [Part 2 of 2]”
[...] Few days ago, we learned how to design a project portfolio dashboard. The next part talks about how to create this dashboard using Excel. [...]
[...] That is all for this installment. In the next part, Learn how to create a project portfolio dashboard using Excel. [...]
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 !
The basic logic of this is explained in the KPI dashboard sort article here:
In this dashboard, the logic is like this:
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
Holy Nice-moly! This is awesome! Going to put lots of this to use!
Thanks in advance!
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
Hi RSE.. Thanks for your purchase. I have issued 20% refund to you 🙂
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
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!
Hi Michael... Thank you.
To fix this issue follow below steps:
That worked! Thanks.
Is a demo available ?
Yes, please download it from here:
http://img.chandoo.org/pm/project-portfolio-dashboard-VBA-v1-2010-trial.xlsm
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!
Awesome....I am quite impressed with the Mind map Presentation. It seems like the foundation for each project. Could you share the software used
Hi Amit, I use a website called bubbl.us to create these mindmaps
thka a lot!!!
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
Thanks for your lovely comments & purchase Adam.
Unfortunately, there is no easy way to display holiday names in the highlighted portion. One alternative is to show list of holidays that occur in the filtered date range in legend area of the dashboard. For this you need to use formulas. As a start, I suggested looking at range lookup article. - http://chandoo.org/wp/2010/06/30/range-lookup-excel/
Can the portfolio management dashboard be translated to spanish? can the translation affect the functionality?
thanks
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.
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.
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
@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
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 !
Hi Chandoo,
Great spreadsheet, but how do i change the currency in the dashboard?
thanks.
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!
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.
I am having the same problem. what was the resolution?
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
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
Hello Dear Shandoo,
Ineeed to know more information about how to purshase this dashboard.
Thank you.
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?
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!!
Dear Chandoo,
Kindly explain how your percentage of Projects done on the dashboard is calculated as this seems to depend on the budget?
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?
[…] Project Portfolio dashboard using Excel […]
Do you have contact number to reach you over the phone
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
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...
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....!
Hi Chandoo,
Are there any issues in running this template in Excel 2007? Has the template held up good with Excel updates?
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?
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.
Thanks Chandoo for sharing your excellent portfolio template with us.
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
One more thing. When I make an activity in Project Plan 100% complete; the dashboard shows ####
By the way; this dashboard is brilliant...
@Phil
Make the column wider
hui,
I have made column wider (dragged column D in Gantt View), and the dashboard still shows ####. is there another place where I need to do this? I cant seem to edit the dashboard...
Best
Just realized, there were more than one Gantt in Gantt View. thanks @Hui
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?
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
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
Dear John,
I was wondering if you got a response to your question and if so whether you could share the answer.
Thank you
[…] | Read Sources […]
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?
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.
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.
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
@Markus...
You can purchase unprotected version of the templates from here: https://chandoo.org/pmt/pmt-index-1.html
You can easily edit every aspect of the files.
I had purchased unprotected version today, EBS Payment ID: 140686093. Do let me know when would i receive the pack
Regards,
Padmaraj.S
Thanks for purchasing this Padmaraj. I have already emailed you the download link.
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
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.
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?
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
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