What is “Conditional Formatting”?

By using conditional formatting, we can format cells based on its contents.

In other words, by using “Conditional Formatting” for data in our table, we can visually analyze data and detect possible problems.

An example: if a cell has a value of less than 1,000, then color it red.

We find the “Conditional Formatting” in the tab “Home”, under the “Styles” group.

Since the 2007 version, the “Conditional Formatting” command is much improved, both from a use as well as formatting perspective

In the 2010 version, there are a number of formatting features which are added.

 

Adding “Conditional Formatting”

The first thing we do is select the “Range” on which we want to apply “Conditional Formatting”. The quickest way to do so is to select the cell with the figure in the upper left corner and click the Ctrl Shift END shortcut on your keyboard.

As an example, below we have some sales amounts for sales representatives realized in the first six months of the year.

Next, we click the button “Conditional formatting” in the “Ribbon”. This opens a drop-down menu with different formatting options, divided into categories. The category ‘Data Bars “,” Color Scales ” and ” Icon Sets ” are the ones where everything is automatically resolved. When you move your mouse over a layout in the drop-down menu (1), you can see the result immediately in the “Worksheet”. What this actually does is comparing the numbers in the “Range” with each other and depending on the differences in value, makes cell color lighter or darker. Nothing more, nothing less.

Whats new in the 2010 version is that a negative number is shown in red on the left of the cell (2). If you wish to apply formatting, click it in the drop-down menu. Easy and above all, quick.

You can also apply this “Conditional formatting” multiple times on a “Range”. For example, a data bar with coloring and icons.

Remove “Conditional Formatting”

If you have made a mess, you can remove the “Conditional Formatting”.

Just click the “Conditional Formatting in the “Ribbon” and select “Clear Rules “in the drop-down menu.

Click “Clear Rules from selected cells” or “Clear Rules from Entire sheet”

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats