Create a Timeline with Conditional Formatting in Excel

Entering this formula in conditional formatting in Excel can create a quick and easy time line:

=IF(LEN(G$4)>0,AND(F$4>=$C5,G$4<=$D5),F$4<=$D5)

. . . where you have the start dates of projects listed in column C, the completed by dates in column D, and a list of dates in the range for a project or event beginning in F4 and continuing in that row to the right. It may make more sense to list the end dates of projects on your timeline than completed by - the latter being the day after the project is to be completed.

See Figure 1 below, with tasks listed in column B. When you have your data fully entered in columns B to E, and also row 4, follow these steps:

1. Select cell F5.

2. In Excel 2010, go to the Home tab, Conditional Formatting . . . New Rule.

3. Pick 'Use a formula to determine which cells to format'.

4. In the box labeled, 'Format values where this format is true', enter:

=IF(LEN(G$4)>0,AND(F$4>=$C5,G$4<=$D5),F$4<=$D5)

See Fig. 2.

5. Click and format and choose a fill color.

6. Pull the formula all the way to the right of the chart, and then down by pressing CTRL + D.

You'll now have a nice timeline showing the time ranges of each of the tasks you have listed.

Download an Excel file with the sample chart and the conditional formatting formula already entered on this page: http://soshea0.wix.com/litsupporttips#!create-a-timeline-in-excel/r38if

Note that you shouldn't try to add the formula to the full range of cells in the Conditional Formating rules manager. Just transfer the formula using the fill handle or CTRL + R, and CTRL + D.