1.3-Data Structure and Conditional Formatting

2025-10-18 23:34

Status: Child

Tags: Excel data types data formatting

Conditional Formatting in Excel

Overview

Conditional Formatting is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell 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 is used to easily spot trends and patterns using colors, bars, and icons.

Types of Conditional Formatting

1. Highlight Cell Rules

Highlight Cell Rules allow you to format cells based on specific criteria or conditions.

Example: Student Grade Data

Formula Used for Total: $\text{Total}=M2+M3$

Formula Used for Percentage: $\text{Percentage}=\frac{\text{Total}}{\text{Maximum Marks}}\times 100$

Formula Used for Grade:

=IF(Percentage >= 33, "PASS", "FAIL")

Name	M2	M3	Total	Percentage	Grade
Aman	58	27	145	48	PASS
Suman	72	70	177	59	PASS
Aman	79	84	203	68	PASS
Anshika	2	2	6	2	FAIL
Anmol	81	72	201	67	PASS
Bhashkar	2	3	45	15	FAIL
Sukh	61	61	192	64	PASS
Sudeep	2	2	61	20	FAIL

Conditional Formatting Rule:

• Highlight cells where Grade = "PASS" with green fill
• Highlight cells where Grade = "FAIL" with red fill

2. Conditional Formatting with Formulas

Conditional formatting can be applied using custom formulas to create more complex conditions.

Example: Highlighting Salary Values

Format if salary is equal to or greater than: $80,000$

Method 1: Highlight Specific Values (≥ $80,000$)

Conditional Formatting Formula for Value Highlighting:

=C2>=80000

Where C2 is the cell containing the yearly salary value.

Formula Used for TRUE/FALSE Column:

=C2>=80000

Name	Yearly Salary	Condition
Gary Miller	80,000	TRUE
James Willard	39,627	TRUE
Richard Elliot	18,000	TRUE
Robert Spear	80,000	TRUE
Roger Mun	134,000	FALSE
Paul Garza	34,808	TRUE
Robert Marquez	134,468	FALSE
Natalie Porter	45,000	TRUE
Kim West	80,000	TRUE
Stevie Bridge	21,972	TRUE
Andre Cooper	81,000	FALSE
Crystal Doyle	185,000	FALSE
Robert Musser	80,000	TRUE
Daniel Garrett	140,000	FALSE
Ann Withers	100,000	FALSE

Method 2: Highlight Entire Rows (≥ $80,000$)

Conditional Formatting Formula for Row Highlighting:

=$C2>=80000

The $ symbol before the column letter makes it an absolute reference, ensuring the formula checks the same column (Yearly Salary) for each row.

Steps to Apply:

1. Select the entire data range (e.g., A2:C16)
2. Go to Home → Conditional Formatting → New Rule
3. Select “Use a formula to determine which cells to format”
4. Enter the formula: =$C2>=80000
5. Click Format and choose your desired formatting
6. Click OK

Name	Yearly Salary
Gary Miller	80,000
James Willard	39,627
Richard Elliot	18,000
Robert Spear	80,000
Roger Mun	134,000
Paul Garza	34,808
Robert Marquez	134,468
Natalie Porter	45,000
Kim West	80,000
Stevie Bridge	21,972
Andre Cooper	81,000
Crystal Doyle	185,000
Robert Musser	80,000
Daniel Garrett	140,000
Ann Withers	100,000

3. Top/Bottom Rules

Top/Bottom Rules allow you to highlight the highest or lowest values in a range.

How to Highlight Top 3 Values

Steps:

1. Go to Home → Conditional Formatting → Top/Bottom Rules → Top 10 Items
2. Under “Format cells that rank in the top”, select Top 3
3. Choose “With Custom Format” and select your desired color
4. Click OK

Excel automatically applies the formula:

=LARGE($E$2:$E$9, 3)

This identifies the 3rd largest value in the range and highlights all values greater than or equal to it.

Example: Top 3 Total Scores

Formula Used for Total: $\text{Total}=M2+M3+M4$

Formula Used for Percentage:

=Total/4

(Assuming maximum marks per subject is 100, total maximum = 400)

Name	M2	M3	M4	Total	Percentage
Aman	58	27	55	200	28%
Suman	72	70	66	243	27%
Aman	79	84	60	263	23%
Anshika	2	2	60	66	91%
Anmol	81	72	80	281	28%
Bhashkar	2	3	36	81	44%
Sukh	61	61	41	233	18%
Sudeep	2	2	2	63	3%

Top 3 values in Total column: 281, 263, 243

4. Data Bars

Data Bars provide a visual representation of values in cells, displaying horizontal bars proportional to the cell values.

No formula is required – Data Bars are applied directly through:

1. Select the range (e.g., F2:F9 for Percentage column)
2. Go to Home → Conditional Formatting → Data Bars
3. Choose a color scheme

How Data Bars Work:

• Minimum value in range = smallest bar
• Maximum value in range = largest bar
• All other values are proportionally scaled

Formula for calculating bar length (internal to Excel): $\text{Bar Length}=\frac{\text{Cell Value}-\text{Min Value}}{\text{Max Value}-\text{Min Value}}\times 100\%$

Example: Data Bars Applied to Percentages

Name	M2	M3	M4	Total	Percentage
Aman	58	27	55	200	50
Suman	72	70	66	243	60.75
Aman	79	84	60	263	65.75
Anshika	2	2	90	96	24
Anmol	81	72	80	281	70.25
Bhashkar	2	3	36	81	20.25
Sukh	61	61	41	233	58.25
Sudeep	2	2	2	63	15.75

5. Color Scales

Color Scales apply a gradient of colors to cells based on their values, making it easy to visualize the distribution of data.

No formula is required – Color Scales are applied directly through:

1. Select the range (e.g., E2:E9 for Total column)
2. Go to Home → Conditional Formatting → Color Scales
3. Choose a color scheme (e.g., Green-Yellow-Red)

How Color Scales Work:

• Lowest value → One color (e.g., Red)
• Midpoint value → Middle color (e.g., Yellow)
• Highest value → End color (e.g., Green)

Formula for color calculation (internal to Excel):

For a 3-color scale:

• If $\text{Value}\le \text{Midpoint}$: Interpolate between Color 1 and Color 2
• If $\text{Value}>\text{Midpoint}$: Interpolate between Color 2 and Color 3

$\text{Color Position}=\frac{\text{Cell Value}-\text{Min Value}}{\text{Max Value}-\text{Min Value}}$

Example: Color Scale Applied to Total Scores

Name	M2	M3	M4	Total
Aman	58	27	55	200
Suman	72	70	66	243
Aman	79	84	60	263
Anshika	2	2	90	96
Anmol	81	72	80	281
Bhashkar	2	3	36	81
Sukh	61	61	41	233
Sudeep	2	2	2	63

Color Distribution:

• Lowest (63) → Red
• Middle (~172) → Yellow
• Highest (281) → Green

6. Date Occurring

Date-based conditional formatting allows you to highlight cells based on date criteria (e.g., today, yesterday, this week, last month).

Common Date Formulas for Conditional Formatting

Highlight Today’s Date:

=A2=TODAY()

Highlight Yesterday:

=A2=TODAY()-1

Highlight This Week:

=AND(A2>=TODAY()-WEEKDAY(TODAY(),2)+1, A2<=TODAY()-WEEKDAY(TODAY(),2)+7)

Highlight This Month:

=AND(MONTH(A2)=MONTH(TODAY()), YEAR(A2)=YEAR(TODAY()))

Highlight Last Month:

=AND(MONTH(A2)=MONTH(TODAY())-1, YEAR(A2)=YEAR(TODAY()))

Highlight Dates in the Past:

=A2<TODAY()

Highlight Dates in the Future:

=A2>TODAY()

Example: Date-Based Data

Formula Used for Total: $\text{Total}=M1+M2+M3+M4$

Conditional Formatting Applied: Highlight rows where Date = “1/May/25”

Formula:

=$A2=DATE(2025,5,1)

Date	M1	M2	M3	M4	Total
1/May/25	60	58	27	55	200
1/May/25	35	72	70	66	243
1/May/25	40	79	84	60	263
7/May/25	2	2	2	90	96
9/May/25	48	81	72	80	281
10/May/25	40	2	3	36	81
1/May/25	70	61	61	41	233
7/May/25	57	2	2	2	63

Common Conditional Formatting Formulas Reference

Comparison Formulas

Condition	Formula	Description
Equal to	=A1=100	Highlights if value equals 100
Not equal to	=A1<>100	Highlights if value doesn’t equal 100
Greater than	=A1>100	Highlights if value is greater than 100
Less than	=A1<100	Highlights if value is less than 100
Greater than or equal	=A1>=100	Highlights if value is ≥ 100
Less than or equal	=A1<=100	Highlights if value is ≤ 100
Between	=AND(A1>=50, A1<=100)	Highlights if value is between 50 and 100

Text-Based Formulas

Condition	Formula	Description
Contains text	=ISNUMBER(SEARCH("text",A1))	Highlights if cell contains “text”
Starts with	=LEFT(A1,3)="ABC"	Highlights if cell starts with “ABC”
Ends with	=RIGHT(A1,3)="XYZ"	Highlights if cell ends with “XYZ”
Blank cells	=ISBLANK(A1)	Highlights empty cells
Non-blank cells	=NOT(ISBLANK(A1))	Highlights non-empty cells

Advanced Formulas

Condition	Formula	Description
Duplicate values	=COUNTIF($A$2:$A$100,A2)>1	Highlights duplicates in range
Unique values	=COUNTIF($A$2:$A$100,A2)=1	Highlights unique values
Above average	=A1>AVERAGE($A$2:$A$100)	Highlights values above average
Top N values	=A1>=LARGE($A$2:$A$100,5)	Highlights top 5 values
Bottom N values	=A1<=SMALL($A$2:$A$100,5)	Highlights bottom 5 values

Summary

Conditional Formatting is a powerful feature in Excel that enhances data visualization and analysis by:

• Automatically highlighting important values using formulas
• Making trends and patterns immediately visible
• Creating visual indicators using colors, bars, and icons
• Enabling quick identification of outliers and key data points
• Supporting both simple rules and complex formula-based conditions

Key Formula Syntax Rules:

• Use = to start all conditional formatting formulas
• Use $ for absolute references when highlighting entire rows
• Combine functions like AND(), OR(), IF() for complex conditions
• Reference the first cell in your selection range, Excel will auto-adjust for other cells

By mastering these conditional formatting formulas, you can create more intuitive and informative spreadsheets that communicate data insights effectively.

References