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:
- Format all cells based on their values (New)
- Format only cells that contain
- Format only top or bottom ranked values (New)
- Format only values that are above or below average (New)
- Format only unique or duplicate values (New)
- 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
- Sales categories
- Age descriptions
- College grades
- And more…
Format style: Data bar
- Compare heights of the tallest mountains
- Traffic accidents
- And more…
Format style: Icon set
- Quickly see revenue status and trends from one quarter to the next
- Compare different product criteria by using a rating scale
- Examine profit trends from month to month
- Identify regional sales below $1,000,000
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.
- 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.
- What are the top performing products in sales
- Who are the top two students in the class
- And more…
Rule 4) Above/below average (new)
The graphic below shows three columns. Conditional formatting in column A highlights “above average” cells (average =5.5), highlights “below average” cells on column C and highlights the cells that belong to the “1 std deviation above” in column C.
- Identify top, bottom, and above average values in a marathon training log
- Identify top, bottom, and above average values in students’ scores
- Identify top, bottom, and above average values in sales
Rule 5) Duplicates (new)
The graphic below shows two columns. Conditional formatting in column A highlights duplicate cells and highlights unique cells in column C.
- Find duplicate rows in a list of customers
- Find unique entries in a list and filter them out
- And more…
Rule 6) Formula
By using a formula, you can create any condition you can imagine.
The graphic below highlights the column A cells when the corresponding cell in column B is greater than 50
- Format alternate rows in a range
- Compare data in a cell outside the conditionally formatted range of cells
- Format an entire row where more than one condition must be true
- Format an entire row if the row is a unique value
- Format cells that match a set of parameters specified in another group of cells
- Identify a dynamically changed number or text value in a list of products
Manipulating rules is an important task, now you have the functionality to become a conditional formatting star.
And as I said to you above, more rule types and more representation types mean you can present data in more ways, get the message delivered and gain more new insights from your data.
You will increase satisfaction and confidence at work.
What are you waiting for?