Build your own Gantt Chart Template in Excel - Free DIY Guide
After 3 years of building Gantt Chart templates, we have decided to share some of our knowledge and experience with those that may be keen to build their own.
This Do It Yourself (DIY) guide is for those that would rather invest the time and energy to build their own template.
Whilst the below guide is not a complete encyclopaedia on Gantt Chart based planning it should provide you with some unique insights into how to construct your own Free Excel Gantt Chart Template.
The guide is designed for those using Excel 2007 or later. If you are using Excel 2003 or earlier, you will find that there are a number of limitations that may prevent you from achieving the full functionality outlined in this guide.
It is expected that this Gantt Chart guide will be used in conjunction with other online gantt chart resources so that you can create from scratch a Gantt Chart for Excel that closely meets your requirements.
We suggest that you bookmark this page so that you can refer back to it throughout your build process.
Having taken 3 years to get the Chart gantt products to the stage we have, we would expect that it would take you a minimum of 16 hours to construct an operational gantt chart using this guide.
There are a number of complexities that arise when building excel gantt charts that do not seem obvious from the outset.
The guide is expected to be just that a guide, and you may need to use your initiative and resources to solve some complex planning issues.
CONTENTS OF THIS GANTT CHART TEMPLATE GUIDE
Why do I need a Gantt Chart?
Using this workbook to create your own Gantt Chart
Principles of an effective Excel Gantt Chart Template
Features of a Chart gantt DIY gantt chart
Benefits of a Chart gantt DIY Gantt Chart
Building an Excel Gantt Chart Template – The Basics
Structuring your worksheets – Calculation and Display sheets
Setting up the Display Sheet
Setting up the Calculation Sheet
Setting up the View Selection Data
Copyright and Intellectual Property
WHY DO I NEED A GANTT CHART?
A Gantt Chart is a standard way of visualising time that is widely accepted across the world. The graphical nature of a Gantt Chart has enabled it to become the standard across language barriers.
An effective Gantt Chart lets you see immediately what should have been achieved at any point in time. It acts as a simple and effective way of visualising your project and communicating the tasks and timelines to others.
USING THIS WORKBOOK TO CREATE AN EXCEL GANTT CHART TEMPLATE
It is expected that many users will just use elements of this workbook to construct their own gantt chart. Users may use this workbook to build their own Gantt Chart in excel, or they may use it to better understand the construction of the Chartgantt products.
It has been developed for Excel users that are familiar with complex formula and sheet construction.
The workbook will provide the advantage that you will understand the exact construction of your gantt chart, its capabilities and limitations.
This workbook is designed to be used in conjunction with Chartgantt Free, which will act to guide your development efforts. This guide will assist you to develop your own version of Chartgantt Basic.
Depending on your level of proficiency with excel, the construction of your gantt chart would be expected to take between 4 and 12 hours.
You may decide to omit features that are included in Chartgantt Basic, particularly if you are only planning to create one or two small projects with your template. Alternatively, you may wish to add specific features that are unique to your template requirements.
The workbook has been developed for Excel 2007 or later users. Due to limitations on earlier versions of excel, the status field will not be able to be used by 97-2003 users. All other features are able to be created.
This workbook is designed as a self-guided tour, no further support will be provided by Chartgantt. To further de-construct the formula’s on the main planning page, consider purchasing a Chartgantt template.
The forum below is for discussing Excel Gantt Chart Template frustrations and fixes.
PRINCIPLES OF AN EFFECTIVE GANTT CHART
An effective gantt chart has the following key features:
1. It must be an accurate representation of your plan.
2. It must be simple for other users to interpret.
3. It should be as automated as possible to remove risk of input or transposition errors.
4. It should be fast and easy for an input user to use.
5. It should enable both the planning of activity and the management of activity.
FEATURES OF A CHART GANTT DIY GANTT CHART
Chartgantt products have been designed to offer many of the features of sophisticated planning solutions, but at a fraction of the cost. After selling high end planning solutions for many years, I realized that the vast majority of users just need something that is easy to use, and can be sent to others for editing.
With mainstream planning solutions costing in excess of $500, the author of the gantt chart and the reviewer, seldom have the same license, making collaboration difficult.
Even with complex planning solutions, many people end up working in Excel and then transferring the data to their other solution. This can result in errors, and reduces the flexibility in the planning process.
A ChartGantt gantt chart is designed to have the following features:
• Everything is done in one workbook in Excel
• Perform calculations and functions that are normally only performed using macros, without using macros
• Simple layout with the ability for users to add and remove columns or rows as required.
• All views of your plan are updated within the one planning sheet, key calculations are performed elsewhere.
• Instant response to date information entered.
• Ability to customize key planning information such as weekend days.
• A variable set of views for your data without scrolling or switching sheets
• Use of conditional formatting rather that graphs as the graphical engine.
• Active status field (office 2007 or later users only) that helps you manage your project.
BENEFITS OF A CHART GANTT DIY GANTT CHART
To build the first Chartgantt product took an Excel expert 20 days. It has since taken 3 years of refinement to get the product to the stage it is today.
There were many complex issues and calculations that needed to be resolved to produce what you see in the Chartgantt products. This manual will take you on a guided tour of these, with the benefit of knowing that they have been resolved.
The GanttChart products offer users the following benefits:
• Unparalleled flexibility
o By enabling the user to set the start date, we are able to cater for a plan produced at any time, be it today or in 5 years.
o Need to plan by minutes, no problems, just add a column and build a formula to translate from days and away you go!
o The template is primarily a visualization structure, everything else is at the users discretion. Add a new column to perform any function you can think of.
• Faster planning
o The template takes care of all the complexity, the user is left with a very simple interface.
o Just enter or paste in your tasks and dates, it is that easy!
• Add on new functionality
o Add new functions that are important for your project. All you need is a basic level of knowledge of excel.
o Integrate your plan with other spreadsheets or databases.
BUILDING A GANTT CHART IN EXCEL – THE BASICS
Chartgantt excel based gantt charts use Conditional formatting heavily. There are other approaches, and they all have advantages and limitations.
We believe that using Conditional formatting gives a greater degree of flexibility than using an adapted version of a traditional bar chart.
The key to making conditional formatting appear as bars is to use the same colour for the text and the background of the bar. This enables you to run calculations in the cells to determine their colour.
STRUCTURING YOUR WORKSHEETS – CALCULATION AND DISPLAY SHEETS
A key to the simplicity of the Chartgantt tool is the use of two separate worksheets, one for the hard number crunching (Calculation) and one for the user to work in (Display). In your Chartgantt Free Version, they are referred to as “Project Planning” and “Sheet 6”. Sheet 6 is hidden from view, you can unhide it by right clicking on the sheet toolbar and selecting unhide.
For all Chartgantt products the contents of this sheet are hidden from view. In this workbook we will be uncovering some of the secrets that live on this sheet.
SETTING UP THE DISPLAY SHEET
1. Download Chartgantt Free from www.chartgantt.com
2. Create your own new workbook.
3. Name the first sheet “Display”
4. Set up the columns and rows exactly as you see in the “Project Planning” sheet of ChartGantt Free (See below)
5. You are free to customize the colours or formats as you see fit, however, we like to keep the input cells as green if possible.
6. Format your columns in to, numbers, text or dates as required. Ie the start date column should show dates. Right click, select format cells, and select the format that is relevant for your column.
7. Turn off gridlines, and create a dotted grid for your bar chart area, use solid borders for your planning area
8. The Aug 2011 above is a row 4 cell that is formatted on an angle. Format all row 4 cells from the red section to as far right as you want to plan so that they look the same as the above example.
9. Using conditional formatting to create your bars and colours. The key trick behind the bars appearing clearly is to set both the background and text colours to be the same colour. i.e White text on a white background appears as white.
a. Select the first cell directly below your first “Red” cell. Select across the width of your plan and down the length (To row 200). Then click on all the cells below your gantt chart area (Headed by the red section), down 200 rows. Select Conditional formatting, then new rule. Create the following rules
i. To create your white background – if cell value =0, white background and white text.
ii. To create the Gantt Chart Bars – if cell value =1, then your Gantt Chart Bar format background and text colour.
iii. To create the weekend shading – if cell value <0, then your weekend colour background and text.
iv. To create your task status colours (Excel 2007 or Later only)
1. In Progress – if cell value = 2, then background and text colour amber and.
2. Completed – If cell value = 3, then background and text colour green.
10. Test your conditional formatting by entering a number entered above in into one of the conditionally formatted cells. It should now reflect your colour.
11. Your sheet is now set up and ready to take data. We will come back to it later.
SETTING UP THE CALCULATION SHEET
The calculation sheet is where many of the complex calculations are performed, and issues relating to dates are managed.
The starting point for the Calculation sheet is to set up some lists that we will use as named ranges and conversion tables.
1. Create the following lists in your calculation sheet.
2. Set up a named range for each list by selecting the list (excluding the numbers), right click and select “Name a range”, name each range according to the heading above. For example “Date List” for the date list range. Write these names down for later.
3. Creating the dates for the plan.
a. Start with the Daily View. The below table is what you should hopefully end up with at the end of this step.
i. Enter a “Start Date” back on your Display sheet to be used for calculating dates.
ii. Set a cell on your Calculation sheet equal to your start date cell.
iii. In the first cell to its right enter the formula =”Your Cell Reference eg A4” +1 Click on the small square in the corner of the box and drag across (or copy across) as many cells as you wish to plan.
iv. This is the beginning of your date work.
v. In a new cell below your row of dates, enter Weekday(“Cell above”) This will return the day of the week that is represented by the date. Copy these across.
vi. Convert these dates into your day references, by using a Vlookup against your “Day List”
vii. Convert your date into just the day of the month by using Day(“Your cell reference eg A4”)
viii. Convert your date into a month of the year by using Month((“Your cell reference eg A4”)
ix. Convert your date into the year by using the Year(“Your cell reference eg A4”)
x. Convert your date into Month Name by using a Vloolup from Viii above and referencing your date list.
xi. With the top 2 rows, we need it to only show when there is a change. Ie. Show only 2011 after the end of 2010, and show only Aug, after the end of Jul. This can be done by using an “if” statement to determine if that month or year is different to the one in the previous column. Here is an example of the formula used (=IF(L4<>0,YEAR(L5),0)) with row 4 being the row of dates.
b. Setting up the weekly view. The below table gives a guide as to what we are looking to create to enable a weekly view of the data.
i. A week is calculated simply by taking the start date and adding 7 for each column.
ii. Then use the day and month functions respectively to create WE Day and month rows.
iii. Use a Vlookup then to convert your month number to “Aug”
iv. Use the “Year” function to determine the year, then build an if statement to determine whether or not to display the year.
c. Setting up the Monthly View
i. The monthly view involves de-constructing your date and adding a month to it. Sample formula =DATE(YEAR(K5),1+MONTH(K5),0) Continue adding one month to your date for each column.ii. Calculate year changes as you have done for month changes in B above.iii. Convert your month to just the month number by using the month function. Sample formula =MONTH(K21)iv. Convert your month number to the month name using a Vlookup
Now your dates are ready for use. There is still one critical piece of the puzzle to resolve. You need to build a selection formula, that enables you to select between day, week or month views. We need to pop over to the Display sheet first so that we have the view selector working
BACK TO THE DISPLAY SHEET
Now we need to format our cells to use our named ranges.
Select the cell E2, which in Chartgantt Free refers to the first weekend day. Click on Data, Validation. Then select list from the drop down, then in the source field type = your named range. Instead of your named range enter the name you have to that range. For example “Days”.
Find all drop down’s cells in Chartgantt free and repeat the above process using data validation to create your drop down lists in the display sheet.
We will come back to this sheet later to complete the formula’s that will generate our bars.
BACK TO THE GANTT CHART CALCULATION SHEET
SETTING UP THE VIEW SELECTION DATA
The below table illustrates the information that is actually used in your display sheet.
The “Daily” below comes from the drop down selection on the Display sheet. Ie. ='Project Planning'!E4
Top refers to the top row of date information in your Display sheet (usually year or year and month)
1 Down refers to 1 row down from the top of your display sheet which is day Week Ending or Month.
2 Down refers to the lowest level of granularity of date information on the display sheet.
The date runner is simply so that we can see what information the system is actually using for its calculations.
Set your view selection equal to the view selection cell in your display sheet. Check that this is working if you change your drop down selection
Use an “if” statement to determine what data to display.
Here is the statement used for Calculating this cell =IF($G26="Daily",IF(K4<>0,CONCATENATE(K4," ",K3),0),IF($G26="Weekly",K15,IF($G26="Monthly",K20,"NA"))) What this formula is doing is determining which date information to use and displaying the date in the required format.
The formula for 1 down is similar =IF($G26="Daily",K7,IF($G26="Weekly",K14,IF($G26="Monthly",K19,"NA")))
The formula for 2 down is also similar
The formula for 2 down is similar also, but just pointing to the data you want displayed.
Test your formula’s and the following should happen. Selections on the drop down should change all the dates in your range.
After checking your formula’s and dates are working as they should, we can move on to the next stage of building our gantt chart
BACK TO THE DISPLAY SHEET TO FINISH OFF OUR GANTT CHART
We now need to build up our calculations. These will be used in every cell that could possibly display our gantt chart bars, and will need to be copied across and down to fill out our planning window.
This formula needs to work out if the cell in question is a weekend, it also needs to determine if it is between the start and finish dates of that task. Additionally for those using the status field, it needs to work out the status of the task.
The below is a sample formula that is used in the first planning cell in the worksheet.
=IF($E$4="Daily",IF('Dependent Calcs'!K$6='Dependent Calcs'!$B$2,-1,IF('Dependent Calcs'!K$6='Dependent Calcs'!$B$3,-1,IF($J8<'Dependent Calcs'!L$29,IF($L8>'Dependent Calcs'!J$29,IF($I8="Select",1,IF($I8="in progress",2,IF($I8="Completed",3,0))),0),0))),IF($J8<='Dependent Calcs'!K$29,IF($L8>'Dependent Calcs'!J$29,IF($I8="Select",1,IF($I8="in progress",2,IF($I8="Completed",3,0))),0),0))
You will need to create your own formula (you can use the above formula as a guide) to determine if the result is -1,0,1,2 or 3. These results will then invoke your conditional formatting, and your bars will be drawn for you based on input from your start date and end date information.
That is it, you are done! You now have an operational gantt chart based in excel that can be used for any type of project and at any time.
COPYRIGHT AND INTELLECTUAL PROPERTY
Mission HQ Pty. Ltd ATF The Innovate Trust is the licensee of the copyright in this material from the Author, Marcus Tarrant.
You are not permitted to re-transmit, distribute or commercialise the information or material without seeking prior written approval from Mission HQ.
Any completed gantt charts must not use the GanttChart logo, or make reference to Chartgantt in any way.
Any gantt charts developed as a result of using this manual may not be commercialized or sold to 3rd parties. They must be for private or company use only.
For written permission to use the information or material from this site, please contact Mission HQ. email@example.com
Mission HQ will in no way be held liable for any omissions, errors or inaccuracies in this document. The document is intended as a guide only and the resultant product will be reliant on the skill of the individual developing the gantt chart.
Free Gantt Chart Guide, Free Gantt Chart, Free Guide, Gantt Chart Guide, Free Chart, Free Gantt.