Make a Gantt Chart in Excel – Part 4: Color Coding


Welcome to How to make a Gantt chart in
Excel, Part 4. This video will show how to change the color of the bars in the Gantt Chart
by choosing a color or using a formula to assign a color based on the lead name.
I’ll also show how to highlight overdue tasks. This video includes some advanced Excel topics and I cover a lot in a short amount of time,
so be ready to pause and rewind as needed. First, let’s insert a new column and call
it “CLR” for color. Make it narrow, and then enter some letters to represent Red, Green,
Orange, Blue, Purple, Cyan, Magenta, Yellow, and Black. Now I’ll create a relative named range for the color column Go to Formulas>Name Manager>New.
Call it task_color. Choose Sheet1 as the scope. Choose the cell in column C that is on the same row, then make the row number relative. The font color in the chart area is white,
so I’ll change it to black so that I can demonstrate how this
relative named range works. Anytime we refer to task_color in a formula it will return the value from column C. The next step is to create a new conditional formatting rule for each of the different colors. Select any cell in the chart area for now. One method would be to
make new rules just like the purple one, adding an extra condition that
checks the letter in the color column, but I’ll show a different approach. I’m going to create a new rule that for now is just equal to TRUE and does not apply
any formatting at all Then I’ll copy the Applies To range from
the purple bar rule. When I apply the change nothing happens yet, but now I’ll check the “Stop If True” box. When this rule is TRUE, it stops
any of the rules below it from being applied to the cells
within the Applies To range. Move the rule down to
just above the purple rule and click Apply. Now only the purple rule below it is blocked, because rules are evaluated from top to bottom. Now I’ll change the ‘stop’ rule to only be true when the bars should NOT be highlighted. That may seem confusing at first
but it will simplify things later. First, I’ll copy the formula for the purple bars. Then I’ll edit the formula
for the new rule and paste the formula inside the NOT function. And then I’ll change the purple bar formula to=TRUE. I set this rule to TRUE because it
will be our default color. Now let’s add the rule to make the bars red. All we need is the formula task_color=”R”
with R between double quotes. The R can be upper or lowercase. Now we move this rule down below our
stop rule and above our default purple rule. And then copy and paste the Applies To range. I’ll move the window so you can see what
happened. So now we just repeat this for all of the other colors. Notice that I’m using these colors
instead of the theme colors. That is because theme colors can
change if the theme has changed and we want R to remain red and G to remain green. Red, orange and yellow are good colors to use for indicating urgency, Red being the most urgent and Orange being the next most urgent. I’m adding a rule for choosing purple as a color even though purple is our current default. By “default,” I mean the rule that is at the
bottom and set to true. This one here. In theory, using this approach ought to be more efficient than repeating our
complicated formula for every rule. To be even more efficient we could check the
Stop If True box on all of these new color rules. The letter C is used for cyan.
I like this one well I think I’ll choose a color with a bit more green in it. I would recommend choosing a set of
colors that won’t hurt people’s eyes. For example, for magenta we could choose this bright color, but I don’t like how bright it is, so I’m
going to choose a lighter shade instead. If you struggle to create color schemes that won’t offend the artistic eye, I have one main tip for you: Choose a main color and then for your
other colors just change the Hue. I will demonstrate this in a minute when I
change the default color. We will use K for black because B is already used for blue and K is commonly used for black. We don’t have diamonds for milestones in
our Gantt chart yet but we can use a black color for milestones. Now I’ll change the default color to brown. To demonstrate changing just the Hue, I’ll
choose this purple as my base color, Then go to More Colors, choose HSL from
the Color Model drop down, and enter 22 for brown. You can make some decent color
schemes by just changing the Hue. if you want to choose the colors manually for
each task it may be helpful to create a drop-down list. Go to Data Validation, choose List, and enter
the color codes separated by commas. This will help you remember what
colors are available. One way to use color coding is to assign
colors by the name of the assigned Lead. Start by going to the Settings
worksheet and creating a table for listing the names and their color codes. Nothing complicated here so I’ll do this quickly. Now I’m going to select the table and
use the Name Box to name it lead_table. For the drop-down list in the Lead column I’m going to be fancy and use the INDEX function to return the
first column of the lead table by leaving the second argument blank, which
is normally the row number. I’ll enter a few names in the lead column now. The formula in the color column is going
to be a VLOOKUP function with cell B8 as the lookup value. We’ll use IFERROR to handle blanks or when the name isn’t listed in our table, and have it show it a dash. Now just copy the formula down and it’s done. Now these bars are color coded by the name in the Lead column, and everything else uses the default color. Another useful feature to include in a
Gantt chart is to highlight overdue tasks. One way to do that is to edit the
formula in the color column. If task_end=this cell, making the column relative. Now we’ll choose the fill color to be red. Move the rule directly under the stop rule then
copy and paste the applies to range. This is great but let’s make this feature
something you can turn on and off. Go to the Settings worksheet and add a label
called “Show Overdue” Then use a data validation list to
choose between TRUE or FALSE. Now let’s name this cell show_overdue
in the Name Box. Now we’ll edit the rule to include
the new show overdue option. If you want to quickly turn this on and off
you can insert a check box form control. I’ll update the label to “Show Overdue” Then right-click on it and select Format Control. In the Cell Link field, type=and
the named range show_overdue. Now we have an easy way to turn it on and off. Another way to highlight overdue tasks is to
highlight the date in the End column. Select the range and go to
Conditional Formatting>New Rule. The formula for this one will be very similar to the others or will also include the show
overdue option. Remember we always write the rule
based on the upper-left cell in the range, so the last condition is F8

Leave a Reply

Your email address will not be published. Required fields are marked *