How to Make a Gantt Chart in Excel
Creating a Gantt Chart Template in Excel
In this video, the presenter demonstrates how to create a Gantt Chart template in Excel, including features like the scroll bar and the ability to show the progress of each task.
Setting up the Table
- The essential information for a Gantt Chart is a list of tasks with start and end dates.
- Use CTRL+SHIFT+3 to apply the default date format.
- Finish entering data into the table.
Creating the Timeline
- Create a timeline for the chart area starting with November 1st.
- Use custom number format "d" to display just the day of the month.
- Use text function to display abbreviation for day of week and left function to grab first letter of that text.
- Merge these seven cells to display full date of first day of week.
- Copy group of seven columns to right.
Making Chart Area Dynamic
- Add place to enter project start date and link first cell in chart area to that date.
- Use formula to add one day to each of next dates so that when project start date changes, chart area will change.
Formatting Chart Area
- Turn off gridlines and insert rows at top for title later.
- Apply dark background with light font for table headers.
- Change vertical alignment of entire worksheet to middle.
- Add indenting for tasks and horizontal borders for easier readability.
Adding Finishing Touches
- Merge cells and use custom number format to display day of week, day, month abbreviation, and full year for project start date.
- Add border to show that project start date is meant to be edited.
- Use format painter to copy formatting to next two weeks.
- Use conditional formatting to shade cell when date at top of column is within start and end dates.
- Name cell B3 "project_start" using name box and edit formula to subtract weekday of project start so that weeks in timeline will always start on Monday.
Adding a Scroll Bar and Conditional Formatting
In this section, the speaker demonstrates how to add a scroll bar to a Gantt chart and use conditional formatting to mark today's date and represent progress.
Adding a Scroll Bar
- Add an input for scrolling the Gantt chart.
- Use the Scroll Bar form control in the developer ribbon to change the display week.
- Insert the Scroll Bar by going to Developer > Insert and selecting the scrollbar form control.
- Select the cell to link to, which is "display_week".
Marking Today's Date with Conditional Formatting
- Go to Conditional Formatting > New Rule > Use a Formula.
- Create a formula that compares today's date with the date at the top of the column.
- Add red borders so bars in chart are still visible.
Representing Progress with Data Bars
- Add new columns for task assignment and progress tracking.
- Use conditional formatting with data bars to represent progress within cells.
- Edit formatting rule to represent 0% - 100% using gray color.
- Create formula using names for easier readability.
- Expand days complete by multiplying task_progress times total days (task_end - task_start + 1).
Clearing Cells and Setting Print Settings
In this section, the speaker clears cells and sets print settings for a Gantt Chart.
Clearing Cells
- The speaker clears cells before trying out the Gantt Chart.
Setting Print Settings
- The speaker defines print settings for a Gantt Chart.
- A Gantt Chart usually works better in landscape orientation.
- The speaker sets margins to Narrow.
- The top and bottom margins are changed to half an inch.
- Scaling is changed to fit on one page wide.
- The scrollbar is removed from printing by going to Properties and unchecking Print Object.
- Rows are set to repeat on each page.
- Page numbers are added to the footer.
- The speaker finishes with adding their logo.
Conclusion
In this section, the speaker concludes the video.
Conclusion
- Watching this video has helped viewers learn at least a few new things about Excel.
- This is the first video of a new series where other useful spreadsheets will be created and features will be added to this Gantt Chart.
- Viewers are encouraged to subscribe and provide feedback.
Turn any video into a summary like this
YouTube links, meetings, lectures ā with transcripts, search, and chat.