In this article 5 sections
- 1. Select the range to color first
- 2. Find duplicate values through the Home tab’s rule menu
- 3. Mark overdue rows with a formula rule
- 4. Check the order when there are multiple rules
- 5. Checklist before saving
If you want to spot rows with overdue deadlines or cells containing the same value twice at a glance in Excel, using conditional formatting is the simplest approach. Conditional formatting changes the font or fill color only when a cell value meets a rule. Because the value inside the cell does not change, there is no risk of deleting the original data. This article organizes the setup steps based on the menu paths in Microsoft’s official support documentation.
1. Select the range to color first
It is safer to select the range containing the actual data rather than an entire column. For example, specify a range such as A2:C200, excluding the header row. If you create an Excel Table, the same rule will often carry over when you add a new row, but it is safer to check the range once in the Rules Manager after adding it.
2. Find duplicate values through the Home tab’s rule menu
(1) Select the range. (2) On the Home tab, click Conditional Formatting in the Styles group. (3) Point to Highlight Cells Rules and select Duplicate Values. (4) Choose Duplicate or Unique and set the display color. This is the sequence shown in Microsoft’s official support documentation.
There is an important point to note. Leading or trailing spaces and invisible differences can cause identical names to remain as different values. Before checking for duplicates, make a copy of the original and first consider whether to use the TRIM function to clean up spaces. This part was not tested with an actual file in this article.
3. Mark overdue rows with a formula rule
When you need to check two or more columns together, such as for a deadline condition, create a new rule in Conditional Formatting and select Use a formula to determine which cells to format. For example, if the deadline is in column B, the completion status is in column C, and the data starts on row 2, set the Applies to range to A2:C200 and enter the formula =AND($B2<>"",$B2"Complete"). This formula is true only when B2 is not blank, is earlier than today, and C2 is not Complete.
The row number (2) in the formula must match the first row of the Applies to range. If the row number is wrong, the colors will appear shifted by one row. The formula above is an example assuming a table structure and was not tested on an actual worksheet.
4. Check the order when there are multiple rules
When multiple rules apply to the same cell, open the list through Home → Conditional Formatting → Manage Rules. Because the rule at the top is evaluated first, if the color is not what you expected, check whether the rule above is being applied first. Each rule also shows its own Applies to range.
5. Checklist before saving
1) Is the color visible only on actual data rows? 2) Are blank cells being colored incorrectly? 3) Does the range extend when you add a new row? 4) Have you placed status text (for example, Overdue) in an adjacent column so that you do not rely on color alone? 5) Are the original values unchanged?
Conditional formatting is a feature that changes only how data is displayed. Delete duplicates separately, and make a copy of the original before deleting anything. Related articles: How to preserve the original before deleting duplicate items in Excel ↗, How to create an Excel drop-down list: Setup steps to reduce input errors ↗
Source: Microsoft Support, 'Use conditional formatting to highlight information in Excel' (support.microsoft.com/en-us/excel/use-conditional-formatting-to-highlight-information-in-excel), checked on 2026-11-09 KST. The 'Use a formula to apply conditional formatting in Excel for Mac' document was also consulted for the formula rule menu.
Sources and original article
Read the Korean original on Naver ↗
This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.
This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy