Using Conditional formatting to highlight information

  • 21 December 2021
  • 0 replies
  • 198 views

Userlevel 3
Badge

 

Conditional formatting helps you visually explore and analyze data, detect critical issues, and identify patterns and trends. It makes it easier to highlight interesting cells or a range of cells, emphasize unusual values, and visualize data by using color scales.

A conditional format changes the appearance of cells (background color, font color, font style, borders, prefix and suffix) on the basis of conditions that you specify. If the conditions are true, the cell range is formatted; if the conditions are false, the cell range is not formatted. There are many built-in conditions, and you can also create your own (including by using a formula that evaluates True or False).

 

Click on the image to expand the view 

 

 

Applying a Rule

 

  1. Right click on the column or row you want to highlight
  2. Select Conditional Formatting
  3. Select the rule you want to apply
  4. Configure the rule parameters, like the value triggering the condition and the style you want to apply
  5. Activate the toggle All if you want to apply the rule on all the columns or rows with the same modality
  6. Finish by clicking  Done

 

 

Conditional Formating Rules

 

Option Definition
Empty Format cells that are empty
Not empty Format cells that are not empty
Lower than Format cells that are lower than a <number>
Lower than or equal to Format cells that are lower than or equal to a <number>
Greater than Format cells that are greater than a <number>
Greater than or equal to Format cells that are greater than or equal to a <number>
Equal to Format cells that are equal to a <number>
Not equal to Format cells that are not equal to a <number>
Between Format cells that are between a <number1> and <number2>
Not between Format cells that are not between a <number1> and <number2>
Color scale Format cells by using a gradation of two colors with <number1> as min and <number2> as max. You can choose to add a midpoint <number3> to use a three color gradation. The shade of the color represents higher or lower values.

 

 

Managing or Editing the order on which Conditional formatting rules apply

 

The order in which conditional formatting rules are evaluated also reflects their relative importance: the higher a rule is on the list of conditional formatting rules, the more important it is.

This means that in cases where two conditional formatting rules conflict with each other, the rule that is higher on the list is applied and the rule that is lower on the list is not applied.

To reorder the rules, use the Format panel > Tab Conditional 

Click on the image to expand the view

 

Editing Conditional Formatting Rules

 

Open the Format Panel and under the Conditional tab, select the rule you want to edit.

 

Click on the image to expand the view

 

Clearing Conditional Formatting Rules

 

Use the Conditional Format Panel to delete the unwanted rules. Click on the bin that appears next to the rule to clear it.

 

Click on the image to expand the view 

 

 


This topic has been closed for comments