Forum Discussion
Solved
How to build a dashboard with Excel
I'm about to start building a dashboard with MS Project data, but not having done it before I thought I’d ask some questions here.
Requirement is to show last week vs this week's data - currently only two weeks at a time.
It will be activity information, resource usage and utilisation and will include outline codes. Does anyone have their own product they would be willing to share?
We can't use Power BI, sadly as it's not available on our network.
Has anyone got any good YouTube videos they can link to that have helped them in the past?
Any help appreciated
David
- Hi John - yeah, that was a bit vague.
In my head, i've seen and used many different dashboards, and they're all relatively similar. What we said vs what we did, generally.
Resource utilisation - supply vs demand. 100 max utilisation vs what they've actually assigned to
We use outline codes to distinguish teams / contarctor agencies - so i'd like to be able to use these to auto generate / update the page
I'll have a look at PlannersPlace suggestion (thanks!) and see what i like
I envisage just taking a copy and paste from Project (gantt view and resource usage view) each week, pasting it in and dashboards updating
4 Replies
This is how I'd go about it.
- Before starting this week's progress update, I'd save last week's progress data using one of the baseline fields, say Baseline 10 (i.e set baseline to "Baseline 10" if not in use)
- Update the schedule based on this week's progress data
- Create a new View and add all the fields you want to a new Table, and they will be this week's data.
- Also add equivalent Baseline 10 fields of the fields added in #3 above and rename these Baseline 10 fields to have Last Week in their titles, e.g. "Baseline 10 Start" becomes "Last Week's Start"
- Save your View
- Export the View to Excel
- In Excel, use any of the many comparison charts to present your exported data
- Repeat steps #1, #2 & #6 every week
If you need ideas for displaying schedule data in an Excel dashboard, check out Ahmed's blog Ahmed Abdelfattah (ahmed-fattah.com)
=jerome
- David,
It's not totally clear what you're after but it sounds like you want to export Project data to Excel and then build your dashboard in Excel. Is that correct?
Your data requirements are rather general (e.g. "activity information", etc.). It would be helpful to know more specifics. For example, exactly what activity information? What type of "utilization"?
Depending on the specific data you want to see/use, I may already have macro code that exports that data from Project to Excel.
John- Hi John - yeah, that was a bit vague.
In my head, i've seen and used many different dashboards, and they're all relatively similar. What we said vs what we did, generally.
Resource utilisation - supply vs demand. 100 max utilisation vs what they've actually assigned to
We use outline codes to distinguish teams / contarctor agencies - so i'd like to be able to use these to auto generate / update the page
I'll have a look at PlannersPlace suggestion (thanks!) and see what i like
I envisage just taking a copy and paste from Project (gantt view and resource usage view) each week, pasting it in and dashboards updatingWell to be honest it's still a bit vague, (I'm a detail oriented guy), but thanks for the feedback.
Copy and paste is of course always an option but if something like this can be automated with a macro, that's the path I'd take.
Let me know if you need further help.
John
