• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 3. Building Basic Formulas > Using Condition Values

Using Condition Values

The action of the formats you've seen so far have depended on whether the cell contents were positive, negative, zero, or text. Although this is fine for most applications, sometimes you need to format a cell based on different conditions. For example, you might want only specific numbers, or numbers within a certain range, to take on a particular format. You can achieve this effect by using the [condition value] format symbol. With this symbol, you set up conditional statements using the logical operators =, <, >, <=, >=, and <>, and the appropriate numbers. You then assign these conditions to each part of your format definition.

For example, suppose that you have a worksheet for which the data must be within the range –1,000 and 1,000. To flag numbers outside this range, you set up the following format:

[>=1000]"Error: Value >= 1,000";[<=-1000]"Error: Value <= -1,000";0.00

The first part defines the format for numbers greater than or equal to 1,000 (an error message). The second part defines the format for numbers less than or equal to –1,000 (also an error message). The third part defines the format for all other numbers (0.00).

NOTE

Excel also enables you to apply a particular font automatically when a cell meets a specified condition. This is called conditional formatting, and you apply it by choosing Format, Conditional Formatting. To construct the condition for a value, choose Cell Value Is in the first list, and then select a comparison operator (such as Between or Less Than) in the second list. You then enter one or two values (depending on the operator). Finally, click Format to select the font formatting to apply to the cell when the condition is met.


  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint