Auto highlight duplicate entries in calc

Hello guys, I know there is some conditional formatting required here, but as per my previous threads, I’m hopeless when it comes to this type of thing, but basically I have a spreadsheet for customers and as I enter (or rather copy/paste) the customers name into the spreadsheet, I’m looking for a way for that cell to be highlighted if the name has already been entered into the sheet. The purpose is to let me know this is a repeat customer for a variety of reasons but would make life so much easier to know as I enter the details rather than trying to search manually every time I go to enter customer details. The customers name would only be found in 1 column.
Any help appreciated.


this is quite straightforward - lets assume your “customer name” is in column A, then

  • Select column A by clicking on the header character A
  • 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 cell range being A1:A1048576 (You may want to reduce the 1048576 to any reasonable number you assume the maximum to be ever used)
  • Click OK button

Hope that helps.

Tested using LibreOffice:

  Version:, Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
  CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5;
  Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

If the answer is correct or helped you to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

1 Like

Works perfectly, thank you for that :slight_smile:

Thanks a lot …


  1. Select area names
  2. Format
  3. Condicional
  4. Condition…
  5. Cell value is
  6. duplicate
  7. Error (Or create style)
  8. OK

image description

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

1 Like

i did the same but if there are any empty fields they are getting highlighted along with the duplicate ones

This works perfectly to find duplicates. Is there another step or steps to automatically select and delete all highlighted rows?

@caliray, Use Data Advanced Filter

(1) Double the Title

image description

(2) Select the data

(3) Data

(4) More filters

(5) Advanced filter

(6) Filter

(7) Without duplicate

(8) Copy Results

(9) Recording location

(10) OK

image description

image description