Using Conditional Cell Formatting In Excel 2007

One of the most powerful features of MS Excel is conditional formatting. With the help of this feature, you can dynamically modify the format of a cell whenever its value tends to change.

Technically, you set a condition or a formula, which makes Excel to apply the chosen formatting to the cell when it holds true. Now, this format change can be in terms of font, style, size, background color, and text color. For example, you can make MS Excel to highlight those cells whose values are greater than 75. Further, you can also tell MS Excel to fill red color in the numeric cells whose values would take up the last 10 positions if you were to sort them in descending order.

using conditional formatting in excel 2007

The good news is that MS Excel 2007 has widened the scope of conditional formatting, which means you can easily highlight cells based on many more conditions. In this article, we shall explore how to utilize Excel 2007’s conditional formatting by checking out the related options in the Conditional Formatting drop-down menu.

Accessing The Conditional Formatting Drop-Down Menu

Once you have entered data in your Excel sheet, you need to first select the cells to which you want to apply conditional formatting. Then, on the Home tab, click the Conditional Formatting drop-down button present in the Styles group. This will display the Conditional Formatting drop-down menu that has several fixed rules such as Data Bars, Icon Sets, and Color Scales.

Using The Highlight Cells Rules Option

This first option is useful when you want to compare the cell values. It offers rules such as Greater Than, Less Than, Equal To, Between, Contains, and Date. Let’s assume that you desire to highlight cells whose values are greater than 50.

To do so, select the entire column of the cells, open the Conditional Formatting drop-down menu, and select Highlight Cells Rules –> Greater Than. The Greater Than dialog is displayed wherein you need to enter 50 in the text box and select the formatting from the combo box.

color scales

Using The Top/Bottom Rules Option

This one is meant for dynamically formatting the cells according to the top 10, bottom 10, and above/below average rules. If you desire to see the top 20 items in a column having numeric cells at one go, just select this option and Top 10 Items from the submenu, enter 20 in the text box of Top 10 Items dialog, and then select the required formatting from the combo box.

Top Bottom Rules Option

Using the Color Scales, Data Bars, and Icon Sets Options

These three options highlight the selected cells in different ways as per their value range. The Data Bars option inserts statistical bars, while the Icon Sets option includes suitable icons such as arrows, tick marks, cross, and pie charts. The Color Scales option uses different color gradients or shades to represent a cell value. In short, you can make cells appear distinctively according to the values they contain.

Also Read

Import Text Into Excel 2007
Copy Website Tables Into Excel 2007 Spreadsheets
Make Excel 2007 Read Spreadsheets To You
How To Change The Gridlines Color In Excel 2007
How To Change The Default Font In Excel 2007 

Creating Custom Rules

In case none of the above options are useful for you, you can explore the New Formatting Rule dialog box to create your own rule.

This is something similar to what you must have done in Excel 2003. To open this dialog box, select Conditional Formatting –> New Rule.

If you want to edit or delete your own rule, you can do so via the Conditional Formatting Rules Manager that is accessible by selecting Conditional Formatting –> Manage Rules.



Dipaligandhi