An Excel Project Planning Spreadsheet

Excel Project Management Template

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

About

Michael solves problems related to technology and business. Also, he types things into computers.

45 Comments

  1. Maggie Lynch

    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. Cath M

    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. Senthilkumar

    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.

      1. Senthilkumar

        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.

        1. jon

          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.

  4. ms

    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?

    1. Gabi Dobritescu

      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.

      1. Mathew

        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?

  5. Sara

    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!

  6. Amber

    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!!

      1. Amber

        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?

  7. Katie

    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.

  8. Jesper

    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

  9. jayB

    Hi Mike,

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

    Thanks!

  10. Neil

    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.

  11. Doreen Treacy

    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.

    1. mike

      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.

  12. Amy

    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.

  13. Pascal

    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

  14. David

    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.

    1. David

      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.

    2. David

      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.

      1. David

        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.

  15. Rich Medica

    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.

  16. Karenne

    Hi Mike

    Great spreadsheet it has helped me out so much.

    Do you have a spreadsheet for working out project costings?

    Many thanks

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>