Apply and Remove Conditional Formatting

Microsoft Excel Microsoft 365 Apps

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the MO-210 Exam Performance-Based Exam Strategy

2 Import Data & Navigate Workbooks

Import Data from Text Files Import Data from Online Sources Search and Navigate to Named Cells, Ranges, Elements Insert and Remove Hyperlinks

3 Format Worksheets, Views & Collaboration

Page Setup, Headers, Footers, Row Height & Column Width Quick Access Toolbar, Views, Freeze Panes, Properties, Display Formulas Print Area, Export Formats, Print Settings, Inspect, Comments & Notes

4 Manipulate Data in Worksheets

Paste Special and Auto Fill Insert and Delete Rows, Columns, and Cells Generate Numeric Data with RANDBETWEEN and SEQUENCE

5 Format Cells and Ranges

Merge, Alignment, Orientation, Indentation, Wrap Text Number Formats, Format Painter, Format Cells Dialog Cell Styles, Clear Formatting, Group Worksheets

6 Named Ranges & Visual Summaries

Define and Reference Named Ranges Insert and Manage Sparklines Apply and Remove Conditional Formatting

7 Create and Format Tables

Create Excel Tables from Cell Ranges Apply Table Styles and Convert Tables to Ranges Add or Remove Table Rows and Columns

8 Table Options, Filter & Sort

Configure Table Style Options Insert and Configure Total Rows Filter Table Records Sort Data by Multiple Columns

9 Formula References

Relative, Absolute, and Mixed References Structured References in Tables

10 Calculate and Transform Data

SUM, AVERAGE, MIN, and MAX COUNT, COUNTA, and COUNTBLANK Conditional Operations with IF SORT and UNIQUE Functions

11 Format and Modify Text

LEFT, RIGHT, and MID UPPER, LOWER, LEN, CONCAT, and TEXTJOIN

12 Create Charts

Create Charts from Data Create Chart Sheets

13 Modify and Format Charts

Add Data Series and Switch Rows/Columns Add and Modify Chart Elements Chart Layouts, Styles, and Alternative Text

Apply and Remove Conditional Formatting

Key Takeaways Explain This Topic AI

Apply built-in rules from Home → Conditional Formatting: Highlight Cells, Top/Bottom, Data Bars, Color Scales, and Icon Sets

Select the target range first, then choose the rule so formatting lands only where the task specifies

Data bars, color scales, and icon sets visualize relative values inside the selected cells without writing formulas

Manage Rules reviews, edits, and prioritizes rules; Clear Rules removes formatting from a selection or the whole sheet

Removing conditional formatting does not delete cell values—only the rule-driven formatting
Last updated: August 2026

Why conditional formatting matters on MO-210

Domain 2 asks you to apply built-in conditional formatting and remove conditional formatting . Built-in means the gallery rules on Home → Conditional Formatting —Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets—not custom formula rules (those appear more often at Expert level). Projects use conditional formatting to call out late dates, top performers, or relative size at a glance beside sparklines and named totals.

Descriptors sound like "Apply a red fill to cells in D2:D40 that are less than 0" , "Add Data Bars to the values in Guaranteed" , "Apply the Green-Yellow-Red Color Scale" , "Use the 3 Traffic Lights icon set" , or "Remove conditional formatting from the worksheet." Your job is to select the correct range, pick the matching built-in rule, set any threshold the dialog asks for, and later clear rules when instructed.

Workflow shared by every built-in rule

Select the cells the formatting should evaluate (often a single column of numbers or dates).

Open Home → Styles → Conditional Formatting .

Choose a gallery category and a specific rule.

Complete the dialog (threshold value, format style, or icon options).

Confirm visually, then use Manage Rules if you need to edit or delete.

Always select the range before opening the gallery. If only one cell is active, Excel may apply the rule to a much larger region than the task intended—or to the wrong block entirely.

Highlight Cells Rules

Highlight Cells Rules format cells that meet a comparison:

Rule Typical exam use
Greater Than / Less Than / Between Flag scores, inventory, or variance thresholds
Equal To Exact status codes or target values
Text that Contains Partial text matches in labels or categories
A Date Occurring Due dates in the next week, last month, and similar
Duplicate Values Find repeated invoice numbers or IDs

Example: Select E2:E50 , choose Highlight Cells Rules → Less Than , enter 0 , pick Light Red Fill with Dark Red Text (or a format the task names). Cells with negative values highlight; others stay unchanged.

Top/Bottom Rules (Top 10 Items, Above Average, and related) sit in a sibling gallery. If the task says "highlight the top 5 values" , use Top/Bottom Rules , not Greater Than with a guessed cutoff.

Data Bars

Data Bars draw a horizontal bar inside each cell proportional to its value. Larger numbers get longer bars; negatives can show in a contrasting direction depending on options.

Select the numeric range.

Conditional Formatting → Data Bars .

Pick a gradient or solid fill style from the gallery.

Data bars shine for comparing magnitudes in one column without inserting a chart. They do not replace sparklines: sparklines show a series across columns for one row; data bars compare values down a column (or across the selection) at a single snapshot.

Use More Rules only if the task demands a custom minimum/maximum; most Associate prompts accept a built-in gallery choice.

Color Scales

Color Scales shade each cell along a continuum (for example green–yellow–red or blue–white–red). Relative standing is visible without reading every number.

Select the range.

Conditional Formatting → Color Scales .

Choose the palette that matches the descriptor (often named by color order).

Three-color scales map low, midpoint, and high values to three colors. Two-color scales map low to high. If the project names "Green - Yellow - Red Color Scale" , pick that exact thumbnail so low values go green (or as shown in the gallery preview) and high values go red—match the gallery, do not invent a custom scale unless asked.

Icon Sets

Icon Sets place symbols (arrows, traffic lights, ratings stars, flags) beside or in place of values based on thresholds.

Select the range.

Conditional Formatting → Icon Sets .

Choose a set such as 3 Traffic Lights or 3 Arrows .

By default Excel splits the range into percentiles (for example thirds for a 3-icon set). Show Icon Only (via Manage Rules → Edit Rule) hides the number when a task wants icons alone. Only change thresholds when the instructions give explicit cutoffs; otherwise the built-in set is enough.

Manage Rules vs Clear Rules

Two cleanup paths appear on exams:

Manage Rules

Conditional Formatting → Manage Rules opens the Conditional Formatting Rules Manager. Set Show formatting rules for to Current Selection or This Worksheet to find rules. From here you can edit thresholds, change the applies-to range, reorder rule priority, or delete a single rule without wiping every rule on the sheet.

Use Manage Rules when multiple rules overlap or when you must remove one rule but keep another.

Clear Rules

Conditional Formatting → Clear Rules offers:

Clear Rules from Selected Cells — strips conditional formatting only from the selection
Clear Rules from Entire Sheet — removes all conditional formatting rules on the active worksheet

Pick the scope the task states. "Remove conditional formatting from the range C2:C30" means select that range and clear selected cells. "Remove all conditional formatting from the worksheet" means clear the entire sheet.

Clearing rules removes formatting driven by those rules . Cell values, manual fills you applied without conditional formatting, and sparklines remain. If a cell still looks highlighted after Clear Rules, check for ordinary Home → Fill Color formatting that was not rule-based.

Interaction with other Domain 2 skills

Named ranges: You can select a named range first (Name Box) then apply conditional formatting to that selection—useful when the task names both.

Sparklines: Often placed in an adjacent column while data bars or color scales format the source numbers. Do not clear sparklines when the task only says to remove conditional formatting.

Tables: Built-in rules work on table columns; select the column data (not necessarily the header) unless told otherwise.

Exam pitfalls

Applying the rule with a single cell selected so Excel expands to a huge region — preselect the exact range.

Choosing Data Bars when the task required Color Scales (or Icon Sets) — the visual is wrong even if cells "look fancy."

Typing a threshold of 10% when the rule expects the number 0.1 or a plain 10 depending on the dialog — read the operator (greater than value vs percent).

Using Clear Formats on the Home tab instead of Clear Rules — may strip fonts and borders the project still needs.

Forgetting that Duplicate Values is under Highlight Cells Rules when the task asks to flag duplicates.

Leaving an old rule in place when the project says to remove formatting — verify with Manage Rules that the list is empty for that scope.

Practice sequence

Apply Less Than 0 red highlight to a profit column.

Add Data Bars to a quantity column and a Color Scale to a percent column.

Apply an Icon Set to ratings; edit the rule to show icons only if you want practice with Manage Rules.

Clear rules from one column, then clear remaining rules from the entire sheet.

Complete that loop and you have covered apply plus remove for every built-in conditional formatting family MO-210 tests.