Conditional Formatting

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Using Excel Conditional Formatting

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Conditional Formatting

See Also: Highlight Duplicates in Excel With Conditional Formatting. Download Some Great Example Uses for Conditional Formatting!

In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It can be found via the Worksheet Menu Bar under "Format". It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

A simple example would be to use Conditional Formatting to highlight all cells in any given range that are greater than 100. Let's say this range is A1:A100. We would Start by selecting cells A1:A100, Starting from cell A1. This will ensure A1 is the active cell in the selection and from this, Excel will know all other cells we specify are relative to cell housing the Conditional Formatting. Now go to Format>Conditional Formatting and then choose "Cell value is", then "Greater than" and then type 100 in the far right, see below;

Now click the "Format" button and choose the desired formatting for all cells greater than 100. After this, click "Ok" then "Ok" again. The same logic can be applied to any other of the criteria we can choose. E.g "Less than", "Equal to" etc

The only drawback with this way, is that cells housing text may be seen as having values greater than 100! We can over-come this by resorting to the "Formula is" option as apposed to "Cell value is". When using the "Formula is" option, any formula we use, MUST evaluate to either True, or False. With this in mind, here is a formula that we can use in place of simply 100.

=AND(ISNUMBER(A1),A1>100)

Again, it is vital the you select Starting from A1 as all other formulas, in A2:A100, will change their cell references relatively. That is, A2 Conditional Formatting will read;

=AND(ISNUMBER(A2),A2>100)

If you are not already aware, both conditions in an AND Function must evaluate to True for the Function to return True. In other words, all cells in A1:A100 will need to house a number AND the number must be greater than 100 for the chosen formatting to be applied.

See Also: Highlight Duplicates in Excel With Conditional Formatting. Download Some Great Example Uses for Conditional Formatting!

TIP! If you need to reference another Worksheet when using Formula is you can do so in 2 ways.

Name the range , then use the name apposed to the range address.
Enclose the range is the INDIRECT function. E.g INDIRECT("'Sheet2'!A1:A100")

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft