Open In App

How to Make a Dynamic Gantt Chart in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

The Gantt chart is named after Henry Gantt, an American mechanical engineer and management consultant who devised it in the 1910s. In Excel, a Gantt diagram displays projects or tasks as cascading horizontal bar charts. A Gantt chart depicts the project’s breakdown structure by displaying start and completion dates as well as other linkages between project activities, allowing you to track tasks against their allocated time or preset milestones. A Gantt chart allows you to know at a glance which tasks are currently the greatest priority, as well as the expected completion date of the article.

Steps to Create an Excel Gantt Chart

Step 1: Create a table with columns for data such as articles, start date, and end date, as seen in the picture below.

Table-of-articles

 

Step 2: Following that, we must build a date section by creating a timeline and inserting the date formula:

=DATE (year,month,day) 

Applying-date-function

 

After that using the =D5+1 formula, we extend the timeline by one day till 10 Jan 2021.

Extending-timeline

 

Step 3: Now we are going to apply Conditional formatting by using this logical formula:

=IF(AND(D$5>=$B6, D$5<=$C6),”YES”,”NO”)

Explanation: It will return “YES” if the D5 (01-Jan-2021) falls between B6 (01-Jan-2021) and C6 (15-Jan-2021), indicating that the article is active on that day. If the article is not active on that date, then it will return “NO”.

Applying-conditional-formatting

 

Step 4: Select all the cells from D6 to M13. Then drag auto-fill down to get all results.

Dragging-to-auto-fill

 

Step 5: Choose a cell from D6 to M13. Then, on the home menu, pick Conditional Formatting and then select New Rule there.

Selecting-new-rule

 

The dialogue box New formatting rule will then display. Then select to use a formula to determine which cells to determine and then enter the formula =D6=”YES” in the formula tab.

New-formatting-rule

 

After you click on format, the format cell dialogue box will display. and then pick the fill field, then choose any color, and then OK.

Formatting-cell-dialogue-box

 

Step 6: As a result, then it will show a Gantt chart.

Gantt-chart

 

Step 7: To create a dynamic Gantt Chart, simply choose any cell. F2 cell is selected here. Pick the developer tab, then go to the insert tab, select the scroll bar, and then draw it on the F2 cell.

Selecting-scroll-bar-from-insert-option

 

Step 8: It will look like this. Now, right-click on the scroll bar and select the Format Control option.

Selecting-format-control-option

 

The format control dialogue box should now display. You must complete certain fields there. First, you set the maximum value field to 365 since there are 365 days in a year. Select any one empty cell in the cell link to insert the value. The M2 cell is used in this case.

Format-control

 

Step 9: This arrow > now assists in changing the date. So, in this case, you must update the date formula that is written at the start. Replace the day with the cell M2 that you choose on the cell link in the format control box.

Updating-date-formula

 

Step 10: When you’re finished, you’ll have a final Dynamic Gantt chart, now you can see by clicking on the arrow it changes accordingly.

Dynamic-gantt-chart-created

 

After you’ve gone through this procedure with a couple of your projects, making a Gantt chart will come naturally. In less than 10 to 15 minutes, you can create a Gantt chart from your project spreadsheet.

Make an Excel Gantt Chart Template

Excel also offers free online Gantt chart designs. We’ll teach you how to make an Excel online Gantt chart template in this part.

Step 1: Go to File > New. Enter “Gantt” in the search box and press the Enter key. All Excel online Gantt chart templates are now being searched. Click on one of the templates to customize it.

Searching-gantt-chart-templates

 

Step 2: Then a popup appears with a preview and introduction to the selected Gantt chart. Select the Create option.

Selecting-create-option

 

Step 3: The Gantt chart is then built into a new worksheet. To make the Gantt chart usable, just change the provided data with the data you want.


Last Updated : 06 Dec, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads