For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Getting Organized

Use Conditional Formatting to Manage Employee Tasks

A manager needs to assign employee color codes to various tasks they perform for customers. Here's one way to do it with conditional formatting.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
"I have a simple Excel sheet with a list of to-do items down the left hand column. I have clients along the top. For each employee, I have designated a color and have shaded the inside cells showing who is responsible for each item for each client. I put an "x" in a cell when a task is finished.  

"Is it possible to set a formula that says "any time an 'x' is entered in a cell, it will change to a predetermined color?" This would make it much easier to see what needs to be done at a glance." -- Lee Z.

If you use a version of Excel that has conditional formatting, you can do this easily. But Excel 2007 can provide additional capabilities. I'll show you both approaches, but spend more time on the Excel 2007 solution.

The Solution for Excel 2007 and Above

As you add staff, you're going to run short of good colors. It will be more difficult to keep the various colors in mind. And it will become more tedious to assign each color pattern by employee.

This Excel 2003 task list assigns employees to tasks using color codes.

Also, with your current solution, you can't see which employee had been assigned a task after it's been completed. This is information that you probably would find useful at times.

This Excel 2007 task list assigns employees to tasks using color codes.Therefore, this version of your task list uses conditional formats to assign the employee colors.

Each employee has been assigned a one-character code, as shown in the Employees section. Then, when you enter the code for each task in the left column beneath a customer's name, the cell to the right automatically displays the appropriate color.

(You could use longer employee codes, of course. But the longer the code, the more time it takes to enter and the greater the chance of a data-entry error.)

This task list uses Excel 2007's conditional formats to mark completion of a task.As before, when you enter an "x" into a colored cell, it changes to black, as shown here.

Also, notice in both figures that Excel 2007 offers more control over the shading of your colors.

To set up this display in Excel 2007, first enter the text and format the borders as shown.

The customer names do not use merged cells, which I try to avoid wherever possible. Instead, they're aligned using Center Across Selection.

The dialog launcher for the Alignment group in Excel 2007.To set up these labels, enter the customer names in cells B2, D2, and F2. Select the range B2:G2 and choose Home. In the Alignment group, choose the dialog launcher, shown here. In the Alignment tab, in the Horizontal dropdown list box, choose Center Across Selection. Then choose OK.

Assign the color codes to your employees. To do so, choose the Home tab. Then, in the Font group, use the Fill Color icon to fill each cell as shown. (I know, the Fill Color icon has nothing to do with fonts. But then, neither does the Borders icon, which is in the Font group as well.)

You now can assign a conditional format for each employee. This wasn't possible in Excel 2003, because it allowed only three conditional formats. But Excel 2007 removed that limit.

Select the range in your worksheet to assign conditional formatting in Excel 2007.To begin, select the range C3:C6, as shown here.

But when you make this selection, remember one critical thing:

Carefully note your active cell.

The active cell is important because you must enter all conditional formulas that use relative or mixed references from the point of view of the active cell.

Let's review some terms here:

  • An absolute reference is like "$C$5". No matter where a formula is used, it always refers to cell C5.
     
  • A relative reference is like "D3". If the active cell is D4, then D3 refers to the cell immediately above the cell that uses the reference. But if the active cell is E3, then D3 refers to the cell immediately to the left of the cell that uses the reference.
     
  • A mixed reference is like "M$8" or "$B6". It includes both an absolute and a relative reference.

Therefore, when you enter a cell reference, any row number or column letter that doesn't have a "$" in front of it is relative to the active cell.

With the range C3:C6 selected, and with C3 the active cell, choose Home, Styles, Conditional Formatting, Manage Rules. In the Conditional Formatting Rules Manager dialog, choose New Rule. An illustration of using a formula to set conditional formatting in Excel 2007.In the New Formatting Rule dialog, choose "Use formula to determine which cells to format." When you do so, Excel displays a dialog like the one shown here.

Enter the following formula in the edit box labeled: "Format values where this formula is true:":

=B3="b"

(Remember, our active cell is cell C3. Because cell B3 is immediately to the left of C3, this formula causes each cell in your selection to look one cell to its left for the value it uses for conditional formatting.)

Choose Format to assign the cell pattern for Bob, the employee with the "b" code. After you assign the color pattern, choose OK to return to this dialog, then choose OK again to return to the Conditional Formatting Rules Manager dialog. Your dialog now should look like the figure below.

The Conditional Formatting Rules Manager after one rule has been defined in Excel 2007.

Continue this process for each employee. As you add a rule for each employee, it will be added to the top of the list in the Rules Manager. Finally, when you're done with your employees, you have one more rule to add: the rule that handles the "x" when a task is done.

To add this rule, choose New Rule again. But this time, in the New Formatting Rule dialog, choose "Format only cells that contain".  The first dropdown list box says "Cell Value", which is what we want. Change the second dropdown to "equal to". In the third box, which is an edit box, enter:

="x"

Then choose Format to assign the black background color for completed tasks. Choose OK, and then OK again to return to the completed dialog shown below.

The Conditional Formatting Rules Manager after many rules have been defined in Excel 2007.

At this point, you might wonder about precedence. That is, we now have two rules that affect each cell. One rule sets the employee color and the other rule turns the cell black. How do we know which rule will win?

The Rules Manger dialog, shown here, determines the precedence. The rules at the top of the list win out over the rules that are below them. Because we created the "x" rule last, it naturally appeared at the top of the list, and therefore takes precedence over all other rules.

To change the precedence of a rule, click on it and then click on one of the two arrows to the right of Delete Rule near the top of the dialog above. The final task manager set up to use conditional formatting in Excel 2007.Each time you click on an arrow, the rule changes position in the list, changing its precedence.

To complete your project sheet, copy the range C3:C6 and paste it to cell E3; then paste it again to cell G3.

Finally, one last thought. When you edit a rule, you'll make fewer errors if you select the correct range and active cell. The range you need to select is shown in the "Applies to" column of the Conditional Formatting Rules Manager above. And the active cell always needs to be the first cell that appears in that column for the rule that you're changing. 

The Excel 2003 Solution

For Excel 2003 and before, here is a simplified version of what you describe.

There are four tasks for each of three customers. And each task has been assigned to one of the six employees shown at the bottom of the figure.

This task list uses Excel 2003's conditional formats to mark completion of a task.When a task is done, you enter an "x" into the cell. This can turn the cell black, as shown here.

To set this up, select the range B3:D6. Then choose Format, Conditional Formatting.

The first drop-down list box will show Cell Value Is, which is what we want. Change the second drop-down list box to equal to. Then enter...

="x"

...in the third box.

Choose Format, and assign the color pattern you want. Choose OK to complete your formatting. If you choose the black color pattern like I did, your Conditional Formatting dialog now should look something like the one below.

The conitional formatting dialog for Excel 2003.

Now, when you enter "x" into a formatted cell, the cell should turn black.


You now should have a convenient and colorful way to manage your projects.

 

Create Variable-Length, Dynamic Reports Linked to Excel Tables

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts