Did you know? You can use column rules (or conditional formatting) to add emphasis to individual fields, and even entire rows, on a report?
Conditional formatting can be used to call attention to specific information on a report. For example, you could highlight numbers in a budget column in red when costs are running high. Or you could highlight projects that are in the Planned or Current statuses in different colors to help you track work in progress.
For this example, we have a task report that looks at all of the open tasks assigned to our team. We need to quickly see which of these didn’t get completed by their due dates—in other words, the tasks that are late. So let’s highlight the field in red.
We'll walk through this in this linked video. The steps are also outlined, below.
Start by editing the report and clicking the Due On column. This is the one that shows the task planned completion date.
From there, click Advanced Options and then Add a rule for this column.
First, set the condition, or criteria, for when the formatting will apply to the report. You’re looking for the task planned completion date, so leave the criteria as is.
You want due dates that are BEFORE today, so select Less Than from the qualifier drop-down menu.
Finally, set the value field to the date-based wildcard $$TODAY. This criterion is now looking for planned completion dates that are before today. The date wildcard makes the criteria flexible, so no matter which day of the week or month or year you’re looking at the report, the conditional formatting applies to tasks with due dates before the current date.
Now decide what kind of formatting you want to show up on the report. You can make the text in the column bold or italics, plus change the text alignment. You can also change the text color. For this report, you just change the background color of the field to red. That will make the due dates that are before today really stand out.
Note, in regards to formatting, there are a few options worth discussing:
You can make the entire row red. Just click the “apply to the entire row” button. However, as you can see in the preview at the bottom of the window in our example, having the entire row appear in red can be a bit much, so we chose to confine the red to just the planned completion date column.
You can select an icon or display specific text. However, there’s a disadvantage to each of these options.
If you select an icon—such as a smiley face or a red button—this replaces the information that normally appears in the column. In this case, that means you won’t see the actual due date in the column.
When you select Show Text, this allows you to enter custom text, instructions, or a note to display when the condition—planned completion date is before today—is met. But again, this text will replace the actual date in the column.
Returning to our example, everything is set so let’s click Add Rule to save our changes.
Now let’s look at our report. We can see several tasks that were due before today. The conditional formatting makes them easy to identify!
You can create multiple rules for the same column. You’ve already set a rule to highlight tasks with due dates that have passed in red. You could set another rule on the planned completion date column to highlight tasks that are due between today and the end of the week in green. And a rule that highlights tasks due between the end of this week and the end of the quarter in yellow.
Conditional formatting is an easy way to make the information you’re looking for on a report easily identifiable.