You may have heard about a Gantt chart while working on a project and if you worked in Microsoft Project then you have definitely seen one too. A Gantt chart’s horizontal bars let you visually track project phases and tasks, and, in this article, we’ll dive into using Excel alone to create such a tracker. Each task is represented by a horizontal bar that spans its start and end dates. The chart updates automatically based on task status and dates, using conditional formatting to highlight active periods. It looks complicated, but once you see its design under the hood you will see how simple this actually is. Let’s dive in.
To follow along with me, feel free to download this sample spreadsheet I’ve made for this article
This is what we will be aiming for:
Conditional Formatting For Open Tasks
Each column in the timeline grid represents a single day. The formatting logic compares the date in the header row to the task’s start and end dates. If the header date falls within that range, the cell is shaded using conditional formatting.
First highlight all cells in the first row for all dates, in our case it is F2:Z2
- Go to Menu >> Conditional Formatting >> Manage Rules >> New Rule
- Rule type: Use Formula to determine which cells to format
- In the Rule Description field add the below formula
=AND(F$1>=$B2, F$1<=$C2)
- Click the format button below
- Set the Fill to whatever color you want (in my case I chose teal color)
- Click OK and then click Apply
- Your first row should now shade itself in the range between the start and end date of the first task
- Now copy that row you highlighted and paste it all the way down through your tracker to apply that formatting to all tasks in your project plan
- Done
Note
This formula checks whether the date in the column header (e.g., F1) is greater than or equal to the Start Date (B2) and less than or equal to the End Date (C2). If both conditions are true, the cell is shaded teal.
Pitfall: Dates That Look Like Dates But Aren’t
If your header row contains text that looks like dates (e.g., “Jan 2”) but isn’t formatted as actual dates, the formula will fail silently. Excel won’t throw an error—it just won’t apply the formatting. To fix this, format the header cells as Short Date and re-enter one of them to trigger proper recognition.
Adding Conditinoal Formatting For Completed Tasks
In MS typical project plans when tasks are completed and no longer relevant, they are usually marked as grey bars, so you can visually distinguish them from active tasks or those which have not even started. The conditional formatting is very similar to the one we did above, with one key difference: we need to enhance the formula to also include a decision whether the task status = Complete or not.
- First highlight all cells in the first row for all dates, in our case it is F2:Z2
- Go to Menu >> Conditional Formatting >> Manage Rules >> New Rule
- Rule type: Use Formula to determine which cells to format
- In the Rule Description field add the below formula
=AND(F$1>=$B2, F$1<=$C2, $D2="Completed")
- Click the format button below
- Set the Fill to whatever color you want (in my case it’s medium grey)
- Click OK and then click Apply
- If your task status is Complete, your row should now shade itself in the range between the start and end date of the first task and be grey
If your task is not marked Complete, just temporarily change the status, so you can test the formatting - Now copy that row you highlighted and paste it all the way down through your tracker to apply that formatting to all tasks in your project plan
- Done
- From now on, all tasks (if their dates are within the range of your column matrix) should show with either grey or teal color
Once done formatting your first row your conditional formatting rules should look something like this:
Duration Formula
Good project plan charts also include duration for each task. Instead of entering this manually, we can easily use Excel to calculate how long each task lasts. So in the Duration cell, of the first task, just enter the following:
=C2-B2+1
Note the formula in cell E2
This accounts for inclusive date ranges. Without the +1, Excel would treat a task from Jan 2 to Jan 6 as only 4 days long. The +1 makes it 5.
Why To Apply the Rule to One Row First?
When creating these rules, apply them to only the first row of your timeline grid (e.g., F2:Z2). Once the rule is tested and works, use Format Painter or copy-paste formatting to apply it to the rows below.
Do not highlight the entire grid and apply the conditional formatting rule in one go. This causes Excel to misinterpret relative references and breaks the logic.
Absolute References: Why They Matter?
In the formula F$1>=$B2, the $ symbol locks the row or column. $B2 means column B is fixed, but the row can change. F$1 means row 1 is fixed, but the column can change. This ensures the formula behaves correctly as it’s copied across the grid. Why?
This spreadsheet is only covering a range of a couple weeks. Now imagine that you wanted to add extra columns and make it last 180 days! If you copied the conditional formatting and did not have the absolute references ($) in the formulas, Excel would move the formula references by as many columns as you’d be expanding the grid, completely breaking everything, because it would no longer read the start and end date columns. The $ helps fix the formula, like a dog on a chain. It won’t move, no matter where you paste or move to.
I have a separate article about absolute references, if you want to dive into them!
Going Next Level
If you wanted to take this spreadsheet to stratosphere and really please your boss who hates MS Project (don’t tell anyone, but so do I), you could also somewhat automate the date ranges displayed in the matrix.
If you look at the attached spreadsheet linked above you will notice that the date range is from 1/2 through 1/22. those dates are hardcoded. So, as your project moves along and spans multiple months, this spreadsheet could become very unwieldy and hard to navigate.
A fix? Make the dates in row 1 dynamic! How?
- Add a few empty rows about this table
- Pick three cells in there and name them: Today’s Date, Lookback and Date Range
- Today’s Date: should be formatted as a date and have a formula =TODAY()
- Date Range: should be a number and you’d manually enter how many days you wanted to see in your grid, such as 30, 60, 180, etc….
- Lookback should be a number and you’d manually enter how many days backwards from today’s date you want to see – eg: see 14 days back
- Now, in cell F1 (or whatever row your first table header ended on after you inserted rows above it) you’d enter =TODAY()-[Lookback]
- And to populate the range of dates to the right dynamically? Use a =SEQUENCE formula! But that is a whole different article to write! I may once day… I may
Need help streamlining your spreadsheets or solving tricky Excel problems? I offer quick one‑on‑one consultations to get you unstuck fast. Find me on my blog below!
Want more practical Excel tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for updates: goarcherdynamics.com
Leave a Reply