Apply and Remove Conditional Formatting

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

1. Select the cells the formatting should evaluate (often a single column of numbers or dates).
2. Open Home → Styles → Conditional Formatting.
3. Choose a gallery category and a specific rule.
4. Complete the dialog (threshold value, format style, or icon options).
5. 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:

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.

1. Select the numeric range.
2. Conditional Formatting → Data Bars.
3. 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.

1. Select the range.
2. Conditional Formatting → Color Scales.
3. 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.

1. Select the range.
2. Conditional Formatting → Icon Sets.
3. 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

1. Apply Less Than 0 red highlight to a profit column.
2. Add Data Bars to a quantity column and a Color Scale to a percent column.
3. Apply an Icon Set to ratings; edit the rule to show icons only if you want practice with Manage Rules.
4. 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.