Draw Attention with Conditional Formatting

Conditional Formatting is a powerful element that enhances your reporting. When asked for feedback on a report, I often recommend users use some form of conditional formatting to help draw attention to key elements. I won’t lie to you – it takes extra effort to make it work. However, with a little planning you can make your reports pop while guiding your readers to the most critical information on the canvas.

Why is Conditional Formatting Important?

Conditional formatting takes good reports and makes them great. When you design a report canvas, it can quickly become cluttered with too much information. It overwhelms the viewer and causes anxiety. As a result, energy is wasted on trying to understand what the report says. Conditional formatting helps resolve this issue for the viewer.

Conditional Formatting In Your Car

Think about driving a car and looking at the dashboard. There are several measures and metrics to tell you what is happening. A speedometer tells you how fast you are traveling. A tachometer tells you how many revolutions your engine is making in a minute.

However, some of the gauges have a little help. For example, your fuel gauge tells you how much fuel is in your tank. It is a simple – when the gauge reaches “E” or “0”, it is time to get more fuel. But with the bustle of everyday life, sometimes we forget to check the gauge. That is when the low fuel light comes on and alerts us that we need to stop and fill up. The conditions are simple – when the fuel level reaches a low enough point, the light turns on.

Now, the light is not really necessary. It is not required for your car dashboard to work. Yet, we find it useful as it draws our eyes to a potential concern so we can resolve it. It alerts you to an issue that pairs with an action to resolve it.

The check engine light works in a similar fashion. It alerts you of a problem that you have to address. Unlike the low fuel light, the check engine just tells you there is a problem. You will have to do a little more research to find the issue. If you don’t have access to a scanner, you might need to take your car to a garage to have it checked out. Either way, you know something is wrong and can resolve it before it becomes a bigger problem.

Augmenting Reports with Conditional Formatting

Conditional formatting does not replace your current report visuals. It draws the eyes to key information on the canvas so you can act. With a little splash of color, you can draw eyes quickly to important data points.

Now, you might be asking “don’t I need a legend or something to tell people what they need to know?” Believe it or not, you really don’t need to do much. If you highlight a value in bright red, almost all viewers will understand that it is bad. When you highlight something in green, it is good. That does not take much effort to understand.

The trick is properly apply the conditional formatting so users are able to understand what is on the canvas.

Applying Conditional Formatting

So all of this sound great. But how do I apply it?

I find the easiest place to apply it is with a table or matrix visual. Even though Power BI has lots of rich visuals, many people still hold on to their PivotTables from Excel. They love seeing data in a tabular format. This is a great introduction to how to apply conditional formatting in a standard report.

For this article, we will be looking at a simple table that lists sales revenue by month for 2021. Our goal is to call out values that need attention so our consumers can understand them.

Highs and Lows with Gradient Formatting

The table above lists out how much revenue was received per month. What if I asked you which month had the highest revenue. You are pretty smart, so you could figure it out fairly quickly. But what if the table had 5 years of revenue? It would take a considerable amount of brain power to figure it out. You could do it, but it would not be easy. Gradient formatting aides viewers in seeing which months had the highest revenue.

To start, we have to enable conditional formatting on the cell backgrounds. We will simply select the table visual, go to the visual properties, expand cell elements, select the revenue column, and turn on the background color element.

You will see that we can easily identify July 2021 as the month with the highest revenue. We also can see February as the month with the lowest revenue. There is no denying it – conditional formatting makes it much easier to read!

But this just shows highs and lows. What if I have a revenue goal that I want to meet. Gradient conditional formatting does not meet that requirement. So how do we take our visualization to the next level?

Thresholds and Goals with Rule-Based Formatting

Instead of highlighting on a gradient scale, we can set formatting on a series of rules. To get started, select the function (fx) button under background color. This will pop open a new window to allow us to make our changes.

From here, we will change the Format Style from Gradient to Rules. After that, I needed to add two more rows of rules to achieve my goal. From there I can establish my rules to highlight which months did not reach my revenue goal of $1.5 million dollars.

The first rule is to show anything that missed my revenue goal. So I will start with zero to $1.45 million. I then assigned the color red to that field. Why not $1.5 million? While that is my goal, I want to highlight any revenue amounts that were close to my goal, but did not quite make it. So my next rule highlights anything between $1.45 and $1.5 million with yellow. And finally, anything over $1.5 meets the goal and is highlighted in green. However, I need a top end value for that rule, so I just put $5 million in there as a place holder.

With my rules in place, it is much easier to tell which months met or missed the goal of $1.5 million in revenue.

It is easy to see that we hit our revenue goals in eight months of 2021. We missed our goals in February, April, and May. And while we were close, we did not quite reach our goal in June. This formatting draws our eyes and makes it easy to where we missed the mark so we can adjust our plans for 2022.

Can I Format the Entire Line?

The short answer is yes – you can do that. The only catch is that you have to update the rules for each individual column in your table. Just select the Month column and start making your changes.

The biggest thing you need to do is change the field the formatting is based upon. It defaults to the column you are formatting. However, we need to set it based upon the values in the revenue column.

Set the rules to match your original formatting and you will have the entire line formatted.

With all of this in place, I have provided conditional formatting leveraging the background color. This was easy because I only have two columns in this table. If I had a lot of columns, it might be more time consuming to setup.

Now, as nice as background colors are, I am constantly asked if there any other options out there. The answer is of course!

What Else Can I Do with Conditional Formatting?

I like using a cell’s background color but there are other options out there. If you want to use color, but the cell background is too bold, then you can use font color instead.

One of my personal favorites is to use icons with the report.

With icons, you can select different styles that suit your needs. You also have the ability to move the icon to the left or right of the values in the table. And if the occasion calls for it, you can just use an icon in the field instead of a value.

The other favorite is to leverage data bars. This aligns better with gradient conditional formatting, but helps show magnitude of the values better.

Regardless of what style of conditional formatting you use, I promise you it will elevate your reports and make them easier to understand.

Next Steps with Conditional Formatting

So with this knowledge of conditional formatting, should I know anything else? Of course – the biggest thing is knowing when to use conditional formatting. Quality report design rules still apply. Overuse of conditional formatting can cause your report canvas to look cluttered and confusing. It also could impact performance as calculations are taking place in the background to enable the formatting. Just remember that less is more.

The other thing you know is that conditional formatting can be applied to more than cells in a table or matrix. In fact, there are other elements of conditional formatting to share. I have an upcoming article that will dig into those features, so keep an eye open for it!

Until then, have you used conditional formatting? Do you have a use case ready to go? If so, tell me in the comments below!