A lot of organizations use online project management systems, like Asana. But, sometimes you want a quick and dirty, light weight Gantt chart to track small projects.
I keep a blank Gantt chart in my client folders as a template ready to open and fill in. It allows me to do quick estimates or track tasks in a consulting project.
This is the first, and simplest, of three Excel Gantt charts I’ve put for you in Critical to Success. Download and play with this one before you move on to the more advanced ones. Or just use this as a “fill in the blank” model without customizing it.
Gantt charts help you track the progress of tasks in a project. This and the following tutorials will show you how to create a Gantt chart in Excel and then enhance it with robust features. The tutorial starts with a simple Gantt chart using conditional formatting and a few functions and evolves into a project and task management system that will handle most small business projects.
The following Excel tutorial will take you through building a simple Gantt chart in Excel that is a foundation for a more advanced Gantt chart. Even the simple template gives you a tool that is useful for many business projects. The simple Gantt chart includes start date, in-progress workdays, and end dates. It also scrolls left or right so you can see dates farther away.
The next article will show you how to add many useful features to this simple Gantt chart. The next article also includes a custom calendar so your Excel Gantt chart will account for international holidays and many religious holidays over the next few years.
Signup for the Critical to Success newsletter and get more Excel and professional productivity tips.
Why use a Gantt chart in Excel when there are many project management systems, online and offline? One factor is the cost, another is flexibility, and a third is workload. Many years ago I managed a book/software project that had a team of 12 authors and developers in sites from the northeast coast of the USA to the west coast of Australia. Each week past the deadline was an estimated $250,000 in lost revenue.
I started using Microsoft Project and soon found that its complexity required a part-time administrator. Instead, I quickly wrote my first Gantt chart in Excel, added a few custom features and continued saving me money and time. The result was just enough, just in time, and the book/software project beat the due date by a week.
If your projects are like the long lists of loosely related tasks that most managers, marketers, or small teams face, then a Gantt chart in Excel could work well for you. Gantt Charts in Excel work best,
If you need to share documents and resources, schedule meetings, chat with team members, and the other things to keep your project running, check out Basecamp 3.
Critical to Success has no affiliation with Basecamp, but many professional project managers I know recommended it.
Beware, don’t create Gantt charts in Excel using horizontal bar charts. This approach works if you need a quick slide for a PowerPoint, but don’t try to use it as a tool for managing projects. Graphical charts are very limiting. You won’t be able to display dynamic details the way you can when using conditional formatting and functions.
Down the left columns of the Gantt chart are lists of tasks, shown under the headings WBS, Start date, Days work, and End date. Enter the following information to build the structure for your Gantt chart. It will be very easy to expand the task list and date breadth after it is built.
TIP: The rows 8:13 are left blank for features that will be added in this and following tutorials.
For this to work correctly and for your worksheet to look like the sample figures, enter dates and days as shown in the table below. Once you have the worksheet built, or download the template, you can enter tasks and dates that work for you.
|B16:E16||Labels for WBS, Start, Days, End|
|B17:B26||Task names, e.g., Task 1, Task 2, etc.|
|C17:C26||Task start dates as shown in the figure, from Dec 11, 2015 through Jan 2, 2016.|
|D17:D26||Days on task between 1 and 12 days as shown.|
Create the horizontal calendar so its first date is the Schedule Start date and then each cell to the right increases by one day.
|F14, F15, F16||=E7||The Schedule Start date|
|G16||=F16+1||Increments date by one day|
|G14||=G16||References day in G16|
|G15||=G16||References day in G16|
Although all the cells in F14:W16 contain dates they display differently due to custom formats. Create custom formats by selecting the range, right-click, choose Format Cells, in the Number tab, select Custom and in Type enter the custom formats as follows for each range.
|F14:W14||mmm||Three-letter month, e.g., Dec, Jan|
|F15:W15||d||Date without leading zero, e.g., 9|
|F16:W16||ddd||Three-letter day, e.g., Mon, Tue|
Learning the syntax for custom formats is extremely useful for reports. See examples of custom formats you can use or modify by scrolling through the predefined custom formats in the Format Cells dialog box.
Now create the End task dates by adding the number of work days to the Start date and subtracting one. In cell E17 enter the formula,
Copy cell E17 down through E26. Copy the formulas and formats in E17:E26 across to column W.
This End date formula assumes people will work on weekends and holidays. What a terrible idea. Not only do you feel bad by working without a break, your productivity actually decreases when you work long stretches without a break. The solution is to use the WORKDAY.INTL function later in the tutorial to adjust the End date for weekends and holidays.
You now have a framework for your daily Gantt chart. Within cells F17:W26 you need to apply Excel’s conditional formatting. Conditional formatting will create horizontal colored bars to show you each tasks workdays. Conditional formatting examines each cell in the Gantt chart. If the date in row 16 above that cell is between the Start and End dates then the cell is filled with a solid color. This creates horizontal bars across your schedule showing when the task is scheduled.
Conditional formatting uses formulas to calculate the type of format applied to a cell. To learn more about conditional formatting check out these two sites,
If you aren’t familiar with the usefulness of conditional formatting check Excel Easy,
Your conditional formats will use formulas to decide whether to apply a format to a cell. Usually this is a difficult and error prone process. Let me show you a trick that makes it easy.
Formula-based conditional formats are entered into a dialog box. That makes it impossible to see if the formula produces the correct test result. Is it correctly evaluating whether a cell should be formatted? You can’t tell if you immediately enter a formula in the dialog box. Instead, use this trick.
A conditional format on a cell is applied when the formula that examines that cell returns a TRUE. Cells where the formula returns FALSE do not have the conditional format applied. The trick then is to first test the formula in the worksheet where you can see if it correctly returns TRUE or FALSE.
The condition (formula) we want to test in cell F17 is,
This formula checks to see if the schedule date in F17 is greater than or equal to the Start date and less than or equal to the End date. If you entered the same values as in the example figures it will return a FALSE because the date is not on or between the Start and End dates.
This formula uses $ to create an absolute reference so the formula can be copied down F17:F26 and then copied across to fill F17:W26. The result will look like the following figure. Notice the TRUE values appear where the schedule date is on or between the Start and End dates. (Remember, a later formula will adjust for weekends and holidays.)
Once you test this formula and see that it returns TRUE and FALSE on project dates you can use the formula to create conditional formatting. To apply the conditional formatting,
1. Select cell F17. In the formula bar select the entire formula and press Ctrl+C to copy it. Copying and pasting the formula can keep you from going crazy trying to fix an errant formula.
2. Select the range F17:W26. This is where you want the conditional formatting applied. The formula you created was for cell F17, so make sure F17 is the active cell in the selection.
3. In the Home tab, in the Styles Group, click Conditional Formatting and select New Rule.
4. In the New Formatting Rule dialog box, in the Select a Rule Type list, select “Use a formula to determine which cells to format”.
5. Click in the Edit the Rule Description box and press Ctrl+V to paste the formula. The dialog box should look like this,
6. Click the Format button. In the format dialog that appears select the format you want applied to cells that test TRUE. Notice you have a wide range of formatting options. To follow the example, select a blue fill.
7. Click Ok twice to return to the worksheet.
Your worksheet should now show a blue fill in each cell that tested TRUE.
Check to be sure that blue fills only cells that are TRUE and are on or between the Start to End dates. The formulas in the cells are not used by the conditional formatting. (You entered that formula in the dialog box.) Once your worksheet looks correct, delete the TRUEs and FALSEs in the cells. You should be left with blue filled cells that show the ranges where work is done.
In a following tutorial I will show you how to add many more conditional formats such as,
Here’s a test for you until the next tutorial. Create a conditional format that outlines or highlights cells under the current date. This will show you where the current date is in the schedule. Use the current date typed in cell E6 so it matches the template. In the next tutorial I will show you one way to solve this.
Here is a tip you might need. When multiple conditional formats are applied to a cell (or range) the order in which the conditional rules are checked is the order in which formats are applied. So the order of your conditional formulas and formats can affect appearance. The Manage Rules dialog box found under the Conditional Format menu has arrows that enable you to move the conditional formulas up or down.
Now for something cool! This Gantt chart is only 18 cells wide. A great improvement would be to make this scroll left or right. In fact, you could scroll through as wide a date range as you need.
To create a scrolling Gantt chart in Excel you need to add a scroll bar to your worksheet. The scroll bar is located on the Developer tab that doesn’t appear in Excel’s default ribbon. Don’t worry! This is not programming. The Developer tab just has a tool you will use to draw worksheet controls like drop-down lists and scroll bars. No programming is needed.
If you haven’t added the Developer menu previously to your toolbar (this website uses it a lot to create dynamic scrolling charts and databases), see how to add the Developer tab in this tutorial that opens in a new tab,
To create the scroll bar that scrolls your Gantt chart,
1. In cell D8 enter and bold the label “Scroll Increment”. This labels cell E8 as the cell containing the amount of scroll.
2. On the Developer tab, in the Controls group, select Insert to display the controls you can draw on the worksheet.
3. In the Form Controls found in the upper section, click the scroll bar tool shown outlined in red in the figure. Do not use the ActiveX controls.
4. Hold the Alt key down as you drag from the top left corner of F13 to the bottom right corner of W13. This draws a scroll bar across row 13. Holding down the Alt key forces the scroll bar to snap to the cell corners.
To define how the scroll bar works set its controls,
1. Right-click the scroll bar and select Format Control.
2. In the Format Control dialog box set these properties,
|Current Value||0||Current scroll bar position|
|Minimum Value||0||Value of button at far left on scroll bar|
|Maximum Value||90||Value of button at far right on scroll bar. This enables about three months of scrolling.|
|Incremental Change||1||Value of change for each click on the scroll bar arrow|
|Page Change||10||Value of change for each click in the grey bar of the scroll bar|
|Cell Link||$E$8||This is where the scroll value is stored|
3. Click Ok then click outside the scroll bar to deselect it.
Test your scroll bar by clicking the arrows on either end, by clicking in the grey bar in the middle, or by dragging the button on the scroll bar. You should see the value in E8 change. If you click on E8 you will see there is no formula. The scroll bar just pokes that value into the cell.
The Gantt chart isn’t changing when you move the scroll bar button. That’s because you need to add the Scroll Increment in cell E8 to the starting schedule date in cell F16.
Select F16 and change the formula to,
This formula adds the scroll bar increment to the Schedule Start date. That increases the first date on the schedule in row 16. The dates to the right are all looking to their left and adding one. As the date in F16 increases they all increase.
Now test the scroll bar and watch the magic as your Excel Gantt chart scrolls left or right. You set 90 as the maximum limit so you can only scroll that far right.
Change some of the Start dates or Days for a task and see how the scroll works. First time I built this I thought it was so cool! It is still kind of neat.
There’s one more change we need to make for this to become a usable Gantt chart. We need to adjust the Gantt chart for weekends and holidays. If you are using Excel 2010 or more current you will use the amazing and awesome WORKDAY.INTL function. If you are using Excel 2007 you will use the WORKDAY function. WORKDAY is very helpful and almost as awesome.
Use WORKDAY.INTL to account for weekends and holidays when calculating the end date of a project. As if that weren’t enough, it gives you the power to adjust for custom work week schedules. For example, someone might work a four day week or they work weekends and take Monday and Tuesday off. WORKDAY does the same as WORKDAY.INTL, but it doesn’t give you the ability to create custom work weeks.
You only need to change one formula to make this magic work. At this point the formula in E17 is
It assumes that every day is a work week. (I know it does seem that way sometimes.) You need to change this formula to use WORKDAY.INTL or WORKDAY and then copy it down the End column.
The syntax for WORKDAY.INTL is,
WORKDAY.INTL(start_date, days, [weekend], [holidays])
start_date and days are obvious. They are in cells C17 and D17.[weekend] is a number or code that specifies which days of the week are weekend. It is optional. Leave it blank for this tutorial and it will assume weekends are Saturday and Sunday. In the next tutorial I’ll show you two ways you can write these custom weekend codes. [holidays] is a range that contains dates that are holidays. It is up to you to build the holiday schedule. Since every country, religion, and government have different holidays Microsoft has left this for you to enter the dates in a range. Check below because I have saved you a lot of work creating holiday schedules.
WORKDAY, in Excel 2007, is very similar to WORKDAY.INTL. It does not have the [weekend] parameter. It’s syntax is,
WORKDAY.INTL(start_date, days, [holidays])
To match the dates in this tutorial, create a short holiday list on the same worksheet in the range B42:E50 like this,
If you have Excel 2010 or more recent, enter the WORKDAY.INTL function in cell E17. If you have Excel 2007 use the WORKDAY function. In cell E17 enter,
Or for Excel 2007 enter
Copy cell E17 down through E26.
Notice two things. The holiday range does not include all the explanatory text I typed. Just use the date range without a heading. Also, the WORKDAY function does not have the parameter for custom weekends so it is missing the extra comma after $D$43.
The second thing to notice is that the blue bars now extend far enough to accommodate for weekends and holidays.
I’m running out of time to get this tutorial to you this week. But, I want to let you know that next week’s article will include a table of custom weekend schedules you can use with WORKDAY.INTL.
Next week’s article will also include a table of international and religious holidays for the next couple of years.
Inserting or deleting new tasks is quick in this Gantt chart.
To insert a new row and copy the formulas and conditional formatting,
1. Click the row header of the row where you want to insert a new task. This selects the entire row.
2. Press Shift+Ctrl+ + (plus sign). This inserts a new blank row across the entire worksheet and moves lower rows down.
3. With the empty blank row still selected, press Ctrl+D. This copies the formulas and conditional formatting from the row above into the new blank row.
4. Change the task label, Start date, and Days of work.
To delete a row,
1. Click the row header of the row you want to delete. This selects the entire row. Beware, all information in this row will be deleted.
2. Press Ctrl+ – (minus sign).
At this point you should have a Gantt chart in Excel that is a useful addition to your professional toolkit. Use it to manage small projects such as marketing campaigns, event scheduling, editorial calendars, small construction projects, home remodeling, and more.
Don’t worry about extending or enhancing the Gantt chart to fit your needs. The information you need to preserve is the tasks, start dates, and task days. It’s like a small database that you will be able to copy into future enhancements in following tutorials.
In the next article I will guide you through some additional enhancements such as,