Soumik Dutta
Gantt Chart is a widely used chart in the managemental branch of any organization. It displays the activities of that institution against the time scale. In this article, we are going to demonstrate two easy examples of creating an Excel Gantt chart with conditional formatting. If you are also curious, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
What Is Gantt Chart?
Gantt chart is a popular chart in the managemental section of any organization. This type of chart is widely used for project management. It will show us the activities of that industry against the time scale. Every activity is usually represented through a bar. In addition, the position and the length of any bar show the start date, duration, and end date of the following activity.
2 Suitable Examples to Create Excel Gantt Chart with Conditional Formatting
To demonstrate the examples, we consider a dataset of five projects. The name of the projects is in the range of cells B5:B9. In addition, the starting and finishing dates of the corresponding project are in the range of cells C5:C9 and D5:D9, respectively.
1. Creating Simple Gantt Chart with Conditional Formatting
In this example, we are going to show the procedure to create a simple Excel Gantt chart with conditional formatting. The steps of this procedure are given as follows:
📌 Steps:
- First of all, write down all the dates 1 July-15 July in the range of cells E4:S4.
- Now, select the range of cells E5:S9.
- In the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
- Then, select the New Rule option.
- As a result, a small dialog box called New Formatting Rule will appear.
- After that, choose the Use a formula to determine which cells to format option.
- Write down the following formula into the empty box using the AND function.
=AND(E$4>=$C5,E$4<=$D5)
- Now, click on the Format option.
- As a result, another dialog box called Format Cells will appear.
- Then, in the Fill tab, choose your desired background color. For our chart, we choose Orange, Accent 2 color.
- Click OK to close the Format Cells dialog box.
- Again, click OK to close the New Formatting Rule dialog box.
- You will see that the chart will be ready, and the cells will show our selected color.
Thus, we can say that our formula worked perfectly, and we were able to create a simple Excel Gantt chart with conditional formatting.
Read More: How to Create Excel Gantt Chart with Multiple Start and End Dates
2. Gantt Chart with Conditional Formatting Based on Categories
In the following example, we will show how to create an Excel Gantt chart with conditional formatting based on categories. The steps of this process are given below:
📌 Steps:
- In this example, first, we have to estimate the duration of the projects.
- For that, write down the following formula into cell E5 using the DAYS function.
=DAYS(D5,C5)+1
- Press Enter.
- Now, double-click on the Fill Handle icon to copy the formula up to cell E9.
- After that, we wrote the Status of our projects in the range of cells F5:F9. We have chosen three different statuses for our projects Completed, In Progress, and Pending. These are the categories of our chart.
- Then, to get the first 15 days date of our desired month, write down the following formula into cell G4. In this case, the SEQUENCE and DATE functions will help us.
=SEQUENCE(1,15,DATE(2022,7,1),1)
🔍 Breakdown of the Formula
We are breakdown our formula for cell G4.
👉 DATE(2022,7,1): This function returns the first date of the month of July, 7/1/2022.
👉 SEQUENCE(1,15,DATE(2022,7,1),1): This formula get the first starting point from the DATE function. Then, the function returns the other 15 dates in a row and 15 columns with unit intervals.
- Again, press Enter.
- You will get all the dates from 1 July-15 July in the range of cells G4:U4.
- Now, we have to apply the general conditional formatting like the previous example.
- For that, select the range of cells G5:U9.
- After that, in the Home tab, click on the drop-down arrow of the Conditional Formatting option from the Styles group.
- Afterward, choose the New Rule option.
- As a result, a small dialog box called New Formatting Rule will appear.
- Then, choose the Use a formula to determine which cells to format option.
- Write down the following formula into the empty box using the AND function.
=AND(G$4>=$C5,G$4<=$D5)
- At last, click on the Format option.
- As a result, another dialog box called Format Cells will appear.
- Now, in the Fill tab, choose your desired background color. For our chart, we choose Orange, Accent 2 color.
- Click OK to close the Format Cells dialog box.
- Again, click OK to close the New Formatting Rule dialog box.
- You will see that the chart is showing our chosen color.
- Now, we are going to apply the cell formatting based on our category.
- Select the range of cells G5:U9 and follow the same procedure to open the New Formatting Rule dialog box.
- Then, write down the following formula.
=AND(G$4>=$C5,G$4<=$D5,$F5="Completed")
- Choose a different color for this category. We choose Green, Accent 6, Darker 50% color.
- You will see that the projects whose status is “Completed” show our selected color.
- Similarly, write down the following formula for the In Progress criteria.
=AND(G$4>=$C5,G$4<=$D5,$F5="In Progress")
- Choose a color for this category according to your desire. We choose Green, Accent 6, Lighter 40% color.
- You will get the In Progress projects showing our selected color.
- Finally, create another formatting rule for the Pending category.
- For this category, write down the following formula in the New Formatting Rule dialog box.
=AND(G$4>=$C5,G$4<=$D5,$F5="Pending")
- Choose another distinguishable color from the Fill tab of the Format Cells dialog box. We choose Red for this category.
- Close all the dialog boxes and you will get the complete Gantt chart.
At last, we can say that all of our formulas worked successfully, and we were able to create an Excel Gantt chart with conditional formatting based on categories.
Read More: How to Create Gantt Chart for Multiple Projects in Excel (with Easy Steps)
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to create an Excel Gantt chart with conditional formatting. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
