Summary of "How to Make a Gantt Chart in Excel"

This tutorial from Vertex42 demonstrates how to create a dynamic and feature-rich Gantt Chart in Excel, including task progress tracking, scrollable timelines, and Conditional Formatting for visual clarity. The video guides viewers step-by-step through building the chart from scratch, focusing on practical Excel techniques and formulas to automate and enhance the chart's functionality.

Main Ideas and Concepts

Basic Setup
- Start with a list of tasks including start and end dates.
- Format columns and enter sample data.
- Use keyboard shortcuts (e.g., CTRL+SHIFT+3) to apply default date formats.
Creating the Timeline
- Build a date range starting from a chosen project start date.
- Use custom number formats to display only the day of the month ("d").
- Display the first letter of the day of the week using TEXT and LEFT functions.
- Merge cells to show the full date for the first day of each week.
- Make the timeline dynamic by linking it to an input cell for the project start date and using formulas to increment dates.
Formatting the Chart
- Turn off gridlines and add rows for titles.
- Style headers with dark backgrounds and light fonts.
- Use vertical alignment, indentation, and borders to improve readability.
- Format the project start date cell with a custom date format showing day, month, year, and day of the week.
- Use the Format Painter to replicate formatting across multiple weeks.
Adding Gantt Bars with Conditional Formatting
- Avoid manual coloring by applying Conditional Formatting rules.
- Use a formula with the AND function to shade cells where the column date falls between the task start and end dates.
- Apply a theme color fill (purple) for the bars.
Adjusting Timeline Start to Always Begin on Monday
- Name the project start cell for easier reference.
- Modify the start date formula to adjust backward to the previous Monday using the WEEKDAY function.
Making the Timeline Scrollable
- Add an input cell named "display_week" to control which week is displayed.
- Modify timeline formulas to offset dates based on the display week.
- Enable the Developer Ribbon to insert a Scroll Bar form control.
- Link the Scroll Bar to the "display_week" cell to allow scrolling through weeks dynamically.
Highlighting Today’s Date
- Add a Conditional Formatting rule comparing column dates to TODAY().
- Use red borders to mark the current date without obscuring task bars.
Adding Task Details and Progress Tracking
- Insert columns for task assignees and percent complete.
- Enable wrap text and adjust font size for readability.
- Use Conditional Formatting “Data Bars” to visualize progress within the percent complete column.
- Customize data bar settings to represent 0% to 100% progress with subtle gray fill.
Advanced Progress Bar in Gantt Chart
- Create a Conditional Formatting rule to shade part of the Gantt bar based on task progress.
- Use named ranges (task_start, task_end, task_progress) with relative referencing for formula clarity.
- Calculate the shaded portion by multiplying progress by total task duration.
- Convert logical TRUE/FALSE results to 1s and 0s to apply formatting correctly.
Final Touches and Printing Setup
- Adjust print settings for landscape orientation and narrow margins.
- Scale the printout to fit one page wide.
- Prevent the Scroll Bar from printing by adjusting its properties.
- Set rows to repeat on each printed page for clarity.
- Add page numbers and a company logo to the footer and header.
Additional Notes
- The video is the first in a series promising more Excel templates and feature tutorials.
- Viewers are encouraged to subscribe and provide feedback.

Detailed Methodology / Instructions

Prepare Task Table
- Enter tasks with start and end dates.
- Format dates using CTRL+SHIFT+3.
- Adjust column widths and add headers.
Build Timeline
- Enter a project start date input cell.
- Use formulas to fill timeline dates dynamically.
- Format dates to show day number and day initial.
- Merge weekly date headers and format them.
Apply Conditional Formatting for Gantt Bars
- Select chart cells.
- Create a new Conditional Formatting rule using formula:
```
=AND(date_cell >= task_start_date, date_cell <= task_end_date)
```
- Apply fill color for bars.
Adjust Timeline to Start on Monday
- Name project start cell.
- Modify formula to subtract weekday offset to get Monday.
Add Scroll Bar for Timeline Navigation
- Insert an input cell "display_week".
- Adjust timeline start date formula to offset by (display_week * 7) days.
- Enable Developer Ribbon and insert Scroll Bar form control.
- Link Scroll Bar to "display_week" cell for dynamic scrolling.