![gantt chart in excel 2016 gantt chart in excel 2016](https://www.addintools.com/documents/project/image/shot-project-gantt-chart-wizard.jpg)
The conditional format will be copied to the entire range of the Gantt Chart and you will end up with a chart that looks like this one.Ĭonditional Formatting Applied Data RangeĬonditional formatting is a great tool and lets you easily create Gantt-type charts right on the worksheet. Highlight D2:W6 and from the menu select Edit - PasteSpecial - Formats - OK.
![gantt chart in excel 2016 gantt chart in excel 2016](https://www.smartsheet.com/sites/default/files/IC-Simple-Gantt-Chart-Template.png)
![gantt chart in excel 2016 gantt chart in excel 2016](https://i.ytimg.com/vi/NKeRBvLtgPA/maxresdefault.jpg)
Whether the cell turned yellow or not, click on D2 and use Ctrl + C or Edit - Copy to copy that cell. If your upper left cell in D2 happens to fall in a year, that cell will turn yellow. Click OK to close the Format Cells dialog and you should be left with a Conditional Formatting dialog that looks like this oneĬlick OK to dismiss the Conditional Formatting box. Next, choose a bright color to be used whenever the condition is true. Make sure to start with an equal sign or the conditional formatting will not work. In the formula box of the Contional Formatting dialog, enter this formula. We still need to use the same relative & absolute addressing, so this will be D$1=$B2,D$1<=$C2) The second condition will check to see if the year in Row 1 is less than or equal to the date in column C. Note that the dollar sign before the 1 in D$1 will insure that our formula always points to row 1 and that the dollar sign before the B in $B2 will insure that it always compares to column B. Since I always want this formula to refer to row 1, the first part of the formula is D$1>=$B2. The first condition will check to see if the year in Row 1 is greater than or equal to the year in column B. This means that we are going to start with the =AND() function. There will be two conditions to check and both will have to be true. It is important to use the right combination of relative and absolute addresses so that the formula we enter in D2 can be copied to all of the cells in the range. The goal is to enter a formula that checks to see if the year in row 1 above this cell falls within the ranges of years in columns B & C of this row. Change this dropdown to "Formula Is" and the right side of the dialog box will change to a large textbox for entering a formula. The dialog box initially has a drop down on the left side that defaults to "Cell Value Is". From the menu, select Format - Conditional Formatting. Select the upper left cell of the Gantt chart area, or D2 in this example. Then select Format - Column - Autowidth and you will be able to see all 23 columns on the screen. In order to make the years take up less room, select D1:W1, then using Format - Cells - Alignment, select the vertical text option. You can then select D1:E1, click on the fill handle in the lower right corner of the selection with your mouse and drag out to column W to fill in all of the years from 1901 to 1920. Starting in column D, I entered the first year 1901. I scanned through my data and noticed that the dates range from 1901 to 1919. The next step could easily be incorporated into a macro, but the real focus of this technique is setting up the conditional formatting. I am using years for my example, but you could easily use regular Excel dates. There is an event, then start dates in column B and end dates in column C. I imagine that Phil's data looks something like the table to the left.
![gantt chart in excel 2016 gantt chart in excel 2016](https://www.adventuresincre.com/wp-content/uploads/2016/10/Excel-Gantt-Chart-for-Real-Estate.png)
This type of chart would solve Phil's question. That tip from the summer of 2001 mentioned that you could also create a Gantt-type chart on a worksheet using conditional formatting. This topic was covered in the Create Timeline Chart tip. Is there any way to take two columns holding start and stop dates for individual events and create a Gantt type chart without having to leave Excel? Phil wrote this morning asking about creating chart in Excel. Gantt Chart With Conditional Formatting.