If you often have to build schedules for projects, vacations, trainings, etc., that is, any long and complex multi-stage events, then this simple but beautiful trick will come in handy. Let’s say we have a table like this:
The idea is to use conditional formatting to force Excel to fill a cell with a given color if the date falls between the start and end of the step. To do this, select the entire range where the chart should be (in our example, from cell D3 to the end of the table) and then click on the tab Home (Home) button Conditional Formatting – Create Rule (Conditional Formatting — New Rule), choose the last type Use a formula to determine which cells to format (Use a formula to determine which cells to format) and enter the formula:
In essence, this formula does a simple thing – a function И (AND) checks that two conditions must be met so that the date for the current cell is later than the start date of the stage and earlier than the end date. If both of these conditions are met, then the cell is inside the stage, i.e. must be filled. Clicking on the button Framework (Format) you can choose the color you want.
Simple and beautiful, isn’t it?
In a more “fancy” version, such a chart can change the time scale step, take into account the presence of weekends and holidays, and highlight the selected row/column:
The basis here is still the same – conditional formatting. Plus added:
- delay before the start of the stage (can be positive or negative or =0)
- percentage of completion for each stage with their highlighting on the diagram
- function WORKDAY (WORKDAY) for calculating not calendar, but working days
- highlighting holidays and weekends with the same conditional formatting using the function DAY (WEEKDAY)
- coordinate highlighting of the current row and column using a special macro
Of course, we can’t outdo Microsoft Project, but it’s quite possible to get closer to it in the visual part 🙂
- Building a Gantt chart in Excel 2010 (video tutorial)
- Highlight duplicates with color