Hellow guys, Welcome to my website, and you are watching Excel - Build a Gantt Chart in Excel - Episode 427. and this vIdeo is uploaded by MrExcel.com at 2009-09-24T08:11:05-07:00. We are pramote this video only for entertainment and educational perpose only. So, I hop you like our website.
| Name | Excel - Build a Gantt Chart in Excel - Episode 427 |
| Video Uploader | Video From MrExcel.com |
| Upload Date | This Video Uploaded At 24-09-2009 15:11:05 |
| Video Discription | 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! |
| Category | Education |
| Tags | Excel | business | accounting | spreadsheets | tutorial | technology | MrExcel | chart | conditional formatting | color | Formula | Bill Jelen | Learn Excel | Excel Tutorial | Power Excel | Microsoft Excel | Excel Tips | Data Set | Excel Help | Learn Excel with MrExcel | Excel Tips and Tricks | MrExcel Podcast | Excel 2007 | mrexcel forum | mrexcel channel | mrexcel website | episode #427 | podcast #427 | gantt chart |
