Highlight Duplicates or Unique Values in LibreOffice Calc

LibreOffice Calc allows users to instantly identify duplicate or unique records across spreadsheets using conditional formatting combined with the COUNTIF formula. While Calc does not feature a single-click duplicate button like some other spreadsheet programs, setting up a conditional rule takes only a few steps and provides complete control over highlighting single occurrences, repeated values, or entire duplicate rows.

Step 1: Create a Highlight Style

LibreOffice Calc uses cell styles to apply conditional formats. Before creating your rule, define the color you want to use:

  1. Press F11 (or go to View > Styles) to open the Styles sidebar.
  2. Right-click Default under the Cell Styles tab and select New.
  3. In the Organizer tab, name the style (e.g., Duplicate_Highlight or Unique_Highlight).
  4. In the Background tab, choose a highlight color (e.g., light red for duplicates or light green for unique values).
  5. Click OK.

Step 2: Highlight Duplicate Entries

To highlight any value that appears more than once within a selected range:

  1. Select the range of cells you want to check (e.g., A2:A100). Note the active cell in the selection (usually the top-left cell, A2).

  2. Go to the menu bar and select Format > Conditional > Condition…

  3. In the Condition 1 dropdown, change Cell value is to Formula is.

  4. Enter the following formula:

    COUNTIF($A$2:$A$100, A2) > 1

    Note: Use absolute references ($A$2:$A$100) for the range, but keep the individual cell reference (A2) relative so Calc evaluates each cell independently.

  5. In the Apply Style dropdown, select your custom style (e.g., Duplicate_Highlight).

  6. Click OK. Any cell containing a value found more than once in the range will now be highlighted.

Step 3: Highlight Unique Entries

If your goal is to find entries that appear only once in the list:

  1. Select the target range (e.g., A2:A100).

  2. Navigate to Format > Conditional > Condition…

  3. Set the first dropdown to Formula is.

  4. Enter the following formula:

    COUNTIF($A$2:$A$100, A2) = 1

  5. Select your designated unique highlight style from the Apply Style list.

  6. Click OK.

Highlighting Duplicates Across Multiple Columns

To detect duplicate rows based on multiple criteria (for example, matching First Name in Column A and Last Name in Column B):

  1. Select the entire multi-column data range (e.g., A2:B100).

  2. Go to Format > Conditional > Condition…

  3. Set the condition to Formula is.

  4. Enter the COUNTIFS formula:

    COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1

    Note: Place the $ sign only before the column letter for the single-cell references ($A2, $B2) to highlight across the entire row.

  5. Choose your style and click OK.

Managing or Removing Rules

To edit or delete conditional formatting rules:

  1. Select the affected range or entire sheet.
  2. Go to Format > Conditional > Manage…
  3. Select the rule from the list and click Edit to modify formulas/styles, or Delete to remove the highlighting.