An Excel Project Planning Spreadsheet

Project planning and progress tracking is an essential part of any successful project, no matter the size. Part of ensuring success is communicating progress with project stakeholders. For small projects, simple meetings will suffice, but anything involving more than a couple of tasks and people typically requires some form of formal project documentation. While Microsoft Project is a great tool, not everyone uses it and while they have a “viewer” version, sometimes it just doesn’t seem worth the hassle. You may want to use a Gantter a free Microsoft Project alternative, sometimes I think it’s just easier to use Excel. While it’s not free – it’s certainly popular, easy to use and offers the ability to export and/or save as PDF files for easy sharing.

Excel Project Plan Example

Excel Project Plan Example

This is why I almost always create a project plan with a Gantt Chart using Microsoft Excel when embarking on a project. In this tutorial, I’ll walk through the steps I took to create a sample Excel-based project planning spreadsheet with a nifty gantt project schedule that shows who’s doing what, when and how complete the tasks are.

Features

I’ve included some important features in this example. Most notably is the Gantt Chart schedule view of the project which automatically provides a view of who is performing what tasks on what date. The example includes logic to skip around weekends and non-working days as well as a separate column to track progress against tasks or goals.

If you’d prefer to skip the article and simply download the excel project planning template, you may skip to page 4 using the page links below to access the download link.
Continue Reading on Next Page

46 thoughts on “An Excel Project Planning Spreadsheet

  1.  

    Thank you. This is VERY useful and it will make it so much easier for me to quickly produce basic gantt charts for proposals.

  2.  

    I found your comment on Smartlife blog in my hunt for an excel sheet like yours and followed the link here. Thank you - it's a nice template, easy to adapt

  3.  

    So..How do you download the template? I was on your site at http://www.mlynn.org/2012/05/excel-project-planning-spreadsheet/ I read all the info but I never saw a link for the free download. Can you please help.

    thanks,

    Marc

  4.  

    I download the "Excel Project Planning Gantt Spreadsheet (1485)" template, but files are in xml format. No idea how to convert into MS excel format. Could you please help me out how to make into excel format.

      •  

        Mike, Thanks for your quick response. I download as a zip file (i.e. Excel_Project_Plan.zip). When I unzip, all the files are in xml format. I couldn't see any file with *.xls. No idea how to get the template in *.xls format. Please help me out to resolve this issue.

        •  

          xlsx files are, in zip archives that contain XML files and often other files and sub-folders.

          If your downloaded file has a .zip extension it would be because your browser (IE?) recognised the file type and attempted to be helpful by renaming it.

          To fix this problem rename Excel_Project_Plan.zip to Excel_Project_Plan.xlsx and it should open in Excel 2007 or later.

  5.  

    Hi Mike,

    THis is great tool. I tried to use however the dowloaded excel sheet does not color anything. I see all formulas but don't see any colored cell - green or blue. Am I missing something?

    •  

      Hi Marco,

      I hit the same snag. It seems the coloring works in Excel 2010 but not on earlier versions. I'm guessing it has to do with the fact that the coloring is based on a formula powered conditional formatting, a feature that was introduced in Excel 2010.

      I hope this helps.

      •  

        Well, Excel 2007 does have formula-driven colouring. It's the formula which doesn't work. Excel reports an error, even when I simplify it (remove the lookup to the separate sheet).

        It just says there's a problem with the formula. Any ideas?

  6.  

    I am using this template and built a project plan that shows a daily calendar. I want to summarize and show the plan by month. How do I do that?

    Thank you!

  7.  

    Hi There!

    I am not able to download the file and it show error msg after clicking the link you have provided in the comments above. http://www.mlynn.org/wp-content/plugins/download-monitor/download.php?id=13

    Please help
    Vidya

  8.  

    Thank you Mike!! I was asked to create a project plan today. Having never done anything like this, I downloaded your spreadsheet and am half way done.

    You are a lifesaver!!

      •  

        Mike,

        Keeping in mind that I am very new to this, I have a question. On your legend, the red box means tasks overdue start. I have a task that begins on 3/25 and ends on 3/29. This task happens to be 33% complete. Why is the 3/25 box red? The task has been started and it has an additional 4 days before it is overdue. Shouldn't the task continue to be blue reflecting active until the task is truly running late, or is overdue?

  9.  

    Hi Mike. This spreadsheet is great! I would like to add a recurring task that only occurs on Wednesdays. How would I adjust the code to color only Wednesday cells? (I don't think I saw this addressed in the article, but if I missed it I apologize in advance!)
    Thank you for sharing this-- it's fantastic.

  10.  

    Hi

    Nice spreadsheet. The project I run is defined i weeks not in days.

    How do I use weeks instead of days i the Gaant section.

    Regards Jesper

  11.  

    Hi Mike,

    Great spreadsheet! Would you be able to extend it and show us how to add sub-tasks to create a WBS?

    Thanks!

  12.  

    This is a really good spreadsheet. Not only is it useful in itself, but I've now learned a lot about conditional formatting. Great stuff. Many thanks.

  13.  

    Your karma is blessed for the next 10 lifetimes for sharing this amazing tool for the benefit of all -- especially non-profit organizations like mine! I am crossing my fingers that I can figure out how to synch the dates in Outlook...

    Thank you so very much.

    •  

      Not sure - I would imagine software specifically designed to do gantt charts may be more full-featured. Excel is awesome at many things... but my project management example is probably stretching excel's capabilities.

  14.  

    Mike, Thoroughly enjoying your spreadsheet; it's the best I've located so far! Thank you so much for sharing with us.
    I'm running into a few concerns that you can hopefully address with the Gantt conditional formatting.
    1. Current Date with Red left and right border is extending to PAST dates as well. Easy way to change so only TODAY's date is bordered Red and past dates are black like future?
    2. Some tasks with start and end dates in future of Today's Date are showing red. IE: Start, 1/31/2014 red fill on gantt (today is properly set at 1/30/2014). Same issue with longer tasks; past start date, 1/20/2014, future end date, 2/3/2014, 50% completed (or even 0%) showing red or yellow (respectively) for 1/31/2014.

    Thank you for your input.

  15.  

    Thank you Mike!! This is best and easy to understand project planning spreadsheet ever!! I will tweet or put this on my social page. Thanks again mate

  16.  

    Great worksheet. I'm trying to get the small company I work for to do simple project plans, as spoken ideas rarely get done!

    however, we work weekends and days off are Monday and Tuesday. I tried to follow your logic, but cannot see how to change the shaded weekend days. Could you offer assistance?

    It seems the WEEKDAY function is only to count weekdays, but one can't select working days to be different.

    Thanks in advance.

    •  

      Answered my own question:
      I re-read your notes and searched "Conditional formatting"
      Tab - Home
      Drop-down - Conditional Formatting>manage rules
      Change to:
      =OR(WEEKDAY(H$2)=2,WEEKDAY(H$2)=3+$I$5,NOT(ISNA(VLOOKUP(H$2,Sheet2!$A$2:$A$7,0,FALSE))))

      WEEKDAY 2= Monday and 3 = Tuesday here - voila!

      Thanks again.

    •  

      I answered my own question:
      I re-read your notes and searched "Conditional formatting"
      Tab - Home
      Drop-down - Conditional Formatting>manage rules
      Change to:
      =OR(WEEKDAY(H$2)=2,WEEKDAY(H$2)=3+$I$5,NOT(ISNA(VLOOKUP(H$2,Sheet2!$A$2:$A$7,0,FALSE))))

      WEEKDAY 2= Monday and 3 = Tuesday here - voila!

      Thanks again.

      •  

        Ah!
        now i don't know how to change the different colored tabs.
        Could somebody please point me in the right direction as I'm having difficulty decoding the formulas...

        Sorry.

  17.  

    Thank you so much. I am always looking for Project Management Excel Spreadsheets. My goal is to have one spreadsheet with multiple tabs for the Whole project. I am slowly working on a spreadsheet for Project Status report.

    Thanks so very much for sharing your spreadsheet and your knowledge.

  18.  

    Hi Mike

    Great spreadsheet it has helped me out so much.

    Do you have a spreadsheet for working out project costings?

    Many thanks

  19.  

    Hi Mike,
    Great tutorial and gantt chart spreadsheet - thanks!

    I made the following customisations:-
    O
    1. Edited out the weekends as not being working days from all relevant formulas
    2. Changed the overdue and running late formulas to be "DAYS360(..)< 0" rather than "DAYS360<=0" so a project doesn't show overdue or running late when a task is due to start on today's date
    3. Set the date for I3 to be H3+1 and copied that formula across the rest of the range so I only have to set the first date (H3)
    4. I wanted today's date to be highlighted in white and all other dates to be greyed out (the reverse of how it was set) so I selected rows 2 and 3 for the date range and edited the conditional formatting to be OR(DAYS360($B$29,H$2)0) instead of DAYS360($B$29,H$2) <=0

    Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>