Connect with us


How to create excel simple conditional formatting



how to create simple conditional formatting.

Conditional Formatting provides a way of viewing data and making it easier to understand worksheets. Conditional formatting enables you to automatically apply to format to one or more cells based on cell value, such as colors, icons, and data bar.

Specifying Conditional Formatting

To apply a conditional formatting rule to a cell or range, select the cells and then use one of the commands on the Home Tab ⇒ Style group ⇒ Conditional forming drop-d0wn t0 specify a rule. The choices are: :

  • Highlight cell rules: Example rules include highlighting cells that are greater than a particular value, between two values, contain or duplicate specific text strings.
  • Top bottom rules: Examples include highlighting the top 10 items, percent items, and items above average.
  • Data bars: Applies a graph bar directly to the cell, proportional to the value of the cell.
  • Color Scale: Applies background color, proportional to the cell value.
  • Icon sets: Displays icon directly in the cells. the icon depends on the cell’s value.


When you apply the rules for conditional formatting, Excel displays a dialog box that is specific to that rule. These dialog boxes have one thing in common: a drop-down list with suggestions for common formatting.

The figure shows the dialog box that appears when you choose Home Tab ⇒ Styles ⇒ Conditional Formatting ⇒ Highlight Cells Rules ⇒ Between.

The figure below shows a worksheet with nine ranges, each with different types of conditional formatting rule applied. Here’s a brief explanation of each.

  1. Greater than 10: Values greater than 10 are highlighted with a different background color. This rule is just one of the many numeric value related rules that you can apply.
  2. Above Average: It highlights values that are higher than the average value.
  3. Duplicate Values: Values are highlighted that appear more than once.
  4. Words that contain X: If the cell contains X (upper or lower case), it will highlight the cell.
  5. Data Bars: Each cell shows a horizontal bar proportional to its value.
  6. Color Scale: The color of the background varies depending on the cells ‘ value. You can choose from multiple color scales or create your own.
  7. Icon Set: One of the many sets of icons. It displays in the cell a small graphic. The graphics vary depending on the cell value.

Create and Manage Rules

It is basically useful if we want to apply to format in a different location and the conditions are in different locations. But sometimes the built-in formatting rules don’t go far enough. Adding your own formula to a conditional formatting rule gives you a power boost to help you do things the built-in rules can’t do. It’s basically useful if we want to apply to format in a different location and the conditions are in different locations. For example, if we want to highlight the name of the salesman if the sales amount is more than 50,000. Manage rule is used to edit any existing rules.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *