Chapter-6: Format all cells based on their values

Format all cells based on their values rule is the first rule in the New Rules option of conditional formatting. In this option excel cells are formatting on the base of their values as its name.

In this chapter you will learn 4 type of format style

2-Color Scale
3-Color Scale
Data Bars
Icon Sets

To apply “Format all cells based on their values” rule below are the steps

Select the range on which you want to apply the conditional formatting
As in below image select “B2:G9”

Data sets to apply the conditional formatting

Go to Home tab>>Conditional Formatting>>New Rules

New Rules Option in conditional Formatting — New Rules Option

New formatting rule window will be opened.
Select Format all cells based on their values option, however this is by default selected.

There are four option is available in format Style-

Table of Contents

1. 2-Color Scale:

2-Color Scale formatting style is default selected. to apply this formatting below are the steps.

You can take Number, Percent, Formula or Percentile in place of Lowest Value

We have selected Number here and putting value minimum 20 and maximum 200

Click on OK button to apply this conditional formatting.
Data set with this conditional formatting will look like below image.

Data set after applying conditional formatting

2. 3-Color Scale:

below are the steps to apply 3-Color Scale formatting style-

Select 3-Color Scale option in Format style
Choose the type here we are taking type as Number for Minimum, Midpoint and Maximum
Put values for Number for Minimum, Midpoint and Maximum

Click on OK button to apply this conditional formatting.
Data set with this conditional formatting will look like below image.

Data Set after applying the conditional formatting

3. Data Bar:

below are the steps to apply Data bar formatting style-

Select Data Bar option in Format style
Select a option from type drop down for Minimum and Maximum (here we have taken Number)
Put the value for Minimum and Maximum .
Select fill option Solid or Gradient Fill (here we have taken Gradient Fill )

Data bar Option in Conditional Formatting

Click on OK button to apply this conditional formatting.
Data set with this conditional formatting will look like below image.

We can display bars only (hide the numbers). Tick the “Show Bar Only” option

Click on OK button to apply this conditional formatting.
Data set with this conditional formatting will look like below image.

3. Icon Sets:

below are the steps to apply Icon Set formatting style-

Select Icon Set option in Format style
Select a Icon Style from the list.

Select type drop down, here we are select number.
Put the value, here we are we are taking Green light when cell value is equal to or greater than 150, Yellow light when cell value is less than 150 and equal to or greater than 100 and Red light when cell value is less than 100.

Click on OK button to apply this conditional formatting.
Data set with this conditional formatting will look like below image.

Data after applying traffic light conditional formatting

We also can show Icon only. We can change the Icon for any condition.