About Computers for Newbies & Everyone Else

December 14, 2010

Excel Tricks – Conditional Formatting

Filed under: Columns — Tags: , , , — Ron @ 5:28 am

Several weeks ago I did a couple of articles about tricks using Microsoft Word and your responses truly surprised me.  I thought that I may hear from a couple of Office Geeks but I heard from quite a few of you.  First, thanks for the emails, I always enjoy them even when you disagree…that tells me that they are being read! 

Recent "emailers" have asked me to add a couple of Excel Tricks so today that is what we will do.  I cannot imagine that there are that many of you Excel readers out there but because of the email requests I will certainly give you some info.

I will figure that Excel users know all of the basics on how to write a formula, etc. so I will skip those things.  If you don’t know those basics you need to invite me to your office for a couple of training sessions.

Conditional Formatting is a basic staple of Excel; however, it has been my experience that very few people use it since, at first glance, it looks a little intricate.  I guarantee once you try out Conditional Formatting (CF to save space) a few times you will want to continue to learn different, very practical ways to use it.

CF allows you to change the formatting of an individual cell based on the results being displayed in that cell. For instance, you could make the text in the cell bold and purple if its result is greater than a certain value. You could even color the background of a cell based on the result of a formula.  There are many possibilities. 

To start off with: select the cell, or group of cells, which you want to format according to its value or formula result.  For this example we will want to format the cell to bold yellow text with a red background if the resulting value is above nine.

imageFor Excel 2003 or earlier choose the Format menu, Conditional Formatting and then "Formula Is".

For Excel 2007 and above select the Home ribbon, the Styles group, Conditional Formatting. Drop down to New Rule and select "Format only cells that contain".

From here they will be similar, so choose the "greater than" option. Next SNAGHTML3963d2enter the number (9 for our example) then change the text format color, bold it and the fill (the background cell color).

When the answer to a formula or the actual number entered into a cell is above nine, the cell will be highlighted for easy recognitions.

You can also apply this for dates, words, etc.  Play with CF and see how you can utilize it.   This was a very basic look at Conditional Formatting.  It contains many more features.  Let me know what you think after you give it a try.


No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress

%d bloggers like this: