The new Excel 2007 Conditional Formatting features allow you to apply icons and bar color scales instead of only colors and formats to a cell or array.

So you will see your spreadsheets converted into more powerful visual dashboards!

And this is just the tip of the iceberg! Yes, Excel 2007 brings other enhancements to this command. I will show them to you here…

For those who have not heard about Excel conditional formatting, it is a command that allows you to format cells or arrays based on its values or on the values of other cells.

The new functionality means that you can to do these things even better…

• Being informed in real time
• Get clues when editing massive databases
• Answer questions visually
• Analyze data: find exceptions, find relationships, find trends, etc
• Presenting data
• And more…

I will show here the 4 things I love about the Excel 2007 Conditional Formatting command. You will love them too!

You will want to put your hands to work on this immediately…

1) I love Excel 2007 Conditional Formatting because it has a new friendly user interface

You can easily select the type of rule you want to apply on the “Select a Rule Type:” area (see graphic below); and then you can edit the rule parameters on another clear separated area called “Edit the Rule Description:”

I like the fact that all the rules are shown at once, this way you have a sense of the big picture of which rule would be better to apply for a given scenario (Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only values that are above or below average, Format only unique or duplicate values, Use a formula to determine which cells to format).

Give a look at the New Formatting Rule dialog box…

Additionally, with the new Conditional Formatting Rules Manager (see graphic below) you can do several things you could not do before. For example:

• Now you can see/edit the range that the rules apply to
• Now you can move up/down each rule so you change the order in which the rules apply (so simple now but a not so straightforward process in Excel 2003)
• That’s not all, you can choose which workbook rules to show: selection, sheet, etc (see “Show formatting rules for:” area at the top of the manager dialog box below). This way you can work on a limited set of rules that you want to analyze the order of application, etc

In summary, the usability of conditional formatting command increased.

You can easily navigate through rules to administer them: creating, changing, deleting, reordering, etc. If you have been using the conditional formatting command on Excel 2003 you know that these little enhancements make a great difference.

2) I love Excel 2007 Conditional Formatting because it has pre-built formatting options

Once you access the command on the new Excel 2007 Ribbon (Home>Styles>Conditional Formatting), you can pick the preset rules and apply formatting right away.

For example: on the graphic below, you can choose one of the 5 preset formatting rules: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets.

After you choose one of them, you can then easily specify preset format criteria. More below…

Once you choose the type of rule and enter the condition, you can select a rich preset color configuration for your cells (see graphic below).

These preset formatting options allow you to prepare common formatting schemes quickly and easily. So in a couple of clicks you can format your cells as desired.

When can’t you use the preset rules and formatting?

As you will always need to create new and complex rules, you should create such criteria from scratch using the New Formatting rule manager (Home>Styles>Conditional Formatting>New Rule) or the Conditional Formatting Rules Manager ((Home>Styles>Conditional Formatting>Manage rules).

You will also love the next feature…

3) I love Excel 2007 Conditional Formatting because it has more rules and you can now move them up and down and more…

Excel 2003 and users of “previous versions” conditional formatting are limited to only 3 conditions (rules); see graphic below. There’s no such limit in Microsoft Office Excel 2007; you may have as many conditional formats as you like. And you can administer them all efficiently using the Conditional Formatting Rules Manager (see graphic above).

Let’s illustrate this shortcoming with an example…Imagine you had four age description ranges you would like to slice and dice with formatting. <2 infants, 2 – 18 children, 19 – 65 adults, >65 years old. Sorry, you are not able to do it with Excel 2003 conditional formatting (only 3 conditions).

Additionally, you cannot easily change the order of the conditions. Now you can! Yes, using the up/down arrows provided on the Conditional Formatting Rules Manager (see “Conditional Formatting Rules Manager” graphic above)

Here is another example…

Imagine you enter in Excel 2003 the conditions in this order:

• 2 – 18 children
• 19 – 65 adults

And then you want to add the condition <2 infants at the beginning. You need to reenter everything. You are not able to change the order of the conditions.

The other major limitation of conditional formats in Excel 2003 and earlier versions is that you couldn’t have multiple conditions be true for the same cell.

In Excel 2007, you can control whether the conditional formatting stops or continues after Excel discovers that a specific condition applies to a cell.

Don’t miss the next loved feature…

4) I love Excel 2007 Conditional Formatting because it has 4 new rule types (conditions)

More rule types means you can represent more types of trends and data; this can lead you to making awesome presentations and gaining powerful insights from your data.

You will hear great comments from the people who use your spreadsheets. And you will see the meaningful formatting taking shape as you change values in your sheet.

There is no reason to not put this to good use after you know the capabilities of the type of rules that Excel 2007 brings:

1. Format all cells based on their values (New)
2. Format only cells that contain
3. Format only top or bottom ranked values (New)
4. Format only values that are above or below average (New)
5. Format only unique or duplicate values (New)
6. Use a formula to determine which cells to format

Now, let’s explore each one briefly and envision some practical applications…

Rule 1) Format all cells based on their values (new)

This type of rule formats several cells based against the same scale. This rule type allows you to find relationships in your data.

The graphic below shows three columns. Conditional formatting in column A highlights cells according to a two color scale, highlights cells according to a data bar scale in column C, and highlights cells according to a icon set scale in column E.

You can compare sales, heights, etc.

Now, let’s explore the format styles very briefly…

Format style: 2 color scale and 3 color scale

Applications

• Sales categories
• Dates
• Age descriptions
• College grades
• And more…

Format style: Data bar

Format style: Icon set

Rule 2) Cells that contains

This type of rule is useful when you want to spot trends individually on cells. For example: highlight the cells that contain value greater than 10,000.

The graphic below shows two columns. Conditional formatting in column A highlights values = 5, and highlights blanks in column C.

Applications

• Identify specific numbers, dates, and text in a list of products
• Find blanks, non blanks
• Identify dates that occur within specific periods like: today, yesterday, etc.
• And more…

Rule 3) Top/bottom rank (new)

You can use it to spot the top performing products, top performing students, Pareto 20%, etc.

The graphic below shows two columns. Conditional formatting in column A highlights top 20%, and highlights bottom 20% in column C.

Applications

• What are the top performing products in sales
• Who are the top two students in the class
• And more…

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!