7 Conditional Formatting Highlight Cells Rules
Conditional Formatting Introduction
Conditional formatting is very useful function for Highlighting cells with only given condition.Using this function analysis data very simple way in visual basis.
Change Selected cells format with value result. Multiple rules available.Learn Details.
7 Different mostly use Highlight Cells Rules
1) Greater Than
2) Less Than
3) Between
4) Equal to
5) Text that contain
6) A date Accruing
7) Duplicate Value
1) Highlight Cells rules Greater Than
Greater than function use for from the selected data highlight greater than value
In the Screenshot AMP Services sales Report showing Sr, Date, Party Name & Amount. We are finding amount that sales amount (Column E) is Greater than 500000.
Highlight Cell vale > 500000.
Fisrt Select your require Data. Here Amount Available range is (E4:E21).
Go to Home Tab >> Click on Conditonal Formatting >> Click on Highlight Cells Rules >> Click on Greater Then.
New Window shown.
Enter Value. & Select cells format.
You can get value from clicking on this Icon
After Select cell Click icon again.
Some format is available here.Clicking on custom format you can make your own format.
You can change format Number , Font , Border, Fill, Color.
These format applicable only as per cells rules.
Then Click ok
You will get result according rules.
Cells highlight with applying format Above value 500000.
For For Remove Rules.
Select data
Home >> Conditional Formatting >> Clear Rules >> Clear Rules from selected Cells or Entire Sheet.
2) Highlight Cells rules Less Than
Highlighting cell Less than Value.
Here we are highlight cell that value is less than 300000.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Less Than.
Enter Value. Select format . Click ok.
3) Highlight Cells rules Between 2 Value
Highlighting cell Between two Values.
Here we are highlight cell that value Between 500000 to 700000.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Beween.
Enter first small Value then second. Select format . Click ok.
4) Highlight Cells rules Equal to
Highlighting cell Equal to.
Here we are highlight cell that value equal to 415306.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Equal to..
Enter Value. Select format . Click ok.
5) Highlight Cells rules Text that contains …
Highlighting cell Text that contains.
Here we are highlight cell that Text is MNC Technology.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Text that Contains..
Type Your text or Select Cell which text is available. Select format . Click ok.
In Every Rule you can Enter value or text manually or select cell.
You can type only some text. Highlight cells that contains text match with few character.
Here Entered only Ltd. Result shown All Cells that contains Ltd Text.
6) Highlight Cells rules Date Occurring…
Highlighting cell Date Occurring.
Here we are highlight cell that entered Date today means computer Date.
Select column that contains Date.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Date Occuring..
Select today form drop down list. Select format . Click ok.
We will highlight using more option.
Yesterday
Today
in the last 7 day
Last week
This week
Next week
Next month
This Month
Last month.
Can use multiple rules in one column or selected data one by one.
7) Highlight Cells rules Duplicate Values…
Highlighting cell Duplicate Value.
Here we are highlight cell that entered Text 2 or More time.
Select Data.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Duplicate Value.
Click ok.
Highlight Cells rules Unique Value…
Highlighting cell Unique Value.
Here we are highlight All cell that entered Text 1 time.
Select Data.
Go to Home >> Conditional Formatting >> Highlighting cells rules >> Duplicate Value.
From the drop down menu select unique value.
Click ok.
