Find duplicates in excel and LibreOffice Calc

Last updated on May 10, 2013 @ No comments yet

When you move from MS Excel to LibreOffice Calc, you might find it harder in the beginning to work with the Excel way on LibreOffice (LO) Calc. For example menus and shortcuts. There was time when i keep working on both application at the same time in order to find duplicates in excel that i thought not available on LibreOffice Calc. I need LibreOffice because it can read exported xls file from MySQL Workbench without any problem while MS Excel give me an error message – unreadable content.

Regarding about finding duplicates on excel and calc, we can do it easily with both application using nearly the same way. But please note that all menu and result might not be the same. On Excel you can just click a button to do things, but on LibreOffice Calc it might be a bit tricky.

Finding and Mark Duplicate Values on Excel

  • We can use conditional formatting to easily find and mark duplicates on excel.
  • Assume you already open an Excel file, select a column or cells that contain duplicate values.
  • Open Home tab to find Conditional Formatting button.
  • Click on it, navigate to Highlight Cells Rules and then select Duplicate Values.
  • Select cell format to mark the duplicate with specific fonts, color and background from the list or set your own custom cells format.

mark duplicate values

Finding and mark duplicate values on LibreOffice Calc

  • Same way with Excel, we will use conditional formatting menu on LibreOffice Calc.
  • Select columns or cells that contain duplicates.
  • Open Format menu and navigate to Conditional Formatting menu and then select Condition.
  • On 2nd form you’ll see equal to, change this to duplicate (scroll down to find duplicate).
  • On Apply Style form, select a style or set your own by selecting new style.
  • If you choose new style, you can customize duplicate cells value by changing font, style and background on new style window.

calc find and mark duplicate

Find and remove duplicates in excel

  • Select one or more columns or cells that contain duplicates.
  • Navigate to Data tab and click Remove Duplicates button.
  • If the sheet contain only single column, you won’t be asked with selection expanding.
  • If the sheet contain multiple column and you only select a single column, you’ll be asked with expand the selection question. If you want to remove only current selected column, just select continue with current selection.
  • If you choose expand the selection or you’ve select multiple column, you’ll be asked to choose column that contain duplicates. It will then delete duplicate cells on selected column.

excel remove duplicate

Remove duplicate on LibreOffice Calc

  • Not like Excel, in Calc we can only copy the result into another cells
  • Select one or more columns or cells that contain duplicates.
  • Open Data menu, navigate to Filter and select Standard Filter.
  • On filter window select Value to Not Empty.
  • Click on More Options to show advanced options.
  • Check on No Duplication.
  • You can stop here if you only want to filter rows from duplicate values, otherwise check on Copy Result to
  • Click on the right Shrink button and select a single cell location for the result will be and press enter to back to filter window.
  • Click OK to finish the process.
  • If you want to filter based on multiple column, repeat step 4 by adding another filter criteria with AND operator, and Field name with Column or Cell header name.

libreoffice calc remove duplicate

Both office application has the similar features but of course on different ways and result. While you can simply click a button to do something on MS Excel, on LibreOffice Calc you need to do it a bit harder and tricky. But you just need to get used to it and practice more with it. I’ve seen all features that i used to work on Excel can be done easily on Calc. The problem is, sometime applied settings not always work on both Excel and Cals. Excel document with specific formulas might not properly opened on Calc or vice versa.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply


Loading Google+ Comments ...
Loading Facebook Comments ...