Highlight duplicate values

Not exactly the solution to this specific question, but I find the following comes in very handy when you have the column in question sorted, especially when you are sorting by multiple columns:

If you want to format any value in a cell that is a duplicate of the one immediately above it, select the entire column and use the following formula in Conditional Formatting

(INDIRECT(CELL("address"))=OFFSET(INDIRECT(CELL("address"));-1;0))

In a sorted column, the first occurrence of a value will be considered "unique" in that it hasn't occurred yet, and then all of the duplicates which follow immediately afterward are flagged (I usually use a light grey for the font color). If the column is not the major order (ie 2nd or later in the sort criteria) then the "uniqueness" is effectively "reset" each time sorting starts over for this column.


In LibreOffice Calc 6.0.7.3 this can be done by following these steps (from this link):

Select column A by clicking on the header character A (the top of the column)

Select the menu: Format -> Conditional -> Condition...

Condition 1: Cell value is and select duplicate from the dropdown

Apply Style: Select your cell style (e.g. Error or any you defined in advance)

Check that your cell range is A1:A1048576 (You may want to reduce the 1048576 to any reasonable number you assume will be the maximum to ever be used)

Click OK button

Conditional Menu


Select the column of numbers and note the 'active cell' (A1 in the sample image below). Go to Format ► Conditional Formatting and set up a rule as a formula using COUNTIF(A$1:A$15; A1)>1.

   calc_duplicate_formatting

It is important to get the 'active cell' correct. If the 'active cell' was A15 then that formula would be COUNTIF(A$1:A$15; A15)>1.

I've also put the formula in column C so that you can see how each row resolved to TRUE or FALSE.