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.Conditional formatting allows you to apply various formatting styles (color, font, decoration, gradient) to cells to work with data on the spreadsheet: highlight or sort through and display the data that meets the needed criteria. 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 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)) 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: Error or any you defined in advance)Ĭheck 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) Select the menu: Format -> Conditional -> Condition.Ĭondition 1: Cell value is and select duplicate from the dropdownĪpply Style: Select your cell style (e.g. Select column A by clicking on the header character A (the top of the column) In LibreOffice Calc 6.0.7.3 this can be done by following these steps ( from this link): I've also put the formula in column C so that you can see how each row resolved to TRUE or FALSE. If the 'active cell' was A15 then that formula would be COUNTIF(A$1:A$15 A15)>1. It is important to get the 'active cell' correct.

Go to Format ► Conditional Formatting and set up a rule as a formula using COUNTIF(A$1:A$15 A1)>1. Select the column of numbers and note the 'active cell' (A1 in the sample image below). Tags : formatting,duplicates,openoffice-calc 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.Īctually I use the LibreOffice Calc, version 5.0.3.2, and I can't find this option on the menu! In Excel, Office 2007, I have the option to highlight duplicate values on the menu.

Openoffice calc highlight duplicates