21

In Excel, Office 2007, I have the option to highlight duplicate values on the menu. Keep simple! Select the column, select "Conditional Formatting" - "Highlight Cells Rules" - "Duplicate Values" and voilá! All values that were duplicate on this column it was paint with color red, for example.

Actually I use the LibreOffice Calc, version 5.0.3.2, and I can't find this option on the menu!

How can I do this job?

Community
  • 1
  • 1
Edgar Oliveira
  • 323
  • 1
  • 2
  • 8

3 Answers3

19

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.

  • 1
    What if you want to compare all the selected cells to each other and find duplicates across all data? – Hooli Jul 30 '16 at 11:30
  • 1
    Please do not post new questions as comments to old answers. Click Ask Question in hte top-right corner. –  Jul 30 '16 at 14:43
  • I believe in the original question and in the answer by Jeeped, "all the selected cells" are indeed compared to to each other and even non-adjacent duplicates are found. So maybe Hooli has not asked a new question but is unclear about the range of the original question and original answer? I too find the original question not very precisely worded but am not veteran enough to dare edit. – Martin Zaske Jan 03 '17 at 15:38
  • For some reason, this only marks some duplicates, but not all. When I take some cell contents, and CTRL+F for it in the the same column, OO does find the duplicates, yet they are not formatted accordingly... – Michiel Haisma Mar 05 '18 at 16:55
  • seems like a more flexible approach would be to select the entire column and use `COUNTIF(A$1:A$65535; INDIRECT(CELL("ADDRESS")))>1` so that you wouldn't have to keep adjusting the endpoint of the range every time the range changed. – Jeff Feb 08 '19 at 23:54
11

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

Shrout1
  • 2,497
  • 4
  • 42
  • 65
2

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.

Jeff
  • 2,095
  • 25
  • 18
  • This is precisely what I was looking for. This was very hard to find, as everyone on the internet is just marking all cells as duplicates, instead of only the real, actual duplicates. The first one to appear is obviously not a duplicate, as it's the first one... Thank you! – Akito Jan 20 '22 at 13:47