Spreadsheet: 861470d1709310096-gnatt-chart-bar-color-schedule_2_2024
Sheet: Help

Help
 | Version: Gantt Chart Template 3.0 for Office 365
 | If this page does not answer your questions, visit the following page: | Support
Intro
 | This Gantt Chart spreadsheet is designed to to help you create a simple project schedule. You
 | only need to know some basic spreadsheet operations, such as how to insert, delete, copy and
 | and paste rows and cells. For more advanced uses, such as defining task dependencies, you
 | will need to know how to enter formulas.
 | Be sure to read the Getting Started Tips below. It would also be a good idea to watch the demo
 | videos on Vertex42.com. The demo videos use the desktop version of Excel, but much of the
 | information is still applicable to users of Excel Online.
 | Watch [NAME] on Vertex42.com
 | Before sharing this spreadsheet, please read the license agreement in the TermsOfUse worksheet.
New in This Version for Online Excel
 | This version of the Gantt Chart Template was designed to work in Online Excel and Excel for iPad and iPhone. It will also work in desktop versions of Excel 2010 or Later. Below are some of the notable additions or changes in this version.
 | - It uses the WORKDAY.INTL and NETWORKDAYS.INTL functions which allow you define which days of the week are considered non-working days (explained in the Help below).
 | - You can highlight the End date red when the task is overdue (explained in the Help below).
 | - Instead of changing the color of the bars to indicate completion status, conditional formatting is used to display a progress bar within the % Done column.
 | - The number of columns used to display the gantt chart had to be reduced, so the [NAME] and Monthly views are less precise, meaning a week or month containing a 1-day task has a bar that is the same length as a week or month containing a 5-day task.
 | - You can define one Predecessor per task (instead of 3).
 | - The "Show Weekends" option was removed. In the Daily view, if you do not want to show weekends, you may hide the columns you do not want to show (though you would need to unhide them when using the [NAME] or Monthly views).
Getting Started Tips
 | - Input cells for defining the task dates and durations have a light green background. | Input Cell
 | - [ Bracketed Text ] is meant to be edited, like the project title and task descriptions.
 | - Some of the labels include cell comments to provide extra help information. | Label
 | - The Project Start Date determines the first week shown in the gantt chart.
 | - To adjust the range of dates shown in the gantt chart, change the Display Week.
 | - Choose the Daily, Weekly, or Monthly view using the drop-down box below the Start Date.
 | - To insert a new task, insert a new row, then copy/paste a row from the selection of template
 | rows at the bottom of the worksheet, depending on how you want to define the task.
 | - Edit the Holidays worksheet to define the dates that should be considered non-working days.
 | - If you see "#####" in a cell, widen the column to display the cell contents.
 | - Backup your file regularly to avoid losing data! Excel files get corrupted occasionally.
Defining the Weekend or NON-WORKING Days
 |  | weekend
 | Choose an option or enter a weekend string → | 1
 | The WORKDAYS.INTL() and NETWORKDAYS.INTL() functions allow you to specify | Option | Non-Work Days
 | which day(s) of the week should be used as the weekend or non-working days. | 1 | Saturday, Sunday
 |  | 2 | Sunday, Monday
 | To use these functions, choose an option from the drop-down box on the right. | 3 | Monday, Tuesday
 |  | 4 | Tuesday, Wednesday
 | If one of the options to the right does not work for you, you can enter a string of | 5 | Wednesday, Thursday
 | 7 characters, using a "1" to represent a non-workday and a "0" to represent | 6 | Thursday, Friday
 | a work day, beginning with Monday. | 7 | Friday, Saturday
 |  | 11 | Sunday Only
 | For example, the string 0000011 would result in a Saturday-Sunday weekend. | 12 | Monday Only
 |  | 13 | Tuesday Only
 |  | 14 | Wednesday Only
 |  | 15 | Thursday Only
 |  | 16 | Friday Only
 |  | 17 | Saturday Only
Using the Template Rows and Choosing a WBS Level
 | Inserting New Tasks.
 | 1. Insert a new blank row where you want the new task to be.
 | 2. Copy the entire row you want to use from the set of template rows.
 | 3. Paste the row you copied on top of the new row you just inserted.
 | 4. Copy and paste the WBS cell separately, based on the level (1, 1.2, 1.2.3, 1.2.3.4)
 | Important: When inserting new rows, you must copy and paste an entire row, because the cells of the [NAME] chart area are formulas.
 | Changing the WBS Level in the WBS Column
 | - The WBS numbering uses a different formula for each level, but the formula does not reference any other cell in the row. So, you can copy and paste just the WBS cell that you want to use.
 | - If you leave a blank cell above a WBS number, the numbering will reset to 1.x.x. The formulas are meant for convenience, but you can manually enter them if you need to.
 | - You can indent the task description for sub-tasks by entering leading spaces or using the Indent feature in Excel.
 | Category Tasks
 | - You can use tasks that are just labels, but it may be useful for a category task to display the minimum Start date and maximum End date of its sub tasks. This can be done using =MIN(range_of_startdates) and =MAX(range_of_enddates).
Creating Task Dependencies
 | - You can enter the [NAME] date manually, or define task dependecies using a formula. Below are the most common options for defining the [NAME] date:
A. | Enter the date manually (e.g. 1/3/2015)
B. | Reference the Project Start Date (e.g. =$E$4 )
C. | Set the Start date to the next Work Day after another task's End date.
 | - Use the formula =WORKDAY(enddate,1) where enddate is the reference to the End date of a predecessor task.
 | - For multiple predecessors, the formula would be =MAX(WORKDAY(enddate1,1),WORKDAY(enddate2,1))
D. | Set the Start date to the next Calendar Day after another task's End date.
 | - This formula is very simple: =enddate+1
 | - For multiple predecessors, the formula would be =MAX(enddate1,enddate2,enddate3 )+1
E. | Set the Start date to a number of days before or after another date.
 | - This formula is just like the one in C or D, except that in place of the "1" you enter the number of days, such as =WORKDAY(enddate,5) or =WORKDAY(startdate,-5)
 | Note: To include holidays as non-working days, use the syntax =WORKDAY(enddate,1,holidays). When using a weekend other than Saturday/Sunday, use =WORKDAY.INTL(enddate,1,weekend,holidays)
E. | Use the Predecessor column to start the day after another task
 | - When you use the template rows with the Predecessor as an input (light green highlight), the Start date is calculated to be the day after the referenced task. There are two ways to enter the predecessor.
 | 1. Reference a Predecessor using a formula.
 | Instead of typing the WBS number into the Predecessor column, use a formula such as =A15 to reference the WBS number for a specific task. This is the preferred method because if you insert new rows, the reference will remain linked to the intended task.
 | 2. Enter the Predecessor manually as text.
 | You can enter the Predecessor manually, but you must make sure that you enter it as text,
 | or you will end up with a bunch of #NA errors. To enter 1.3 as text, just add an apostrophe
 | at the beginning like this: '1.3.
 | Note: This is not the best method, because if you insert a new task, you will have to update all of the Predecessors again, or at least verify that they are still correct.
Changing the End Date Font to Red when Behind Schedule
 |  | Feature on/off: | off
 | Conditional formatting can be used to do some very useful things. One feature built into this spreadsheet is the option to change the font color of the End date to red when it is approaching the current date and also make it bold when the task is past due.
 | Change the urgency days to the right to determine when the End date will change to red. | Urgency days: | 7
Changing the Color of the Bars in the Gantt Chart
 | Color Column:
 | The cells that make up the chart area use conditional formatting to control the color of the bars. The default color is blue. You can make the bars different colors by entering one of the following letters in the color column:
 | Colors: b=blue, k=black, g=green, p=purple, o=orange, r=red, x=gray, y=yellow
 | You can also enter a number 1-6 to use an accent color from the theme palette.
Advanced Uses of the Color Column
 | Using a Formula to Change the Color Based on Percent Complete
 | Using a reference to the %Done column, you can change the color of the bar to gray using the following formula in the color column. Note that "x" and "b" are the colors for gray and blue.
 | =IF(percent_done>=100%,"x","b")
 | Using a Formula to Show Urgency |  | Urgency (Days)
 | You can enter a formula in the [NAME] column to change the color to orange based on a | red | 2
 | comparison of the End date and Today's date. For example, the following formula would give | orange | 14
 | you a 10-day warning, where end_date is a reference to the End date cell. | yellow | 28
 | =IF(end_date<TODAY()+10,"o","")
 | The example below makes the 10-day warning orange and a 2-day warning red.
 | =IF(end_date<TODAY()+2,"r",IF(end_date<TODAY()+10,"o",""))
 | The fancy formula below makes use of the table of urgency values to the right. Note that "urgency_days" is a named range referring to the table on the right.
 | =IF(end_date<TODAY()+INDEX(urgency_days,1),"r", IF(end_date<TODAY()+  INDEX(urgency_days,2),"o",IF(end_date<TODAY()+ INDEX(urgency_days,3),"y","")))
 | Color-Coding Based on Lead | Names | Color
 | One popular reason for color-coding is to help identify ownership of a task. To do this, | [NAME] | k
 | you could create your own nested IF formula: =IF(ref="Bob","o",If(ref="Sally","p","")) | [NAME] | x
 | where "ref" is the reference to the cell in the Lead column. | [NAME] | b
 |  | [NAME] | g
 | If you want to use the table listed to the right of this help section, you can edit the following |  | p
 | formula in the [NAME] column. Not that the Names must match exactly. |  | y
 | =IFERROR( INDEX( lead_color, MATCH( ref, lead_names, 0) ), "") |  | o
 |  |  | r
Changing Dates to dd/mm/yyyy Format |  | Date Format
 | If you would like to display dates in the gantt chart in UK format (dd/mm/yyyy) choose the | mdy
 | appropriate option to the right.
FAQs
Q: | How do I calculate the %Complete for an entire category of tasks?
 | The %Complete for a category task can be calculated from its sub tasks using the formula below, where "workdays" is a reference to the range of work day values and "complete" is a reference to the %complete for each of the subtasks.
 | =SUMPRODUCT(workdays,complete)/SUM(workdays)
 | Example: =SUMPRODUCT(H9:H15,J9:J15)/SUM(H9:H15)
 | Let's say you have 3 sub tasks that are 10 days, 12 days, and 14 days long, respectively. If the first subtask is 50% complete and the others are 25% complete, you could calculate the overall percent complete for the group as: =(10*50%+12*25%+14*25%)/(10+12+14).
Q: | How do I change the Print Settings? (Excel 2010, 2013)
 | Select the entire range of cells you want to print and go to File > Print Area > Set Print Area.
 | Then go to File > Page Setup or File > Print Preview and adjust the Scaling, Marings, and
 | Page Orientation as desired.
Q: | The Start date, End date, or %Complete for a Level 1 task is wrong. How do I fix it?
 | When using =MIN(), =[NAME](), and =SUMPRODUCT(), it is easy for the references to get messed up if you move rows around or insert new rows. You should verify and fix these formulas if they are not referencing the correct ranges.
 | Example: The image below shows that the MIN() function is not referencing all of the sub tasks.
Q: | I've messed up the chart area somehow. How do I fix it?
 | Find a row that works, then copy the cells that make up the gantt chart area from that row into the row that is messed up.
© 2015 Vertex42 LLC