6) Conditional formatting

Conditional formatting

Conditional Formatting  allows us to apply formats to a cell or range of cells.
The formatting can be changed depending on the value of the cell or the value of a formula.
For example, If the value of the cell is greater than 1000 then highlight the cell with yellow colour.
There are five ways to do conditional formatting
1. Condition
2. Color scale
3. Icon set
4. Dates
5. Manage
How Conditional Formatting Works:
Conditional formatting in Calc automatically changes the appearance of selected cells. The automatic format can be the application of a cell style or of an indicator that visually presents data in much the same way as a graph or a chart.
To use Calc’s conditional formatting, select the cells to work with, then click Format | Conditional Formatting | Condition, and select an item from the sub-menu. All types of conditional formatting can be configured from Condition. The Color Scale and Data Bar menu items are short-cuts to options available under Condition. If necessary, you can edit the range of cells affected at the bottom of the Conditions window (Figure 1).

          _      _          Figure 1: The Conditional Formatting window sets the kind of information to be indicated by the formatting.
The Conditions window has three fields. From left to right, they are:
The general condition.
The filter to refine the condition.
The numerical value that must be present to activate the conditional formatting.
Below these three fields, you have the option to select a cell style.
By changing the general condition, you can choose the following types of conditional formatting:
Cell value is: Applies the selected cell style when the numerical value is met. This type is useful for emphasizing target values in a range of cells. It cannot be used for cells formatted for text.
Formula is: Applies the selected cell style to cells in which the designated formula is used. The formula is typically one that viewers of the sheet want to find easily.
Date is: Applies the selected cell style to cells in which the designated filter is used, from Today to Last Week. This type is especially useful for locating recent information.
All Cells | Color Scale (2 entries): Creates a gradient of two colors. The fields refer not to formulas, but to target values. The color scale is especially useful for showing high and low values in a range of cells at a quick glance (Figure 2).
                         
Figure 2: A color scale, with yellow indicating the lowest value and cyan the highest. Mid-range values are different shades of green.
All Cells | Color Scale (3 entries): Like a color scale for two entries, except that a third target value is added, often a midpoint using the value Percent.
All Cells | Data Bar: A gradient that creates a graph-like representation, typically showing how far a cell value is above or below a designated norm (Figure 3).
        _      _        
Figure 3: A data bar in which high values are indicated by more blue in a cell.
All Cells | Icon Set: Adds a set of icons to summarize the contents of cells (Figure 4). Available icons include arrows, flags, check marks, bar graphs, emoticons, and quartered circles. Each icon set has three or four icons, each depicting a different state. For example, traffic light icons or emoticons might designate if results were above, below, or equal to projections.
          _      _        
Figure 4: An icon set annotates results. Here, emoticons indicate that two results were neutral, and two results better than neutral.
Once you have set conditional formatting, you can select Manage from the sub-menu to see a summary of the instances of Conditional Formats and edit the selections (Figure 5). You might also want to add a caption to the formatted cells to explain what is being displayed.
          _      _     Figure 5: Click Manage in the Conditional sub-menu to Add, Edit, or Remove conditional formatting.


Examples of Conditional Formatting:
Example 1
Teacher want to highlight all number by applying a condition(for E.g. values are greater than 80 with PINK and rest in Blue).
Let’s take the spreadsheet given in Handout 1 and follow the given steps :
1. Select the range of data in which you want to check and then Goto Format menu -> Conditional formating ->Condition
OR
Goto Format menu -> Conditional formating ->Condition
A following dialog box will appear .


2. If you have selected the data earlier than check in Cell Range it will show you the entire

   range otherwise use mouse to select the range to apply conditional formating .
3. Take Color scale (2 entries ) and give values the both scales and select “All Cell” to
    check the apply the conditional formating in whole range.
4. Click on OK to see the result .


5. See the colour range from Pink to Blue . All marks above then 80 and below and equal
    to 100 are in purple color i.e. a combination of pink and blue.

Example 2

A gradient cell bar  that creates a graph-like representation, typically showing how far a cell value is above or below a designated norm (example data value from 50 to 100 in whole range).
To explain the above example , let’s take a spreadsheet given in Handout 1.
1. Select the range of data in which you want to check and then Goto Format menu -> Conditional formating ->Condition
OR
Goto Format menu -> Conditional formating ->Data Bar 
A following dialog box will appear .




2. Give values and choose more options a dialog box will appear . Give colour combinations of your choice .


3. Click on OK and then again Click on OK you will find the result . Values which are below condition(Example : value >50) are not considered .