Home : Conditional Formatting
Q11159 - INFO: Conditional Formatting

Conditional Formatting  is used to apply formats to a cell or range of cells in spread design, and have that formatting change depending on the value of the cell or the value of a formula.  For example, to BOLD all values that are above 10.

 

FIELDS:

Apply First True Rule Only (Default): Starting with Action 1 when a condition is met, set the cell format to the Action's format and stop. Example: Action 1 set to Italic if the value >10, Action 2 set to Bold if value > 20. Value of 30 would be Italic because 30 is greater than 10 and the first condition was met. 

Apply All True Rules:  Starting with Action 1 if a condition is met, set the action and move to the next action.  Example: Action 1 set to Italic if the value >10, Action 2 set to Bold if value > 20.  Value of 30 would be Bold, Italic.

When Value of Cell is:

Comparison: Select what type of comparison to make.  NOTE: when comparing a text cell the comparsion is NOT case-senstive (i.e. Hello is equal to HELLO, heLLo...).  When comparing numeric values, we compare the displayed value to the entered limit (i.e. a cell has a formula of =3.14159 but is displayed to 2 decimals so 3.14 is displayed, 3.14 is compared to the limit NOT 3.14159).   When comparing values with data qualifiers the entered value rule is used (i.e. <2 is treated as a 2, >1000 as 1000, ND as 0). Choose from the following list.

is Equal to 
is Not Equal to 
is Greater than
is Greater than or Equal to 
is Less than
is Less than or Equal to
is Between
is Between (Exclusive)
is Not Between
is Not Between (Exclusive)
Contains - If the character(s) entered in the limit are anywhere in the cell.
Does not contain - If the character(s) entered in the limit are NOT anywhere in the cell.
Is Detected - If the cell starts with "ND" or starts with "<".
Is Not Detected - If the cell does NOT start with "ND" or starts with "<".
Is Empty - If the cell is blank or empty.

Limit Indicates limit of condition for the variable to be used.  Can be a constant (i.e. 10, Hello), refer to a the value of a cell using the CELL(A1) syntax where A1 is the cell to compare to, or the value can be looked up from a variable field such as Daily Limit, Entry Limit etc...
Variable: If a lookup is choosen you must select the variable to get the value from.
Offset: When looking up the limit, you can specify which dates to use to lookup for.  For Example, if you are showing three months of daily values (each month is column) and your limit changes every month, for column 2 (2nd month of the report) you would set the offset to 2.  

When Data Approval Level:  Used to set the cell format based on a variables data approval level. 

for Variable: Select Variable used for condition
Grouped By: Spread Report Grouping functions return summary values for a variable for the specified Group Date Range such as Daily, Yearly, Monthly, Quarterly,...etc
Offset: Sets the number of ‘intervals' from the start date of the report. Increment by 1, for example,  increases the offset by one for the grouping in the Grouped By Field.
is: Select Operator Use operator to specify which data is to be used for the condition
Approval Level: Sets the  approval level to use to calculate condition on report

Set Cell Format To:

Set Foreground Color to: Displays selected color of cell foreground if condition is met

Set Background Color to: Displays selected color of cell background if condition is met

Set Bold: Bolds data in cell if condition is met
 
Set Italic: Italicizes data in cell if condition is met

Hide Row: Hides the entire row if condition is met. 

Cell Will Be Display Like: Shows what cell will display if condition is met

BUTTONS:

Applies changes and closes form

Closes form

Creates a new condition. Conditions are tested in order and once a condition is met the cell if formatted and no other conditions are processed.  For example, if you want the cell to be yellow if it is greater than 10, and red if greater than 20 the first action (tab) should check for greater than 20, second tab should check for greater than 10.

Deletes the current Action for the conditional formatting.

Removes all condtional formatting from the cells.

EXAMPLES:

EXAMPLE 1. Set all Influent Flows that are greater than the Entry Max Limit to Bold, yellow on red:

1. Locate Influent Flow in spread design using Locate, Daily Values. Placing date beside data and heading info optional.

2. Select cell range to format then use Format, Conditional Formatting to set condition for data (Entry Max = 13 in this example)

Data that meets condition (Data greater than Entry Limit of 13 for Influent Flow) will now be dispayed as follows:

 

Example 2: Set all Influent Flows that is greater than the Influent Flow Monthly Average Limit to Bold, yellow on red:

1. Highlight cell range and set limit to CELL($D$5).  The CELL(XX) command allows you to compare the current cell to a value in a different cell.  Use the $ to refer to the cell with absolute referencing, i.e. CELL($D$5) will refer to cell D5 for the entire cell range.  CELL(C3) will refer to cell C3, then C4, etc...

 

Example 3: Using IS BETWEEN

When using Is Between, Is not Between, etc... you specify the range with a dash between the values:

If referring to Cells use Cell(B2)-Cell(B3).  Is Between is Inclusive meaning that a value of 3 is considered to be between 3 and 6.  Examples

 

Example 4: Setting format based on a different cells value. 

Set a cell to yellow if the data has been edited.  We use the VDE function in column D to see if the data is edited.  Then in column E, if VDE function returns a 1 (meaning the data has been edited) we set that cell equal to whatever value is in column B.  So if, V1001 has edited on the 3rd we set E8=C8.  Then we set the conditional format for cell C8:

Hint:  You can hide columns D and E. 

Example 5: Only show days with Flows greater than 3 using Hide Row:

NOTES:

The following tables explain how comparisons are performed.

Cell Value Limit Is Greater (>) Is Equal (=) Is Less Than (<) Contains
3.14159 (shown to 2 decimals) 3.14          False        True                False     False
Apple B False False True False
Apple APPLE False True False True

Cell Value Limit Is Greater (>) Is Equal (=) Is Less Than (<)
<2 (will be treated as 2) 2      False        True                False    
>100 (will be treated as 100) 100 False True False
ND (will be treated as 0) 2 False False True

Cell Value Is Detected Is Not Detected Is Empty
45 True False False
<2 False True False
0 True False False
>100 True False False
"" - Blank cell False False True
ND False True False

 

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 5/8/2008 1:40 PM.
Last Modified on 3/14/2019 8:54 AM.
Last Modified by Scott Dorner.
Article has been viewed 3567 times.
Rated 8 out of 10 based on 2 votes.
Print Article
Email Article