Conditional Formatting in Excel

Let's continue with learning a little bit more about Excel. This time we will review conditional formatting. It is very useful when you need to present data and quickly identify variances in a range of values.
Let's say you have a table with information on how much your employees sold in 2010 and 2011. Now you would like to identify top 3 and bottom 3 sales for each year. How can we do that quickly?
First, let's start with 2010 sales. Highlight the cells that contain the information you would like to work with.

Then go to Styles group in the Home tab.

Click on Conditional Formatting. A new menu will open up. In this case we would like to highlight only top 3 and bottom 3 sales for each year, so we will put our mouse button over Top/Bottom Rules. Let's select Top 10 Items...


Once we click on that, a new menu will open up.

We will change to only top 3 cells to be formatted and change the color of the formatting. Because it is top 3, we would probably want it green. So let's change that and then click OK.
Your worksheet should now look like this:

Highlighting bottom 3 sales is the same, only make sure that you will change what kind of formatting you would like; red, yellow, or you may customize it as well.
You might have notices that there are many different options to apply conditional formatting to your table, so experiment! When you are not quite sure what a certain option does, scroll over it with your mouse and a box with an explanation will appear. For example: we weren't sure what a data bar - gradient fill meant, but Excel helped us out like so:

In the end, you will find that conditional formatting can make certain data stand out and help you analyze it and identify its patterns and trends faster.

Tags: 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.