Excel Gantt Chart with Conditional Formatting
- Version
- 5'560 Downloads
- 240 KB File Size
- January 3, 2022 Updated
- 0 Number of comments
- Rating
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.
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.
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.
You have room for ten projects or tasks in the template, but you can easily add more if necessary. To do so:
- Select a project row by clicking the number on the left side of the Excel sheet.
- Right-click the row and select Copy.
- 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.
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.
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.
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.
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.
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.
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.
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.
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!
