Look Who it Was Random video

Excel - Build a Gantt Chart in Excel - Episode 427

1,926 views · Published 24 September 2009 · 4:18 · Indexed 3 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to Build a Gantt Chart in Excel.

Welcome back to the MrExcel netcast! In today's video, we're going to be talking about a cool old trick that we created for one of my friends who wanted to create a Gantt chart right on a worksheet. He didn't want to go through the hassle of creating a chart, so we came up with a solution that allowed him to visualize how certain models ran over periods of time, all within the worksheet cells.

I have a worksheet here with years going across the top. To make them stand up, I used the Format Cells dialog box and chose the vertical text option. Then, I have the model name, start date, and end date listed. The best part? This is a live chart, meaning that if I change the start or end date, the chart will automatically update to reflect those changes.

So how did we create this Gantt chart without using a chart at all? We used Conditional Formatting, but with a twist. Instead of using the typical "Cell Value Is" option, we used "Formula Is" to write our own formulas that would evaluate to True or False. I'll walk you through the process step by step so you can create your own Gantt chart right on a worksheet.

First, select all the cells in the heart of your chart and go to Format, Conditional Formatting. Change the first drop-down to "Formula Is" and then enter your first formula. In my case, I wanted to identify if it was the first year of the range, so my formula was $B2=D$1. This checks if the start year in column B is equal to the year above it in row 1. Then, choose a color and add a solid border on the top, bottom, and left side of the cell. Repeat this process for the ending year and any years in between.

Finally, we need to account for the years that fall outside of the range. For this, we use the AND function to check if the year is greater than the start year and less than the end year. Again, choose the same color and add a border on the top and bottom only. And just like that, you have a Gantt chart right on your worksheet that will update automatically as you make changes to the start and end dates.

I hope you found this trick helpful and can use it to create your own Gantt charts in Excel. Don't forget to subscribe to our channel for more tips and tricks, and we'll see you next time for another netcast from MrExcel. Thanks for watching!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction
(00:20) Creating a Gantt chart on a worksheet
(00:30) Using Format Cells dialog box
(00:45) Using Conditional Formatting
(01:07) Setting up Conditional Formatting
(01:31) Creating multiple formulas
(02:28) Applying formatting to cells
(03:02) Using the AND function
(03:24) Live formulas and automatic updates
(03:48) Conclusion and thanks for watching

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Conditional Formatting in Excel
Creating a live chart in Excel
Creating a timeline in Excel
Customizing borders in Excel
Excel netcast from MrExcel
Excel tips for charts and graphs
Excel tricks for visualization
Formatting cells in Excel
Gantt chart in Excel
Using formulas in Conditional Formatting
Using the Format Cells dialog box in Excel
Visualizing data in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152639/

This cool trick shows you how to create a Gantt chart right in the Excel cells by using Conditional Formatting. The chart is a live chart that colors cells based on several data values. Episode 427 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel