Data Bars, Color Scales, and Icon Sets0%

Using Custom Formulas in Conditional Formatting

Data Bars, Color Scales, and Icon Sets

Beginner12 min read•Updated: Jul 11, 2026

Data Bars, Color Scales, and Icon Sets

Visual conditional formatting features—such as Data Bars, Color Scales, and Icon Sets—allow you to transform raw numbers into intuitive in-cell micro-visualizations. They help decision-makers spot trends, outliers, and progress distributions at a single glance without generating bulky standalone charts.

1. Data Bars (In-Cell Progress & Magnitude)

Data Bars fill the background of each cell with a horizontal bar proportional to the cell's numerical value relative to other cells in the range:

Gradient Fill vs. Solid Fill: Solid fills provide bold, modern high-contrast indicators, while gradient fills offer softer pastel styling.
Positive and Negative Values: Excel automatically places an axis at zero, drawing green bars to the right for positive gains and red bars to the left for negative losses.
Show Bar Only Option: In the rule settings, check Show Bar Only to hide the underlying digits, turning the column into a clean graphical indicator column!

2. Color Scales (Heatmaps)

Color Scales apply a continuous 2-color or 3-color gradient across values, transforming a plain table into an analytical heatmap:

Green - Yellow - Red (Higher is Better): Top revenues or performance scores receive deep green, median values show yellow, and critical low values turn red.
Red - Yellow - Green (Lower is Better): Ideal for error rates, customer wait times, production defects, or operating costs where smaller numbers represent superior performance.
Custom Color Stops: Under More Rules..., you can anchor minimum, midpoint, and maximum color stops to specific percentiles (e.g., 10th, 50th, 90th) to prevent extreme outliers from skewing the spectrum.

3. Icon Sets (KPI Status Symbols)

Icon Sets categorize data into 3, 4, or 5 discrete tiers using visual symbols:

Traffic Lights & Flags: Green circle for 'On Track', Yellow triangle for 'At Risk', Red diamond for 'Delayed'.
Directional Arrows: Upward green arrow, horizontal yellow arrow, downward red arrow (ideal for year-over-year revenue comparisons).
Rating Stars & Battery Meters: Visual indicators for survey scores and inventory reserves.

Configuring Custom Icon Thresholds:

1
Select the numeric range and click Conditional Formatting > Icon Sets > More Rules....
2
Change the threshold type from Percent to Number or Formula for business-specific targets.
3
For example:

Green checkmark when Value >= 100,000
Yellow exclamation when Value >= 50,000
Red cross when Value < 50,000

1
Check Show Icon Only if you want to display just the icon without the numeric score.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does checking the 'Show Bar Only' checkbox in a Data Bar conditional formatting rule accomplish?

Question 2

When tracking manufacturing defect rates or customer wait times, which Color Scale configuration is recommended?

Question 3

What is the default threshold type used by Excel when you apply an Icon Set to a range?

Question 4

How does Excel display negative numbers when a Data Bar rule is applied to a range containing both positive and negative values?

Question 5

Where do you go to change an Icon Set rule from Percent-based thresholds to specific numerical targets?

Next Lesson

Using Custom Formulas in Conditional Formatting

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Highlighting Cells Based on Rules	Using Custom Formulas in Conditional Formatting

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Using Custom Formulas in Conditional Formatting

Data Bars, Color Scales, and Icon Sets

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Data Bars, Color Scales, and Icon Sets

1. Data Bars (In-Cell Progress & Magnitude)

Data Bars fill the background of each cell with a horizontal bar proportional to the cell's numerical value relative to other cells in the range:

Gradient Fill vs. Solid Fill: Solid fills provide bold, modern high-contrast indicators, while gradient fills offer softer pastel styling.
Positive and Negative Values: Excel automatically places an axis at zero, drawing green bars to the right for positive gains and red bars to the left for negative losses.
Show Bar Only Option: In the rule settings, check Show Bar Only to hide the underlying digits, turning the column into a clean graphical indicator column!

2. Color Scales (Heatmaps)

Color Scales apply a continuous 2-color or 3-color gradient across values, transforming a plain table into an analytical heatmap:

Green - Yellow - Red (Higher is Better): Top revenues or performance scores receive deep green, median values show yellow, and critical low values turn red.
Red - Yellow - Green (Lower is Better): Ideal for error rates, customer wait times, production defects, or operating costs where smaller numbers represent superior performance.
Custom Color Stops: Under More Rules..., you can anchor minimum, midpoint, and maximum color stops to specific percentiles (e.g., 10th, 50th, 90th) to prevent extreme outliers from skewing the spectrum.

3. Icon Sets (KPI Status Symbols)

Icon Sets categorize data into 3, 4, or 5 discrete tiers using visual symbols:

Traffic Lights & Flags: Green circle for 'On Track', Yellow triangle for 'At Risk', Red diamond for 'Delayed'.
Directional Arrows: Upward green arrow, horizontal yellow arrow, downward red arrow (ideal for year-over-year revenue comparisons).
Rating Stars & Battery Meters: Visual indicators for survey scores and inventory reserves.

Configuring Custom Icon Thresholds:

1
Select the numeric range and click Conditional Formatting > Icon Sets > More Rules....
2
Change the threshold type from Percent to Number or Formula for business-specific targets.
3
For example:

Green checkmark when Value >= 100,000
Yellow exclamation when Value >= 50,000
Red cross when Value < 50,000

1
Check Show Icon Only if you want to display just the icon without the numeric score.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does checking the 'Show Bar Only' checkbox in a Data Bar conditional formatting rule accomplish?

Question 2

When tracking manufacturing defect rates or customer wait times, which Color Scale configuration is recommended?

Question 3

What is the default threshold type used by Excel when you apply an Icon Set to a range?

Question 4

How does Excel display negative numbers when a Data Bar rule is applied to a range containing both positive and negative values?

Question 5

Where do you go to change an Icon Set rule from Percent-based thresholds to specific numerical targets?

Next Lesson

Using Custom Formulas in Conditional Formatting

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Highlighting Cells Based on Rules	Using Custom Formulas in Conditional Formatting

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next