Conditional Formatting in Excel 2010

Sometimes when you have a lot of numbers in a worksheet, visual formatting may be very useful. In earlier versions of Excel you can only have up to three coditional rules applied to a cell. If you wanted to use more rules you had to use macros. Since Excel 2007 this has changed and now you have much more flexibility when using conditional formatting.

On the example below you have 30 numbers between -100 and 100.

Let's use conditional formatting to make these numbers a little easier on the eyes. You can find a Conditional Formatting icon on the Home tab in Styles group. When you click a Conditional Formatting button you can see that you have a few categories.

In the first group you can see there are two categories: "Highlight Cells Rules" and "Top/Bottom Rules".

First category will help you to create conditions such as greater than, between, less and so on, when you don't know how many cells will meet a condition. Second category helps you create conditions such as top 10%, bottom 3, where you know how many cells meet a condition, but don't know a specific range for condition.

The second group gives you different types of formatting for every number you've selected.

When you use formatting in the second group every cell will be formatted accordingly to its value. There are three categories in this group. "Data Bars", "Color Scales" and "Icon Sets".

Select all numbers inside our worksheet and then Choose "Data Bars". There are two options: gradient fill and solid fill. Negative values are represented by one color on the left side and positive values are represented on the right side by different color.

Click Ctrl-Z to undo and then choose "Color Scales". This formatting options format cells by filling them with color. Intensity is based on the value inside a cell. You can also have different colors for negative and positive values.

The third category is "Icon Sets". This category shows different icons based on a value inside a cell.

Clear Rules

If you want to clear rules, select "Conditional Formatting"->"Clear Rules". We have a few different options. We can clear rules from entire sheet, from select cells, from tables and from pivot tables. In our example we have only first two options.

Manage Rules

You can create your own specific rules and merge them inside "Conditional Formatting Rules Manager" window.

Let's create two different rules and merge them inside our sheet. The first rule will only fill positive values and the second rule will only fill the top 3 values. Choose "Manage Rules" and then click "New Rule". Choose "Format only cells that contain" and set the options to cell value greater than 0. Then click "Format button" and choose light blue color. Click ok and create a new rule. Now choose "Format only top or bottom ranked values". Set top 3 and click "Format". Select Darker blue and click ok. Now you have two format rules.

Remember the order of the rules. When you change order in the example you won't see the 3 top values.

Polska wersja kursuExcel, gdzie możesz nauczyć się podstawowej obsługi Excela.