#158: Excel Conditional Formatting

Conditional formatting in Excel is a somewhat hidden, yet extremely useful gem that can transform your data from an unintelligible mess into something meaningful and good looking. Better still, you don’t even need to be a guru or excel pro to be successful with this trick. You can learn some cool conditional formatting tricks over a nice, warm cup of coffee (did I mention that it’s COLD here?)

So go grab a cup of coffee and we’ll get started as soon as you get back?

Oh, great you’re back. Let’s dig in….Most spreadsheets of any size present you with row after row of numbers that are not always easy to interpret. With conditional formatting, it only takes a glance to highlight and compare the data that is important to you. What conditional formatting does is allow you to format cells based on the contents of that or other cells based on the ‘rules’ you create.

To illustrate this, the example below shows conditional formatting that automatically highlights any sales values less than $5,000 (in red) and greater than $15,000 (in green).

Before:



After:


How Its Done:

1. Select the cells to be formatted.

2. From the Format menu, select Conditional Formatting…

3. In the Conditional Formatting dialog box, leave the first drop-down box set to Cell Value Is

4. In the second drop-down box, choose one of the operators.

5. In the text box, type a number or a cell reference.

6. Click the Format button to set your font and cell formatting options.



7. Click OK, to close the Format Cells dialog box.

8. If you want to add another conditional format, click the Add button.

9. Repeat steps 3 to 8, using the values and colors that you want for the second conditional format.

11. Click OK, to return to the worksheet and see the results.


NOTE: There is a limit of 3 sets of formatting options.
* Unless you have the Excel 2007

How could you use conditional formatting on your data? Click here to send me a note and let me know.

Have a great weekend!
Mike

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s