What is Excel Conditional Formatting and why is using it so advantageous?

In a few words: ECF is assigning formats to cells if their values meet a given condition. For example: in the graphic below…if the balance is negative, the cells are colored…

It appears simple at first sight but once you start using it, you uncover many practical applications. And you will never be the same after you add this professional ingredient to your spreadsheets.

Here I will share with you some of the handy applications and benefits of using Excel Conditional Formatting command extensively…

Before we move forward…I want to share this thought with you: If Excel Conditional Formatting is so important then…Why don’t Excel users use it frequently? I really don’t know, I personally didn’t use it too much either until I discovered my productivity increased with a few applications.

I think we need to know some WHYs so you start using this awesome command right away.

And the more you use it, the more you will find creative uses for it.

Here are the reasons…

1) Excel conditional formatting keeps you informed in real time

You can create your own checks. As soon as they occur you will be notified by a change in cell formatting. Colors will get your attention and you will never miss a model feedback. This of course means you can make informed decisions and make them faster.

For example:

• You can get a red color each time you insert a duplicate
• You can create a check to alert you each time you enter a non-valid value
• You can get an orange color as your month spending reaches a preset limit
• And more…

Real time highlights means you get real time insights. You can make decisions right away instead of allowing the errors remain hidden or replicate in second instances that can lead to catastrophic consequences and delay important decisions.

That’s not all…Immediate feedback can help you to keep alert of underlying patterns taking shape!

When important decisions happen fast and accurately, it’s the type of thing that catapults careers and businesses into leaders of their field. When your work helps to make your company a rock star, or even to take a few solid steps in that direction, bosses have a tendency to offer you rewards and promotions.

Don’t miss the second great application…

2) Excel conditional formatting helps you edit massive databases

It is a very common task to edit data that does not comply with your requirements: duplicates, length of cell content, numbers stored as texts, etc.

This happens because Excel communicates with such diverse software packages and these programs don’t export the data as we would like. When working with thousands of lines of data, this can often be overwhelming.

You can highlight those problems while editing the raw data so you don’t over sight any error. And even better, you can combine the highlight with filters and sort commands to edit massive rows in seconds

Examples of conditional formatting on Excel:

• Highlight Duplicates and decide which ones to delete
• Highlight Unique entries and decide which one to keep
• Highlight invalid data so you can filter them and correct them
• Highlight specific text
• Highlight  dates occurring in certain periods: today, last week, etc
• Highlight Blanks, No blanks, Errors, No errors
• And more…

Here’s more…

3) Excel conditional formatting helps you to answer questions visually

As a data analyst you play with a table to get specific answers. While you can create custom filter criteria or use lookup formulas or Pivot Tables, you can create conditional format criteria to show you where the answers are.

Examples of these questions may be:

• What are the trends in product preferences over the past two years?
• Who has sold more than $100,000 dollars this month?
• What is the overall age distribution of employees?
• Which products have greater than 10% revenue increases from month to month?
• Who are the highest performing and lowest performing students in the freshman class?

“The more you use it, the more you will find creative uses for it”.

4) Excel conditional formatting saves time

Since Excel conditional formatting is updated automatically, you can manipulate text on the go, you can change the underlying cells and see what’s going on, what’s still missing or needs to be added, etc

What I mean is that once you set the conditions that trigger the formats, you focus on editing, analyzing and making decisions accordingly without extra configuration.

5) Excel conditional formatting helps you analyze data

If you put conditional formatting on key metrics that need to be measured and monitored against target values, then you can take a look at your dashboards and get a better understanding of patterns.

Conclusion

Additionally, my satisfaction is increased since I feel a kind of connection with my own models. End users also feel comfortable when they are notified by color rules.

No excuses not to start using Excel Conditional Formatting and let YOUR inner ‘Excel Rock Star’ loose today!