Scribd
Upload
30 views6 pages

Excel Conditional Formatting Guide

The document discusses different types of conditional formatting rules that can be applied to cells in Excel, including highlighting cells based on logical tests or comparative operators, ranking values with data bars or color scales, and identifying top or bottom ranked values. Examples are given of applying conditional formatting rules to cells containing sales data to highlight above or below average values, values over a certain threshold, or the top 3 values.

Uploaded by

Akshayaa Prakash
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
30 views6 pages

Excel Conditional Formatting Guide

The document discusses different types of conditional formatting rules that can be applied to cells in Excel, including highlighting cells based on logical tests or comparative operators, ranking values with data bars or color scales, and identifying top or bottom ranked values. Examples are given of applying conditional formatting rules to cells containing sales data to highlight above or below average values, values over a certain threshold, or the top 3 values.

Uploaded by

Akshayaa Prakash
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

Conditional Formatting to cells: A logical test that comes out TRUE or FALSE

TRUE = the cell gets the formatting


FALSE = the cell does NOT get the formatting
Highlight Cell Rules for different data types and comparative operators

Greater than 1,500.00


a 1000 Contains FUN

Date Product Net Sales Cost Date Product


1/1/2013 Quad $1,232.44 $591.58 1/1/2023 Quad
1/2/2013 V-Rang $1,000.00 $212.08 1/8/2023 V-Rang
1/2/2013 Carlota $357.50 $193.06 1/15/2023 Carlota
1/2/2013 Darnell $1,957.60 $1,037.55 1/22/2023 Darnell
1/3/2013 Quad $561.68 $303.31 1/29/2023 Quad
1/3/2013 Fun Fly $1,980.00 $990.00 2/5/2023 Fun Fly
1/4/2013 Quad $2,772.00 $1,469.16 2/12/2023 Quad
1/5/2013 Quad $4,536.00 $2,358.72 2/19/2023 Quad
1/5/2013 Bellen $503.10 $251.56 2/26/2023 Bellen
1/5/2013 Fun Rang $3,938.40 $1,890.40 3/5/2023 Fun Rang
1/5/2013 Bellen $464.96 $237.12 3/12/2023 Bellen

Top Bottom Rules for numbers


Top 3

Date Product
1/1/2013 Quad
1/2/2013 V-Rang
1/2/2013 Carlota
1/2/2013 Darnell
1/3/2013 Quad
1/3/2013 Fun Fly
1/4/2013 Quad
1/5/2013 Quad
1/5/2013 Bellen
1/5/2013 Fun Rang
1/5/2013 Bellen

Data Bars = Ranks the numbers with length of bar


Data Bar on top of numbers
Data bars in cell next to numbers

Grades Grades Gradient Grad Solid Grad


19 95 95 95
76 76 76 76
23 23 23 23
92 92 92 92
73 73 73 73
92 92 92 92
36 36 36 36
59 59 59 59
81 81 81 81
98 100 100 100

Color scales = Ranks number by color.

Grades Grades Grades


19 19 0
76 76 10
23 23 20
92 92 30
73 73 40
92 92 50
36 36 60
59 59 70
81 81 80
100 100 90

Icons = divides the numbers into 3, 4, or 5 groups (Top, middle, bottom)

Grades Grades Category Less Than 40 Red Arrow


19 19 19 Between 40 to 80 Orange Arrow
76 23 23 Above 80 Green Arrow
23 36 40
92 59 59
73 73 73
92 76 76
36 81 81
59 92 92
81 92 92
98 98 98
TRUE or FALSE.

2 Cell Based Formatting


Less Than 1,500.00 HardCode Formatting

Net Sales Cost


$1,232.44 $591.58
$1,000.00 $212.08
$357.50 $193.06
$1,957.60 $1,037.55
$561.68 $303.31
$1,980.00 $990.00
$2,772.00 $1,469.16
$4,536.00 $2,358.72
$503.10 $251.56
$3,938.40 $1,890.40
$464.96 $237.12

Above Average

Net Sales Cost Average


$1,232.44 $591.58 $1,754.88
$1,000.00 $212.08
$357.50 $193.06
$1,957.60 $1,037.55
$561.68 $303.31
$1,980.00 $990.00
$2,772.00 $1,469.16
$4,536.00 $2,358.72
$503.10 $251.56
$3,938.40 $1,890.40
$464.96 $237.12
Conditional Formatting to cells: A logical test that comes out TRUE or FALSE
TRUE = the cell gets the formatting
FALSE = the cell does NOT get the formatting
Highlight Cell Rules for different data types and comparative operators

Greater than 3,500.00


a 1000 Contains a

Date Product Net Sales Cost Date Product


1/1/2013 Quad $1,232.44 $591.58 1/1/2023 Quad
1/2/2013 V-Rang $1,000.00 $212.08 1/8/2023 V-Rang
1/2/2013 Carlota $357.50 $193.06 1/15/2023 Carlota
1/2/2013 Darnell $1,957.60 $1,037.55 1/22/2023 Darnell
1/3/2013 Quad $561.68 $303.31 1/29/2023 Quad
1/3/2013 Fun Fly $1,980.00 $990.00 2/5/2023 Fun Fly
1/4/2013 Quad $2,772.00 $1,469.16 2/12/2023 Quad
1/5/2013 Quad $4,536.00 $2,358.72 2/19/2023 Quad
1/5/2013 Bellen $503.10 $251.56 2/26/2023 Bellen
1/5/2013 Fun Rang $3,938.40 $1,890.40 3/5/2023 Fun Rang
1/5/2013 Bellen $464.96 $237.12 3/12/2023 Bellen

Top Bottom Rules for numbers


Top 3

Date Product
1/1/2013 Quad
1/2/2013 V-Rang
1/2/2013 Carlota
1/2/2013 Darnell
1/3/2013 Quad
1/3/2013 Fun Fly
1/4/2013 Quad
1/5/2013 Quad
1/5/2013 Bellen
1/5/2013 Fun Rang
1/5/2013 Bellen

Data Bars = Ranks the numbers with length of bar


Data Bar on top of numbers
Data bars in cell next to numbers

Grades Grades Gradient Grad Solid Grad


19 95
76 76
23 23
92 92
73 73
92 92
36 36
59 59
81 81
98 100

Color scales = Ranks number by color.

Grades Grades Grades


19 19 10
76 76 20
23 23 30
92 92 40
73 73 50
92 92 60
36 36 70
59 59 80
81 81 90
100 100 100

Icons = divides the numbers into 3, 4, or 5 groups (Top, middle, bottom)

Grades Grades Category Less Than 40 Red Arrow


19 19 Between 40 to 80 Orange Arrow
76 23 Above 80 Green Arrow
23 36
92 59
73 73
92 76
36 81
59 92
81 92
98 98
t TRUE or FALSE.

2 Cell Based Formatting


Less Than 3,500.00 HardCode Formatting

Net Sales Cost


$1,232.44 $591.58
$1,000.00 $212.08
$357.50 $193.06
$1,957.60 $1,037.55
$561.68 $303.31
$1,980.00 $990.00
$2,772.00 $1,469.16
$4,536.00 $2,358.72
$503.10 $251.56
$3,938.40 $1,890.40
$464.96 $237.12

Above Average

Net Sales Cost Average


$1,232.44 $591.58 $1,754.88
$1,000.00 $212.08
$357.50 $193.06
$1,957.60 $1,037.55
$561.68 $303.31
$1,980.00 $990.00
$2,772.00 $1,469.16
$4,536.00 $2,358.72
$503.10 $251.56
$3,938.40 $1,890.40
$464.96 $237.12

You might also like