Excel Gantt Chart with Conditional Formatting

  • Version
  • 5'737 Downloads
  • 240 KB File Size
  • January 3, 2022 Updated
  • 0 Number of comments
  • Rating
table of content

One terrific, undervalued feature of a spreadsheet application like Microsoft Excel is its conditional formatting. This allows you to format cells automatically based on the data. For instance, you can have the color a cell change if it matches certain criteria.

Gantt charts allow project managers to track a project’s schedule, often with important dependencies - such as resources and personnel - in mind. Our Excel Gantt Chart with Conditional Formatting has taken advantage of some of the best tools in Excel to create a powerful project management tool that will help you keep your next project on time and on budget.

Project Progress and Critical Path

With the conditional formatting built into this Gantt chart template, you can easily see if your project timeline is on schedule and view its critical path clearly.

Boxes will color in automatically to show you if you are veering from your planned dates. Plus, these colors display on the chart view for a quick glance to help you identify the critical path.

All you need to do is download the Excel file and enter your information. But to clarify some of the nitty gritty details, we’ve created this instructional article to help you get the most out of our Gantt chart. Planning and managing your next project will be simpler than ever, and using this tool will help you be a better manager.

Creating Your Gantt Chart

This template comes with one workbook tab that has a big area where you can see both the work breakdown section on the left and the progress bar chart on the right. These two sections work together to give you a rich picture of your project’s timeline as it progresses.

To build a Gantt chart, begin by entering the basic project details and then move on to the work breakdown structure section.

Project details

This is where you can paste some basic Gantt chart details about your project.

Project Information

The basics for this Gantt chart in Excel include the Company Name and the Start of Planning date. You’ll see these fields in the upper left corner of the template. Just click each cell to enter your own text.

Excel Gantt Chart Conditional Formatting Project Information

Tip: You can insert your company logo in the cell labeled Gantt Planner for a personalized chart appearance.

Start of Planning

For the Start of Planning field, enter the start date for your project. You can use whichever date format is most comfortable for you and the template with reformat it as it appears in our example here. So, you can enter June 5, 2019 or 6/5/19 and the template will format it as 05-Jun-19.

After you enter this date, it will automatically update the dates in the Gantt chart view. The chart week date will always start on a Monday. So, if you enter a Start of Planning date of Wednesday, June 5, the Gantt chart will start on Monday, June 3.

Excel Gantt Chart Conditional Formatting Start Planning

Work Breakdown Structure

When you finish entering the basic project details, you’ll move to the work breakdown section. This is where you will enter your project tasks, resources, planned dates, and actual dates.

Projects (Tasks)

Enter the name of your first project or task in the row and cell labeled Project 1 and then continue with additional names in the subsequent project rows and cells.

Excel Gantt Chart Conditional Formatting Tasks Breakdown

You have room for ten projects or tasks in the template, but you can easily add more if necessary. To do so:

  1. Select a project row by clicking the number on the left side of the Excel sheet.
  2. Right-click the row and select Copy.
  3. With the row still selected, right-click it and select Insert Copied Cells. This will place your new row directly above the one you copied.

Excel Gantt Chart Conditional Formatting Insert Cells

Responsible People

As you enter the project or task names, you can also add the names of the resources directly to the right in the Responsible People column. This gives you a fast and easy way to see which team members are responsible for completing which tasks.

Excel Gantt Chart Conditional Formatting Responsible People

Start and End Dates by Plan

This Gantt chart template lets you plan your project more effectively by providing planned start and end dates. These planned dates will be compared against the actual dates, that you’ll enter later, to show you if your project is on target.

You can enter these in the Start Date by Plan and End Date by Plan columns corresponding to each project or task.

Excel Gantt Chart Conditional Formatting Plan Dates

As with the Start of planning date, you can enter whichever date format works best for you and the template will reformat it automatically.

Start and End Dates Actual

When you obtain the actual start and end dates for the tasks, you can enter those in the Start Date Actual and End Date Actual columns.

Excel Gantt Chart Conditional Formatting Actual Dates

Once you enter these dates, the template uses conditional formatting in the work breakdown section. It also populates and formats the Gantt chart view. This lets you see at a quick glance if your tasks, as well as the overall project, are on schedule.

Conditional Formatting in the Work Breakdown Section

The conditional formatting you see in the work breakdown section provides an easy way to see if you are on target for your planned start and end dates.

If you have a task planned to start on June 1, but the actual start date isn’t until Jun 5, you’ll see a dark yellow box indicating that delay. The same is true for the end dates and if you’re ahead of schedule. So, if a task is planned to end on Jun 20, but actually ends on Jun 10, this results in a dark yellow box too.

Excel Gantt Chart Conditional Formatting Breakdown Section

These dark yellow boxes let you know that the workflow does not meet the planned dates that you set, regardless of whether you are ahead or behind.

Work Days Number

Do not enter any data in the Work Days Number column. The fields in this column will calculate automatically as you complete the Start Date Actual and End Date Actual fields. Keep in mind that the number uses only weekdays as workdays.

Excel Gantt Chart Conditional Formatting Work Days Number

The Chart View

The Gantt chart view on the right is where you can see your project schedule and critical path. The template uses the planned and actual start and end dates from the work breakdown section to give you a clear view of your project’s progress. Just scroll to the right in the Gantt chart view to see the bar chart for the upcoming weeks.

The template is designed for 16 weeks. To help you plan your project and understand the chart clearly, each week begins on Monday, contains five workdays, and alternates lighter and darker grey for easier viewing.

Below the dates, you will see colored boxes for your tasks. These boxes represent the number of workdays it takes for each to be completed.

For instance, if you have a task’s actual start date as Jun 10 and actual end date as Jun 23, that equals 10 workdays. So, you will see a bar chart made of 10 boxes in the chart view for that task.

Excel Gantt Chart Conditional Formatting Chart View

You will also notice a key showing that a Normal Workflow will display in brown and a Workflow is Not Up to Plan will display in dark yellow. This is where the planned and actual start dates come into play with conditional formatting once more.

For tasks that meet the planned dates with the actual dates, the boxes will display in brown. And for those where the actual dates do not match the planned dates, the boxes will display in dark yellow.

Excel Gantt Chart Conditional Formatting Color Condition

This type of conditional formatting in Excel is extremely convenient for project planning. It allows you to view the critical path, your project’s progress, and whether or not each task is on schedule.

The Usefulness of Gantt Charts

As you can see from this Excel tutorial for our Gantt Chart with Conditional Formatting, the template is easy to use for planning, managing, and keeping your project on track throughout its lifespan. And the automatic formatting just adds to its usefulness for any type of project.

If you’re considering a different chart type to manage your project, keep these benefits of Gantt charts in mind. They can help you:

  • Break down your project into manageable pieces,
  • Evenly distribute your project resources,
  • Visualize your workload for better planning,
  • Track your tasks, dependencies, and overall project progress, and
  • Prioritize activities by viewing the project timeline and critical path.

As you can see, Gantt charts offer some wonderful advantages and with the conditional formatting mixed in, this template is a terrific tool for any project manager.

Go Forth and Manage Your Project

Now that you have your Excel Gantt chart template, as well everything you need to know in order to use it, the rest is up to you. We wish you the best of luck with your next project!

How useful was this template?

Click on a star to rate it!

Average rating 4.4 / 5. Vote count: 7

No votes so far! Be the first to rate this template.

We are sorry 🙁

Help us improve!

How we can improve this template?

Employee Attendance Tracker

11.2 Mb / 63161 downloads
Travel Request Form

24 KB / 5541 downloads
Employee Database Excel Template

30.2 Mb / 36688 downloads
Employee Schedule Template

42 KB / 3972 downloads
Employee Vacation Planner

2.1 Mb / 26205 downloads
General Ledger Template

51 KB / 16666 downloads
Mileage Log Template

34 KB / 19488 downloads
Free Gantt Chart

4.0 Mb / 11074 downloads
Account Payable

54 KB / 9684 downloads
View Comments
There are currently no comments.