October Sale - 20% of all courses - Book Today

Microsoft Project Gantt Chart

Easy Gantt Charts in Excel

December 05, 2018

Tracking a series of related tasks against time is a process we perform often in both the workplace and in other aspects of our lives (such as that summer project - hint, hint - or to organise that next holiday if you are a detailed planner like I am).

As humans we relate better to graphical representation of data, like charts and pictures.  Back in the early 20th century Henry Gantt, an engineer and consultant (although the term 'consultant' probably didn't exist back then) came up with a way to represent this, that we've all become very familiar with now.

The Gantt Chart

Project Managers live in these charts every day, with tools such as Microsoft Project providing detailed views and variations with ease (although a bit of training may be required to navigate a powerful tool like this).

For those of us without the need (or expense account) for full fledged PM tools, we can make use of the easier to reach options such as:

 

  • A 3rd party tool (web based tool or plug-in to an existing application)
  • An Excel Add-in
  • An Excel Template (someone has already done the hard work)
  • Do it yourself in Excel (you do the hard work)

There are more 3rd party Gantt Chart sites & tools than you can possibly count.  You may find a great solution in one of these, although there might be a small learning curve and possibly a need to create an account.  Some may also provide limited functionality unless a subscription is paid for (they have to earn a living somehow).

bing - Gantt Chart Online

Excel Add-ins are an under-rated and lesser known way of doing things in Excel that would otherwise be unachievable for us commoners.  Add-ins can be simple macro-style helpers that perform tasks within the sheet, or more powerful interfaces in and out of external systems.

Excel - top bar

There are some Gantt Chart Add-ins but, similar to the 3rd party sites / tools, can have limitations or costs.

Why you shouldn't do it yourself

The last resort is to do it yourself in Excel, with many well known Excel gurus blogging about how to do this in a small handful of steps based off a bar chart.  This is great for having full control over the appearance, but may require some hard work beyond the basic look and feel.

 

Just because you can do it yourself doesnt mean you should

 

Thankfully, Microsoft have  provided a Gantt Chart template we can use in Excel.  Launch Excel from your desktop and select New from the left navigation (or if already in Excel, click the File ribbon tab to go backstage then select New).

 

In the "Search for Online Templates" box type Gantt and press <Enter>.

Gantt Templates

 

There are a variety of chart types, for this exercise we'll select the Simple Gantt Chart.  Click on this (or your preferred one) and select the Create button when the description box appears.

Create

The template is pre-populated with mocked up data and begins today.  For this template there is an "About" worksheet with instructions and links to further help.  Also on the "ProjectSchedule" sheet is a series of help comments in column A - you'll see them in the formula bar.

Create a project schedule in this worksheet

Depending when you create the new file, you may notice the date format is US mm/dd/yy.  This is one of the constant battles between the US and UK date formats, and depends on the cell number formatting.

start - end

 

We recommend the very first thing you do is modify this.  Otherwise confusion is sure to occur, as you will need to input dates in the system locale (say UK format) but it will display per the number format of the cell !!

 

For the Project Start Date:

  • Select cells E3 to F3 (it's a merged cell so click on E3 or F3 only)
  • Right click and choose "Format Cells..."
  • In the Type field change the order of 'm' and 'd' so it becomes:  "ddd, d/m/yyyy"

Sample

 

For all the dates in the Gantt Chart:

  • Select from cell E8 to F33 (assuming you've made no changes to rows / columns yet)
  • Right click and choose "Format Cells..."
  • You can select any of the other display formats, or just modify the Locale (location) field to be English (Australia) or your appropriate locale

choosing location

A good practise is to choose a date display that is preceded with asterisk (*). So, it adjusts per the user's locale on their PC.

Then you can modify the chart contents using data entry. Even formulas if you want to link dates or use arithmetic to determine end dates based on duration.

Summer clean up Gantt Chart

 

Not every template is perfect so you should try a few to find the one that best fits your needs.  Some create a separate chart object while others represent the timeline in a table using conditional formatting (like this one).

And there are limitations in the layout (8 weeks on display horizontally). Which would need manipulation of the sheet to extend further.

Gantt Charts in Excel won't usually feature the rich project management functionality. Of task dependencies, linked start / end dates (although you can do this with formulas) or resource allocation.

 

If you'd like to learn more about formatting cells, using dates in formulas, condition formatting or creating charts in Microsoft Excel,  book at seat in one of our Excel courses.

Contact Us

What is Remote Training?

Valued by Individuals

4.8/5
Over 23623 Reviews
Very good course - I learnt a lot. Tamara was really patient and helpful. -R Intermediate Brisbane
Elly - R Intermediate, .
Learnt lots good stuff to use for my work. Thank you -Excel Beginner Brisbane
Adam - Excel Beginner, .
Thank you for your time and it was very informative. Many Thanks -Excel Beginner Brisbane
Morgan - Excel Beginner, .
it covered all my needs. Thanks -Excel Beginner Brisbane
Rohan - Excel Beginner, .
Great learning and enjoyed . -Excel Beginner Brisbane
John - Excel Beginner, .
Very pleasant, informative day. -Power BI Beginner Brisbane
Bobbette - Power BI Beginner, .
This has been a very helpful introduction to Power BI - we'll be able to move some of our reports across to Power BI sooner than we expected. -Power BI Beginner Brisbane
Karen - Power BI Beginner, .
Great beginner session - trainer very personable. -Power BI Beginner Brisbane
Tracey - Power BI Beginner, .
Very helpful and hands on. A comprehensive look at the basics of PowerBI - definitely interested in learning more in the intermediate course. -Power BI Beginner Brisbane
Daniel - Power BI Beginner, .
Very valuable session. Can definitely see how we will use this in the workplace. Steven was a great trainer and kept the room going all day, -Power BI Beginner Brisbane
Amanda - Power BI Beginner, .
Steven was a great trainer. -Power BI Beginner Brisbane
Fay - Power BI Beginner, .
Great overview. -Power BI Beginner Brisbane
Barbara - Power BI Beginner, .
Very informative, the presenter was thorough and welcoming to all. I never felt uncomfortable asking questions. I am also grateful for the Course Manual, so I can review the Agenda topics again at my own pace. -Excel Intermediate Sydney
Janelle - Excel Intermediate, .
excellent teacher very impressed -Excel Intermediate Remote Online
Michelle - Excel Intermediate, .
Very informative. Some great hacks -Excel Intermediate Remote Online
Matthew - Excel Intermediate, .
Thanks Michael, you were patient and happy to go back over knowledge whenever people needed it. I love how excited you got over some of the technical aspects - they were really interesting! Sometimes it was a little hard to hear, might have been connectivity issues on my end, not sure. Thanks so much! -Excel Intermediate Sydney
Kristie - Excel Intermediate, .
The topics chosen were very useful. I especially think the If statements, vlook up, pivot table and flash fill will work well for me in the future. Michael was very helpful and always stopped to assist those who needed further info. He was also happy to answers questions in general related to excel. Potentially needed some polls or questions within the training to check learning and understanding. -Excel Intermediate Sydney
Jessica - Excel Intermediate, .
Michael is very good teacher. It was a great session and learned a lot in a short time. -Excel Intermediate Sydney
Philip - Excel Intermediate, .
very good. Need to practice now -Excel Intermediate Sydney
David - Excel Intermediate, .
Jason gave us a really good overview of both Power BI service and desktop and was very helpful answering our questions. The course was very helpful in cementing and extending my basic understanding of Power BI -Power BI Beginner Canberra
Paul - Power BI Beginner, .

Trusted by Business

south east asia microsoft training courses nexacu

Follow us