I have an exciting news & massive post for you.
As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. While this is not a huge achievement or anything, It certainly calls for celebration. I am so happy to see our mission to make people awesome in Excel is reaching out to more people everyday. Thank you.
To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates.
Share on Facebook
Tweet This!
Join our FREE Newsletter
Add Chandoo.org RSS Feed to your Newsreader
These 220 tips are broken down in to following areas. Click on the links to access them.
- Formulas [52 tips]
- Formatting & Conditional Formatting [36 tips]
- Charting [60 tips & templates]
- Tables & Pivot Tables [15 tips]
- Using Excel [47 tips]
- Free Downloads [5 downloads]
- Recommended Resources [5 resources]
Formulas [52 tips]
Learn 5 tips on how to use IF formula, CHOOSE Formula in Excel. [link]
Find out how you can sum or count values that meet a criteria. A must have tool for any analyst or manager using Excel. [link]
Lookup formulas are famous for a reason. In this article, you will learn how to use them to find anything, well, almost! [link]
While VLOOKUP is awesome, it can-not go left. That is why you must learn how to use INDEX+MATCH combination. [link]
OFFSET formula becomes very important if you ever plan to make an excel dashboard. So go ahead and learn. [link]
I just love SUMPRODUCT formula. And read this to find out why. [link]
SUMIFS & COUNTIFS formulas are like Swiss army knifes. They can do pretty much anything and still look slick. [link]
Find out how you can use ROWS or COLUMNS formulas to generate sequential numbers [link]
Learn how to calculate moving average using Excel. [link]
Learn how to write formulas to test Between condition. [link]
Calculate weighted average using Excel formulas [link]
Learn to write either-or (XOR) conditions using excel formulas [link]
SUBTOTAL is a powerful & low profile formula. In this article we explore the possibilities and show you some cool examples. [link]
If you use Excel to do anything more than counting your chickens, chances are you use VLOOKUP quite often. Read this to learn 6 important VLOOKUP tips. [link]
15 Formulas for Advanced Users
Anyone can write a SUM or IF formula. But how well do you know some of the powerful formulas in Excel. Read this to know 15 very useful formulas in Excel. [link]
10 Formulas & Tips for working with Date & Time in Excel
Dealing with dates is not something only for young people. Analysts and managers work with dates & times all the time. In this article, we explain 10 powerful ways in which you can work with Excel Date & Time values. [link]
Formatting & Conditional Formatting [36 tips]
16 Paste Special Tips & Tricks
Paste special is one of my favorite features in Excel. In this article, we explore various ways paste special can save us time. [link]
5 Ways to become Awesome using Conditional Formatting
Conditional formatting is your way of asking excel to change a cell’s formatting when a certain condition is met. You can use it do some pretty awesome things. In this classic chandoo.org article, learn 5 ways to use conditional formatting to become awesome. [link]
15 Fun things you can do in Excel in less than 15 seconds
How well do you know Excel? See this list to find out 15 simple but fun things you can do in less than 15 seconds. [link]
Charting [60 tips]
Thermo-meter Chart – Excel Tutorial & Free Template
Prepare a thermo-meter chart to compare actual performance with targets. [link]
Bullet Graph – Excel Tutorial & Free Template
Learn how to make bullet graphs using Excel. [link]
Comparison chart – Excel Tutorial & Free Template
Learn how to make an interactive comparison chart to compare one value with several others. [link]
Pareto Chart – Excel Tutorial & Free Template
Pareto analysis is very important in quality control, decision making etc. In this tutorial learn how to create pareto charts using Excel. [link]
Water Fall Chart – Excel Tutorial & Free Template
Water-fall charts are a good way to visualize how various components contribute to the overall outcome. Like how net profit is arrived etc. Know how to make these charts using Excel. [link]
Panel Charts – Excel Tutorial & Free Template
What are panel charts and how to make them using Excel? [link]
Gantt Chart – Excel Tutorial & Free Template
Gantt charts are an excellent way to plan for a project and track it. Learn how to use Excel’s conditional formatting, cell grid structure etc. to make Gantt charts. [link]
Project Timeline Chart – Excel Tutorial & Free Template
Timeline charts are a great for showing project progress and macro level picture. In this article, we show you how to make such milestone / timeline charts using Excel. [link]
Burn Down Chart – Excel Tutorial & Free Template
Burn down charts help you in visualizing how far the project has progressed. Learn how to create these charts using Excel. [link]
Dynamic Chart – simple example – Excel Tutorial & Free Template
Learn how to make simple dynamic chart using Excel data validation feature. [link]
Dynamic Chart with Checkboxes – Excel Tutorial & Free Template
Learn how to make a dynamic chart with checkboxes and interactivity. [link]
More Dynamic Charts – Excel Tutorial & Free Template
Get more examples, templates & tutorials on Dynamic charts using Excel [link]
In-cell Charts – Excel Tutorial & Free Template
Get more examples, templates & tutorials on incell charts using Excel [link]
Make compelling, effective charts by following these 5 simple rules. [link]
5 Chart formatting tips that cost you nothing and make your charts look cool. [link]
Come what may, do not make any of these 6 types of charts. They are ugly. Period. [link]
Charting Lessons from Optical Illusions
We come across optical illusions all the time. But do you know they also teach valuable lessons on chart formatting & Selection? [link]
Pie charts are ineffective in all but few scenarios. But how do you show composition of several values? Read this post to find 10 alternatives to pie charts. [link]
Back in 2009, we ran a contest on how to visualize budget vs. actual data. You can see 14 different charting ideas in this article. [link]
Tables & Pivot Tables [15 tips]
Excel tables are a new and powerful feature introduced in Excel 2007. Ever since I learned them, I have been much more productive while making dashboards or reports. Read these 10 tips to learn how to use Excel tables better. [link]
Pivot tables are a great way to analyze data and make reports. In this post, learn 5 powerful pivot table tricks & tips. [link]
Using Excel [47 tips]
Learn how to use Double click feature on your mouse to save time and become awesome in Excel. [link]
Learn how to use Mouse to become even more productive in Excel [link]
10 Ways to make your Excel Workbooks Boss-proof
While not all bosses are like Dilbert’s boss, we do realize that they like to press things and test. So it is a good idea to make your workbooks boss-proof. Learn 10 tips to do so. [link]
15 Excel Productivity Tips & Tweaks
Do you know that you can customize excel to make yourself more productive? You can turn off annoying features and set default formatting options to save precious time and make better worksheets all the time. Read this post to learn 15 such customizations & productivity tips. [link]
12 Rules for Making Better Spreadsheet Models
It is a good idea to follow a set of principles while designing a complex workbook model or dashboard. In this post you can find 12 rules for making better excel workbooks. [link]
Free Downloads [5 tips]
Using this handy one-pager, you can quickly remember the syntax for the most used formulas. Also find out what to do if there is an error. [link]
Get this e-book (available only on subscription to my newsletter) to enjoy 95 excel tips & tricks. [link]
Learn how to use Excel 2007 Ribbon
Learn how to use Excel 2007’s ribbon based user interface using this handy guide. [link]
12 Rules for Better Spreadsheets – Poster
Larry’s spreadsheet rules in a simple poster. Very useful if you work on spreadsheet models often [link]
Use this cheat-sheet to write better VLOOKUP formulas [link]
Recommended Resources [5 tips]
Join my Excel school program to learn Excel in a step-by-step fashion and become awesome. We have 23 hours of instruction on Excel & 8 hours of lessons on Dashboards. Click here to learn more. [link]
Excel Project Management Templates
Get a copy of my Excel Project Management templates so that you can save time and become a better project manager. The template pack has 24 templates to take care of various key areas of project management like planning, status reporting, tracking, timesheets etc. [link]
Learn 75 Excel Formulas [e-book]
In this easy to understand e-book, I explain 75 very important Excel formulas in plain English. Get a copy today and learn something new. [link]
Jon Peltier’s Charting Utilities
I recommend Jon’s charting utilities. You can make some of the custom charts that are not part of Excel charting gallery in minutes using his tools. They can save you lots of time and money. [link]
Excel Everest Training Kit for Learning Excel
I recommend using Sean’s Excel Everest if you are an Excel beginner. This is an interactive excel workbook designed to teach you various features of Excel. You can read my review. [link]
Thank you
Thanks for your support to Chandoo.org. Without you this milestone or anything else that we achieve is meaningless. I wish you even more awesomeness and knowledge in the months and years to come.
Share on Facebook
Tweet This!
Join our FREE Newsletter
Add Chandoo.org RSS Feed to your Newsreader
49 Responses to “Project Management Dashboard / Project Status Report using Excel [Part 6 of 6]”
[...] display milestones Part 4: Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project [...]
Excellent!
I was looking forward to this and you've done it again...Shame I can't claim it was all my own work 😉
ps hope you're getting enough sleep
Excelent !!! Tks to share your knowledge with us.
Izabel
Sao Paulo - Brazil
Nice job!.
I'm also keen on PM Excel Dashboards. Please, take a look at
http://screencast.com/t/TyaxH5r4mDf
That's one example of my Project control Spreadsheets.
Cheers
Hi Miguel,
Do you share your PM Excel Dashboards? It looks awesome.
Regards,
Germán
Hi M. Miguel,
Can you share your Excel Dashboards? Awesome work BTW.
Regards,
Michel Levesque
Can you share the PM excel template?
[...] haired Dilbert hat zum Abschluss einer Artikeserie zum Thema Projektmanagement mit Excel eine Anleitung zum Bau eines Projekt-Dashboards veröffentlicht. Ein Dashboard ist eine Visualisierungsform für große Mengen von meist [...]
Quite a nice and helpful article. I am sure excel is one of the most used application across many many big companies. And your info on project status update using excel would surely be usefull. Keep up the good work on this blog site. Also to share there are some open source flash-based graphing and charting solution which caould also be used on any project..
http://askwiki.blogspot.com/2009/07/how-to-create-quality-charts-using.html
@Alex, Izabel .. thank you 🙂
@Miguel: Thank you. Your dashboard looks very good. It is inclined towards the budget and finances of the project. I have kept those aspects out of this series. May be I will revisit the financial aspect of projects at a later point.
@Rishil: Thank you. Yes, you can create flash based charts (or even simple image based charts) and embed them in a project dashboard that can be published to the team using intranet (like sharepoint). This is how large companies usually do it. Thanks for sharing the Askwiki article.
Great looking dashboard!! Do you have a version for the Mac versions of Office available?
Thanks
Chandoo,
this is great piece of collating info.I liked it and shall try using it in office.
Thanks for the all hard work behind this.
Chandoo,
Kudos. This is really as simple as it gets for laymen. We did this sort of stuff in Consulting - but this can now become really simple for people. Will have my team look at this! Great work.
thanks,
Mrigank
[...] I suggest reading my 7 part series on project management using excel. Starting with Excel Gantt Charts to Project Dashboards. [...]
Just downloaded the project management template bundle...great!
Have you done anywork on a Project Portfolio Dashboard template?
@Bw... Thanks for getting a copy of the templates. 🙂 I have worked on few assignments where we built such templates. But these are similar to other regular dashboard templates. I will share some of these ideas in a later post someday. Meanwhile if you have any ideas on how to structure project portfolio dashboard, let me know using comments or email.
[...] to display milestones Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project [...]
[...] display milestones Time sheets and Resource management Part 5: Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project [...]
[...] to display milestones Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project [...]
Thanks fro the great ideas! To get a sense of the layout and design of a Dashboard more geared toward Cost and Schedule anaysis, check out the example Dashboard at http://www.ProjectDashboards.com which was built entirely in excel.
hey,
i just need a simple Chart where by i can show some of the projects by % wise. no dates required.
1st column Project name and 2nd column will be status (filled with %). can you pls help me out.
Thanks.
@DS... if you have excel 2007, you can use data bars in conditional formatting for this purpose.
Hi Chandoo - this series is an excellent resource and tutorial, thank you for sharing.
When I sat down to consider what my dashboard should look like, one of the most important features for me is to be able to maintain version control and to show simply on what version is on display.
Apart from the naming convention of the file name, is there a good way to do this within a dashboard? I'd be interested to hear your thoughts!
@Larph: Welcome 🙂
> You can do version control thru Macros (but always remember that your audience can disable macros)
> Another option is to use a static time stamp / version number in the title page of dashboard that you update manually whenever you make changes to the file
> In excel 2010, you can keep track of file versions from File menu. This can be used to select a previous version of dashboard.
> Best option is to use a version control system like SVN or upload files to Sharepoint or something like that. This will take care of versioning for you (although it is a bit technical and dashboard audience may have difficulty figuring the versions out).
> The easiest option is to use filenames and the CELL() formula to get the version number (or date) from the filename so you can show it on the dashboard.
Hi Chandoo... I'm following you from Brazil...
I would like to thank you for the tips about excel, mainly with dashboards ... It helped me a lot …
Take care...
Di
On the dashboard when I print, the text is blanked out in the middle of the Issues list - suggestions on how to fix?
[...] Project Management Dashboard in Excel [...]
Hi Chandoo, do you have an equivalent Project Management Dashboard / Project Status Report for MS Office 2010?
As a Microsoft trainer I'm interested in your choice of Excel for project management. I'm assuming that you've tried Microsoft Project and have decided not to use it? We get folks on our MS Project courses who've tried to use Excel for PM purposes and none of them have made such an impressive project plan, but I wonder is it worth all the effort?
This looks very interesting. How may I be a part of this
Does this template work in Google Spreadsheets?
Many thanks for sharing your expertise with us. Keep up the good work 🙂
Heya i'm for the first time here. I came across this board and I to find It really helpful & it helped me out a lot. I am hoping to offer one thing again and aid others like you helped me.
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.
Thanks
Adam
[...] Project Status Dashboard [...]
[...] Project Status Dashboard in Excel [...]
[...] Project Management Dashboard in Excel [...]
can you confirm that the downloads will work on a mac - excel for mac v14.3.6
thanks
Made a slight variation on the schedule sheet,
1. Add a date column for start
2. In week column cell use =weeknum() and link to date cell
3. Hide week column
When you enter in a date for each task the week number is populated accordingly
simple but more effective, you can also dynamically link the date cell to your MSP project file for even more automation!!
I purchased a copy of the project management dashboard excel file. I misplaced the password to unlock the file and make modification. Can you please resend the password.
Thank much in advance...
Hi there! I just would like to give you a big thumbs up for your great info
you've got right here on this post. I'll be returning to your website
for more soon.
I bought ur project management template just want to know how to hide the budget section from portfolio?
Hi, Thanks - very good job you've prepared!
You've inspired me as well 🙂
Best regards
Hello!
I am using a gantt chart template which i got from your website. All is good just when I add all my acitivities in data spreadsheet and then go back to gantt chart to view them, I only see first 9 and then I need to keep scrolling for the next ones. is it possible to see most of the activities if not all in the single frame.
thanks for answering!
This is my first time pay a visit at here and i am actually happy to read all at alone place.
I am interested in your dashboard; downloaded the locked version, unable to use it...do you have a user guide that is available that I can see and use on the locked version?
Please send me daily newsletter
Hi,
I downloaded the PM dashboard and the gantt chart only has dates till the year 2016. How do I change this to include 2017 FY as well.
When I enter a activity for this year , it fails to show up on the chart.
Hi
Would.like to purchase the project management .kits
Pls share the payment link in INR
Also share your contact number to speak with you
Regards
Hari
9384825926