Scribd
Upload
72 views23 pages

Types of Conditional Formatting in Excel

Conditional formatting allows you to apply formatting to cells based on certain conditions or formulas. There are several types of conditional formatting including highlighting cells, top/bottom rules, data bars, color scales, and icon sets. Conditional formatting can be used to easily identify trends and patterns in data by changing font colors, cell colors, and more based on the values or formulas in the cells.

Uploaded by

Rohit_MCTS
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
72 views23 pages

Types of Conditional Formatting in Excel

Conditional formatting allows you to apply formatting to cells based on certain conditions or formulas. There are several types of conditional formatting including highlighting cells, top/bottom rules, data bars, color scales, and icon sets. Conditional formatting can be used to easily identify trends and patterns in data by changing font colors, cell colors, and more based on the values or formulas in the cells.

Uploaded by

Rohit_MCTS
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

CON

Formating
Condition

Conditional Formating

GOD

G
O
D

Types of Conditional Formating

1). Basic Conditional Formating


2). Advanced Conditional Formating

1). Basic Condtional Formating


CONDITIONAL FORMATING

When we change size,color,font,border of cells


When we use specific condition

When we change size,color,font,border with condition is called conditional formating

Generator New Rule


Operator Manage Rule
Destroyer Clear Rule

ditional Formating

itional Formating
Conditional Formating

tional Formating

a). Readymade conditional Formating


b). Self Created(Customized) Conditional formating
Class 3
CONDIIONAL FORMATING

Conditional Formatting is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change de
or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100. It
Highlight Cell Rules
Date Name M1 M2
12/19/2018 Aman 60 58
12/20/2018 Suman 35 72
12/21/2018 Aman 40 79
12/22/2018 Anshika 2 2
12/23/2018 Anmol 48 81
12/24/2018 Bhashkar 40 2
12/25/2018 Sukh 70 61
12/26/2018 Sudeep 57 2

Top/Bottom Rules Name M1 M2


Aman 60 58
Suman 35 72
Aman 40 79
Anshika 2 2
Anmol 48 81
Bhashkar 40 2
Sukh 70 61
Sudeep 57 2

Highlight Top 3 Total ->Home-Conditional Formating->Top Bottom


then under Format cells that rank in the top -selct Top 3-With Cu
Data Bars Name M1 M2
Aman 60 58
Suman 35 72
Aman 40 79
Anshika 2 2
Anmol 48 81
Bhashkar 40 2
Sukh 70 61
Sudeep 57 2

Color Scale
Name M1 M2
Aman 60 58
Suman 35 72
Aman 40 79
Anshika 2 2
Anmol 48 81
Bhashkar 40 2
Sukh 70 61
Sudeep 57 2

Icon Sets Project Pct Completed


Project 1 95%
Project 2 100%
Project 3 50%
Project 4 0%
Project 5 20%
Project 6 80%
Project 7 100%
Project 8 0%
Project 9 0%
Project 10 50%

New Rules/Manage Rules


Conditional formating-New rules-use a formula to determine which cells to format

[Link] One column value

Top ProducQuantity Price Top Products


Mobile 2 33 mobile
camera 3 44
laptop 4 4 Step-Select the Top Product column data->Home>conditional formating-New ru
Mobile 5 3 Under Format values where this formula is true Type the Formula ( =$E$73=$A7
perfumes 3 22
camera 4 4
Mobile 5 4
camera 6 5

[Link] Entire Record

Top ProducQuantity Price Top Products


Mobile 2 33 mobile
camera 3 44
laptop 4 4 Step-Select the Top Product entire Row data->Home>conditional formating-New
Mobile 5 3 Under Format values where this formula is true Type the Formula ( =$E$85=$A8
perfumes 3 22
camera 4 4
Mobile 5 4
camera 6 5

[Link] column and row at same time

Products India Aus Canada Product Country


mobile 344343 565 2323 mobile canada
camera 43434 6564 3323
mobile 434343 454 22223 Step For Entire Row Highlight-Select the Value Data Range only-
watches 3434 45 2432 Under Format values where this formula is true Type the Formul
shoes 35343 4545 242323
laptop 343453 3433 24232 Step For Entire Column Highlight-Select the Value Data Range on
mobile 34345 4545 4232 Under Format values where this formula is true Type the Formul
clothes 2453 54545 23322

Invoice Highlight Net Sales based on invoice


659455

Invoice Date Company Product Net Sales Cost


659455 1/11/2013 Tri Fly Fun Fly $39.04 $19.52
659457 1/12/2013 Tri Fly Fun Fly $40.26 $21.33 Step -Select the Net Sales column->Home
659447 1/5/2013 Colorad B Aspen $48.48 $25.20 Under Format values where this formula
659456 1/12/2013 Tri Fly Fun Fly $51.24 $27.16
659447 1/12/2013 Colorad B Aspen $60.60 $28.50
659445 1/5/2013 Colorad B Yanaki $114.00 $55.86
659459 1/14/2013 Tri Fly Fun Fly $183.00 $95.20
659451 1/9/2013 Tri Fly V-Rang $203.04 $95.40
659447 1/11/2013 Gel BoomsQuad $200.60 $106.32
659458 1/13/2013 Tri Fly Fun Fly $207.36 $107.82
659455 1/11/2013 Colorad B Yanaki $219.06 $118.30
have that formatting change depending on the value of the cell
f the cell is greater than 100. It is used to easily spot the trends and patterns using colors,bars and icons
Data
M3 M4 Total Percentage Grade
27 55 200 50 PASS
70 66 243 60.75 PASS
84 60 263 65.75 PASS
2 90 96 24 FAIL
72 80 281 70.25 PASS
3 36 81 20.25 FAIL
61 41 233 58.25 PASS
2 2 63 15.75 FAIL

M3 M4 Total Percentage
27 55 200 50
70 66 243 60.75
84 60 263 65.75 Grade
2 90 96 24 PASS
72 80 281 70.25 PASS
3 36 81 20.25 PASS ------> Home-ConditionalFormating-Highlight Cell Rule
61 41 233 58.25 FAIL Repeat the same process for FAIL.
2 2 63 15.75 PASS
FAIL
nditional Formating->Top Bottom Rules->Top 10 items PASS More Rules
in the top -selct Top 3-With Custome Format select color-ok FAIL Comparative Chart
M3 M4 Total Percentage Mahesh Sales Dinesh
27 55 200 50 5435 January 1212
70 66 243 60.75 1232 February 4323
84 60 263 65.75 3323 March 5345
2 90 96 24 7565 April 6464
72 80 281 70.25 3212 May 7676
3 36 81 20.25 8756 June 8987
61 41 233 58.25 5465 July 7867
2 2 63 15.75 3432 August 9089
4344 September 8767
6565 October 4322
3443 November 1111
M3 M4 Total 5455 December 3222
27 55 200
70 66 243
84 60 263
2 90 96
72 80 281
3 36 81
61 41 233
2 2 63

Showing only one icon for values >=80


95%
100% 57 66 48 70
50% 11 40 44 48
0% 35 98 73 47
20% 95 4 98 89
80% 58 78 49 61
100% 20 94 93 31
0% 55 56 34 12
0% 1 61 93 82
50% 62 87 45 38
11 81 75 95
3 32 10 23
52 90 5 22
72 24 13 33

>conditional formating-New rule->Use a Formula to determine which cells to format


ype the Formula ( =$E$73=$A73) Then Format -Select Color-OK

ome>conditional formating-New rule->Use a Formula to determine which cells to format


ype the Formula ( =$E$85=$A85) Then Format -Select Color-OK
ect the Value Data Range only->Home>conditional formating-New rule->Use a Formula to determine which cells to format
ormula is true Type the Formula ( =$F$97=$A97) Then Format -Select Color-OK

-Select the Value Data Range only->Home>conditional formating-New rule->Use a Formula to determine which cells to format
ormula is true Type the Formula ( =$G$97=B$96) Then Format -Select Color-OK

ct the Net Sales column->Home>conditional formating-New rule->Use a Formula to determine which cells to format
rmat values where this formula is true Type the Formula ( =$A$108=$A111) Then Format -Select Color-OK
rmating-Highlight Cell Rules-text That contains Then under format cells that contain the text(PASS)-With(Custom Format)-Selct the color-O
ocess for FAIL.
ls to format

cells to format
stom Format)-Selct the color-OK
[Link] One column value

Top Products Quantity Price Top Products


Mobile 2 33 Camera
camera 3 44
laptop 4 4
Mobile 5 3
perfumes 3 22
camera 4 4
Mobile 5 4
camera 6 5

[Link] Entire Record

Top Products Quantity Price Top Products


Mobile 2 33 mobile
camera 3 44
laptop 4 4
Mobile 5 3
perfumes 3 22
camera 4 4
Mobile 5 4
camera 6 5

[Link] column and row at same time

Products India Aus Canada Product Country


mobile 344343 565 2323 mobile canada
camera 43434 6564 3323
mobile 434343 454 22223
watches 3434 45 2432
shoes 35343 4545 242323
laptop 343453 3433 24232
mobile 34345 4545 4232
clothes 2453 54545 23322
1 2 F9 to change colors
Press F9 To Change the Colour
Qtr-1 2,145 Conditional formatting:
Qtr-2 22 The Total appears only when all four inputs are entered.
Qtr-3 2,987
Qtr-4 3,021
Total: 8,175
Highlighted Cell Rules

>,<,>=,<=,=,<> Text that Contains


299 Well-Suited Thermostat Inc.
502 Special RAM Marble Corporation
548 Magnificent Bobsled Inc.
207 Functional Washer Inc.
942 Tremendous Aerobic Inc. ram
659 Special Marble Corporation
548 Special Marble Corporation
638 Effortless Thermostat Company RAM
196 Effortless Thermostat Company
498 Rare Washer Corporation
705 Alluring Bottle Company
163 Special Marble Corporation
306 Fabulous Quilt Company
77 Effortless Thermostat Company
653 RAM Alluring Bottle Company
801 Fabulous Tripod Corporation
368 Functional Washer Inc.
176 Magnificent Bobsled Inc.
475 Effortless Thermostat Company
527 Fabulous Luggage Traders
hted Cell Rules TOP/BOTTOM RULES

Date occurance Duplicate/Unique Top/Bottom Amount


4/1/2019 Chandan 44 1778
4/2/2019 mohit 28 7207
4/3/2019 Raju 34 2023 5553.733
4/4/2019 Chandan 31 8637
4/5/2019 sanjeev 87 7319
4/6/2019 Puneet 46 9420
4/7/2019 Manjeet 84 6634
4/8/2019 mohit 55 5024
4/9/2019 RAM 51 1933
4/10/2019 ram 26 7831
4/11/2019 50 5276
4/12/2019 34 6347
4/13/2019 57 4054
4/14/2019 41 3726
4/15/2019 33 6097
4/16/2019
4/17/2019
4/18/2019
4/19/2019
4/20/2019
4/21/2019
4/22/2019
4/23/2019
4/24/2019
4/25/2019
4/26/2019
4/27/2019
4/28/2019
4/29/2019
4/30/2019
5/1/2019
5/2/2019
5/3/2019
5/4/2019
5/5/2019
5/6/2019
5/7/2019
5/8/2019
5/9/2019
5/10/2019
5/11/2019
5/12/2019
5/13/2019
5/14/2019
5/15/2019
5/16/2019
5/17/2019
5/18/2019
5/19/2019
5/20/2019
5/21/2019
5/22/2019
5/23/2019
5/24/2019
5/25/2019
5/26/2019
5/27/2019
5/28/2019
5/29/2019
5/30/2019
5/31/2019
6/1/2019
6/2/2019
6/3/2019
6/4/2019
6/5/2019
6/6/2019
6/7/2019
6/8/2019
6/9/2019
6/10/2019
6/11/2019
6/12/2019
6/13/2019
6/14/2019
6/15/2019
6/16/2019
6/17/2019
6/18/2019
6/19/2019
6/20/2019
6/21/2019
6/22/2019
6/23/2019
6/24/2019
6/25/2019
6/26/2019
6/27/2019
6/28/2019
6/29/2019
6/30/2019
Data Bars Color Scales
Date Productivity Trend Bar Amount
5/9/2019 988 1
5/12/2019 940 4555
5/11/2019 636 7115
5/15/2019 603 5957
5/8/2019 497 2854
5/14/2019 380 8745
5/10/2019 368 10000
5/16/2019 331 2000
5/13/2019 297 3181
5/17/2019 213 1220
4331
4119
9726
3042
2617
7133
8564
9062
207
5479
2248
2730
814
1919
4418
3087
8102
2000
9534
3239
10000
6508
314
829
1
705
Set Icons
Date Productivity
5/9/2019 988
5/12/2019 940
5/11/2019 636
5/15/2019 603
5/8/2019 497
5/14/2019 380
5/10/2019 368
5/16/2019 331
5/13/2019 297
5/17/2019 213

You might also like