How do I color an entire row based on a condition?

Select 'Use a formula to determine which cells to format,' use a relative reference formula based on the first cell such as =$C2>1000, and then apply it to the entire range.

How do I use conditional formatting with dynamic arrays and spill ranges?

Select only the first cell of the spill range and set the rule range using a spill reference such as A2#.

What is the priority when rules overlap?

In Conditional Formatting Rules Manager, move rules up or down to adjust their order and select Stop If True when needed to prevent lower-priority rules from being applied.

Excel Conditional Formatting: Icons, Color Scales, Formula Rules, Full-Row Highlighting, and Duplicate/Expiration Date Highlights

CONTENTS

Excel Conditional Formatting: Icons, Color Scales, Formula Rules, Full-Row Highlighting, and Duplicate/Expiration Date Highlights

These procedures were tested and verified in Excel 365, with AI tools used only as support for organizing the draft.

1) Quick Start: Six Common Rules

Purpose	Rule	Setup Tip
Highlight the top 10 values	Top/Bottom Rules ▸ Top 10 Items	Number format, bold text, and a light fill color
Show KPIs with icons	Icon Sets	Set thresholds as numbers (values rather than percentages)
Visualize distribution	Color Scales (3-Color Scale)	Set minimum, midpoint, and maximum as formulas or percentiles
Color by status code	Text that Contains	Keywords such as “Delayed,” “Complete,” and “Pending”
Highlight blanks and errors	Formula	`=OR(A2="",ISERROR(A2))`
Target achieved	Formula	`=$F2>=$G2` (apply to the entire row)

2) Highlight an Entire Row with a Formula Rule

Select the table range (for example, A2:H1000).
Go to Conditional Formatting ▸ New Rule ▸ Use a formula.

Enter the formula:

=$C2>100000   // Highlight the entire row if sales in column C exceed 100,000

Set the format, such as fill color and bold text, then click OK.

TIP Write the formula based on the first row. Keep the column absolute ($C) and the row relative (2).

3) Flag Expiration Dates and Upcoming Deadlines

Before today: =A2<TODAY() ▸ gray/strikethrough
Within 7 days: =AND(A2>=TODAY(), A2<=TODAY()+7) ▸ yellow
Today: =A2=TODAY() ▸ bold/bright highlight

4) Highlight Duplicates and Unique Values

Go to Conditional Formatting ▸ Highlight Cells Rules ▸ Duplicate Values ▸ choose a color.

For more precision, use formulas:

=COUNTIF($B:$B,$B2)>1     // Duplicate
=COUNTIF($B:$B,$B2)=1     // Unique

5) Use with Dynamic Ranges and Spill Ranges (#)

To apply a rule to dynamic array results, select only the first cell and set the Applies To range to =A2#. To use it with filtering and sorting, the FILTER, SORT, and UNIQUE combination is recommended.

6) Troubleshooting Checklist

Rule is not applied: Check the selected range and the relative/absolute references in the criteria cell.
Multiple rules conflict: Adjust their order in Rules Manager and select Stop If True if needed.
Slow performance: Limit the target column to a table and use Tbl[Column] references instead of entire columns.
Excel display differs from print output: Check the settings in the Print/PDF guide.

7) Practice Tasks and Templates

Task A — Three-level KPI icons (green, yellow, and red)

Set thresholds as numeric values for 100%, 90%, and below 90% of the target. Reuse the setup across reports by changing only the colors.

Task B — “Request Date ≤ Completion Date” rule and delay highlighting

=$E2=""     // Highlight incomplete items
=$E2>$D2    // Highlight delayed items

When finished, save the table as a theme and reuse it by pasting it directly into next month’s report.

XLOOKUP vs VLOOKUP vs INDEX MATCH — 엑셀 조회 공식 비교·마이그레이션 완전 가이드