How to Highlight Unique Values in Microsoft Excel Using Conditional Formatting

Auditing large datasets in Microsoft Excel often requires identifying entries that appear only once. Whether you are reviewing inventory logs to spot one-off orders, analysing customer databases for first-time buyers, or reconciling bank statements for unmatched transactions, picking out distinct values among thousands of rows manually is impossible without visual assistance.

While many Excel users are familiar with highlighting duplicate records, Excel’s Conditional Formatting engine includes an equally powerful built-in rule designed specifically to highlight unique values. You can apply this rule in seconds without typing any formulas, or create custom COUNTIF rules for advanced column comparisons.

Method 1: Highlighting Unique Values Using Built-In Rules

Excel features a dedicated preset that identifies any cell value with a frequency count of exactly one across your selected range:

  1. Open your spreadsheet in Microsoft Excel.
  2. Highlight the cells, column, or row you wish to evaluate (for example, column A2:A100).
  3. On the Home tab of the top ribbon, click the Conditional Formatting button.
  4. Hover over Highlight Cells Rules to reveal the flyout menu.
  5. Click Duplicate Values…
  6. In the dialogue box that appears, click the left drop-down menu which defaults to Duplicate.
  7. Select Unique from the options.
  8. In the right-hand drop-down menu, choose your preferred formatting colour (such as Light Red Fill with Dark Red Text, Green Fill with Dark Green Text, or choose Custom Format… to pick custom border styles and background colours).
  9. Click OK.

Every value in your selected range that appears exactly once will be highlighted instantly. If you add duplicate entries later, the formatting automatically recalculates and clears the highlight in real time.

Method 2: Highlighting Unique Values Using a Custom COUNTIF Formula

If you want to highlight an entire table row based on a unique value in a specific column, or need more granular control over multiple conditions, use a custom formula rule:

  1. Select your entire data range (for example, A2:D100), ensuring the active cell is in row 2.
  2. Go to Home > Conditional Formatting > New Rule…
  3. In the Select a Rule Type list, choose Use a formula to determine which cells to format.
  4. In the formula text box, enter the following expression (assuming column A contains your identifier):
    =COUNTIF($A$2:$A$100, $A2)=1
  5. Click the Format… button, switch to the Fill tab, and choose your highlight colour.
  6. Click OK to confirm your formatting, then click OK again to apply the rule.

Because the column reference ($A) is locked with a dollar sign while the row reference (2) remains relative, Excel evaluates column A for uniqueness and shades the entire matching row across columns A through D.

Sorting or Filtering by Your Highlighted Unique Cells

Once your unique values are shaded, you can isolate them from the rest of your dataset for easy export or reporting:

  1. Click any cell inside your table header row and press Ctrl + Shift + L to turn on column filters.
  2. Click the filter drop-down arrow on your formatted column.
  3. Hover over Filter by Color.
  4. Under Filter by Cell Color, click your highlight shade.

Excel will temporarily conceal all repeating entries, leaving only your distinct, unique records visible on the worksheet.

Clearing the Conditional Formatting Rule

To remove the unique highlighting rule once your audit is finished:

  1. Select the formatted cells.
  2. Navigate to Home > Conditional Formatting > Clear Rules.
  3. Choose Clear Rules from Selected Cells (or Clear Rules from Entire Sheet).

Employing these conditional formatting techniques ensures that outlier records and distinct entries never slip through your spreadsheet audits undetected.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.