Conditional Formatting in Excel

My Newest Book
Excel 2003 Study Guide

get it at Amazon.

FROM WILEY: Download sample Excel files to accompany the book.
Instructors may download tests, slides, outlines, etc. HERE

ABC
Free
Newsletter

Linda's Ebooks

Linda's CD

The Newbie Club

Online classes
at Eclectic Academy

FrontLook
Add-ins
& Screen Capture

WinBackup
SpeedUpMyPC
WinTasks5Pro

Conditional Formatting in Excel

Are your spreadsheets dull and boring?
Would you like to be able to quickly see which data falls within specific parameters?
Do you want your spreadsheet to warn you when certain dates, numbers or text are noted in your spreadsheet?
Are you constantly sorting and re-sorting data to find items that are similar or meet certain criteria?
Why not try Conditional Formatting?

Conditional Formatting is one of the things that's changed quite a bit in Excel 2007, so if you are using Excel 2007, read this tutorial instead.

In Excel, open a blank workbook and add some random numbers and text into various cells. Keep the numbers simple so you can easily see whether your formatting is working.
Hold down your left mouse button and drag across a selected area of cells to highlight them, then go to your Format menu and select "Conditional Formatting".
In this example, let's say you want to find all the numbers in your selection which fall within a certain range. In the Conditional Formatting dialog box, select "Cell Value Is" from the first dropdown box. Then, in the second dropdown box, select "between". Now, put your cursor in the third box, so it is blinking there. Then, click your cursor in a cell which has the lowest value to be included in your parameter and this cell number will now appear in the third box (or you can manually type in any number you choose). Then, do the same with the fourth box, this time choosing the high number of your parameter and this number or cell name will appear.
Now, click on the format button below the boxes which include your parameters and choose either a different font style or cell fill color (pattern) which will identify the cells which meet your criteria. (Although cell borders also show as an option, this does not work with Conditional Formatting.)

(Click on thumbnail pictures to see large displays. These screen captures are from Excel2000, but they are similar in older versions. They will open in new browser windows, so you can just close that window to return to this page.)

In this example, all cells with values between 270 and 71 appear with a blue background.

Now, let's say you want numbers over 1000 to appear in bold, red font.
With the cells still highlighted, go back to Format>Conditional Formatting, and you will see your original instructions still there. Click on the "Add" button and setup criteria number 2, this time choosing "greater than" from the second dropdown box, the number 1000 in the third box, and bold and red from the format font options.

In this example, numbers between 270 and 71 are in blue cells and those over 1000 are bold and red.

Now, add some text to your spreadsheet if you don't already have it there. Make sure some of your text is EXACTLY the same in different cells.
Let's say we want to highlight all occurrences of cell text which meets our criteria. Highlight the cells and go back to Format>Conditional Formatting and, this time, select "equal to" from the second dropdown box and type the text you want to highlight in the third box, click on the format button and make your choices. (When you type your text into the third box, Excel will automatically add an equal sign(=) in front of it and quotes (") around it.)

In this example, all instances of "Linda" are in bold italic font.

Now, what if we wanted to highlight every name BUT Linda? Highlight your cells and go to Format>Conditional Formatting, but this time, choose "not equal to" from the second dropdown box.

In this example, every name BUT Linda is in bold italics.

OK. Now for some real fun. Let's say we have dates in our data and we want to be warned when any record shows today's date.
Enter some dates into your spreadsheet. Highlight your data. Then go to Format>Conditional Formatting and this time, select "equal to" in the second dropdown box and, in the third box type < =TODAY() > (without the < > ). (You must type the equal sign (=) or Excel will add it with the quotation marks (") and will read this formula as text and be unable to find today's date.

In this example, any instance of today's date is displayed with a dark blue background.

What if we wanted to be alerted when a record is outdated....or due within the next week? Again, highlight your data and apply two conditions. One with the formula "less than" and =TODAY()......and one with the formula "between" and in the two boxes following, put =TODAY() and = TODAY()+8, then add your formatting.

In this example, all cells containing dates between today and 8 days from today appear with a red background and all cells with dates that are past (less than today) appear with a green background.

I encourage you to play around with this and try different parameters and formulas. Most of Excel's standard formulas will work with Conditional Formatting also.
How would you like to obviously see all records pertaining to a particular person or organization which fall within a certain range or occur at a particular date, EVERY TIME YOU OPEN YOUR SPREADSHEET? Well, Conditional Formatting is the way to go!