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!
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!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
I'm working on a book about charts and graphs that QUE will be publishing in March or April and was reminded of a cool old trick that we created one time for one of my friends.
He wanted to create a Gantt chart right on a worksheet.
He didn't want to create a chart, just right in the worksheet cells wanted to be able to visualize, how certain models ran over periods of time.
So I have a worksheet here with years going across the worksheet.
And I made these stand up by using the Format Cells dialog box.
On the Alignment tab I've chosen the vertical text to make those years very narrow.
And then I have the Model here, the Start date and the End date and this is a live kind of chart.
If I say that this Model started in 1903, the chart automatically updates to show those dates that it was available.
Now here's how this works.
We use something called Conditional Formatting, but it's unlike most Conditional Formatting that you might have tried.
I want to go back here to a sheet that doesn't have the Conditional Formatting set up, I'm going to select all the cells in the heart of my chart and use Format, Conditional Formatting.
Instead of Cell Value Is, I need to change this first drop-down to say a Formula Is and now I can write any formula that evaluates to True or False.
It'd be simple enough to write one formula to color the cells gray, but I'm actually going to create three different formulas, one to identify if this is the first year of the range, in which case I want the border to go along the left edge.
My formula is $B2, the $ says we're always going to look at the start date in B2 and see if it's equal to D$1.
In other words, look up above us in row 1 to see if the start year from column B is equal to this year above us in row 1.
Once I've entered that formula, I'm going to click the Format tab, on the Patterns I'm going to choose the color that I’d like to use, let's maybe use a green color and then, on the Border tab, specify that I want a solid border on the top bottom and left side of that cell.
Click OK, I have the first of three formulas set up, if I click Add, I can create another condition.
Formula is this time: I want to see if it's the ending year.
So I want to check and basically see if =$C2=D$1.
If it is, I'll click the Format tab, choose the exact same color on the Patterns, but this time on the Border tab say that I want the border on the top, bottom and right.
Click OK, one last formula.
I click Add, change it to Formula Is, this time I need to check for two conditions.
This time I'll use the And function, so that's =AND(D$1>$B2, in other words: is this year greater than the start year, a comma and then the other piece of the And is D$1<$C2), in other words: the ending year.
So for this format I'm going to choose the exact same pattern color, but on the Border tab I only want the border on the top and the bottom not on either end.
Click OK, click OK and you'll see now that I have a nice outline around the entire range and it's filled with green, these are live formulas, if I change the ending date, Excel will automatically extend or contract the bars.
A great trick to create cool visualization in Excel without using a chart whatsoever, using Conditional Formatting and the Formula Is option.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm working on a book about charts and graphs that QUE will be publishing in March or April and was reminded of a cool old trick that we created one time for one of my friends.
He wanted to create a Gantt chart right on a worksheet.
He didn't want to create a chart, just right in the worksheet cells wanted to be able to visualize, how certain models ran over periods of time.
So I have a worksheet here with years going across the worksheet.
And I made these stand up by using the Format Cells dialog box.
On the Alignment tab I've chosen the vertical text to make those years very narrow.
And then I have the Model here, the Start date and the End date and this is a live kind of chart.
If I say that this Model started in 1903, the chart automatically updates to show those dates that it was available.
Now here's how this works.
We use something called Conditional Formatting, but it's unlike most Conditional Formatting that you might have tried.
I want to go back here to a sheet that doesn't have the Conditional Formatting set up, I'm going to select all the cells in the heart of my chart and use Format, Conditional Formatting.
Instead of Cell Value Is, I need to change this first drop-down to say a Formula Is and now I can write any formula that evaluates to True or False.
It'd be simple enough to write one formula to color the cells gray, but I'm actually going to create three different formulas, one to identify if this is the first year of the range, in which case I want the border to go along the left edge.
My formula is $B2, the $ says we're always going to look at the start date in B2 and see if it's equal to D$1.
In other words, look up above us in row 1 to see if the start year from column B is equal to this year above us in row 1.
Once I've entered that formula, I'm going to click the Format tab, on the Patterns I'm going to choose the color that I’d like to use, let's maybe use a green color and then, on the Border tab, specify that I want a solid border on the top bottom and left side of that cell.
Click OK, I have the first of three formulas set up, if I click Add, I can create another condition.
Formula is this time: I want to see if it's the ending year.
So I want to check and basically see if =$C2=D$1.
If it is, I'll click the Format tab, choose the exact same color on the Patterns, but this time on the Border tab say that I want the border on the top, bottom and right.
Click OK, one last formula.
I click Add, change it to Formula Is, this time I need to check for two conditions.
This time I'll use the And function, so that's =AND(D$1>$B2, in other words: is this year greater than the start year, a comma and then the other piece of the And is D$1<$C2), in other words: the ending year.
So for this format I'm going to choose the exact same pattern color, but on the Border tab I only want the border on the top and the bottom not on either end.
Click OK, click OK and you'll see now that I have a nice outline around the entire range and it's filled with green, these are live formulas, if I change the ending date, Excel will automatically extend or contract the bars.
A great trick to create cool visualization in Excel without using a chart whatsoever, using Conditional Formatting and the Formula Is option.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
