Gantt chart with conditional formatting - EASY

The other day a manager stopped by my desk and asked me if I could help them with a problem they were having with creating a Gantt chart. He had a manager meeting in 15 minutes and he wanted to put together a Gantt chart for the meeting. Now granted he had worked on it for a few hours before stopping by so it’s not like he waited until the last minute.

Well it was time to break out the mad crazy Excel skills (actually it’s quite easy to do but let’s keep that secret to ourselves).

All you need to create a Gantt chart is a few bits of information and two formulas and you can have one that looks like this.

I first created a list of project activities with start and end dates. Then I created a “calendar” with the start date and end date with increments of 7 days. Make sure to format the calendar as short date.

Now, whenever a day falls between start and end day for a corresponding activity, I want to highlight that cell so I first need to identify if the calendar date falls between the start and end date.

=IF(AND(D$3>=$B4, D$3<=$C4),1,"")

That returns a 1 in the cell D4.

I used the $ sign in the formula to lock the calendar date. Next I just copy the formula into all the calendar cells (D4 through AG13).

Now all my cells that meet the criteria have a 1. In the above example I have made the font blue.

Next I use conditional formatting to fill the cell the same blue color whenever the cell value = 1.

Highlight all the cells between D4 through AG13.

Go to Conditional formatting dialog (in 2007, Home tab > Conditional Formatting > New Rule…)

Select Use a formula to determine which cells to format. And enter in the following formula. Then select the format button and choose the fill tab and select the same blue color that you made the numbers.

The last step is to sit back and bask in the glowing compliments of your boss.

Download the example here.

New Window – See two tabs at the same time using ALT + W then N

Okay, you have a file with two tabs and would like to be able to see both tabs at the same time.  Sure you could save the spreadsheet and open the saved copy in another window but who has time for that.

Press and hold the ALT and W key at the same time. Release both keys then press the N key.

Ta Da! You now have you one file displayed in two windows. Now you can click on a different tab is each window. Best of all, since it’s still one document, when you make changes on one tab, the change impacts both windows.

Confused???

Basically when you do this you have the same Excel Workbook shown in a vertical fashion.  This lets you view different sections of the same data at the same time.

Need to see three tabs at once? Just do the ALT + W then N again.

You can also select New Window from the View Tab. This does the same as ALT + W then N. Clicking Arrange All will allow you to see the different windows.

The next time the boss and you are working together. Show off and use this trick to ensconce yourself as the Excel expert of your team.