This quick guide explains how you can remove duplicates in LibreOffice calc list. And it’s super easy.
If you used Microsoft Excel, you might have already known that it provides an option in the ribbon to remove duplicates.
However, there is no direct option to remove duplicates in LibreOffice Calc. You have to use the advanced filter to achieve this. Here’s how.
Remove Duplicates in LibreOffice Calc
It’s easier to understand with an example. We have this list of items with the following data. The duplicate values are colour-coded as below.
To remove duplicates, select the list. And then, from the menu, click on Data -> More Filters -> Advanced Filters.
On the Advanced Filter window, select the range for removing the duplicates. It can be a single column or multiple columns of data of any size.
Then select the option "No duplicates".
Press OK.
And there you have it. Your data set without duplicates. Now you can copy the unique data to some other sheet or workbook for further analysis and processing.
Common Errors and Usage Notes
- The above method just show you the unique data items by hiding duplicate rows. If you look at the above image, you can see the row 3 and row 5 is shown. That means, row 4 is hidden. Unfortunately, LibreOffice doesn’t have functionality as of current version to delete everything duplicates and show you unique values in a chronological order.
- So, once you have the above list, you need to copy them to a different sheet or workbook for your further processing.
- If you are receiving any of the below two errors, then make sure to select the range and the range is visible in the text box under ‘read the filter criteria from’.
Video
If you still have doubts, I have put up a quick video below. And don’t forget to subscribe to us on YouTube!
As the LibreOffice Calc doesn’t provide a simple enough quick option, you have to follow this way to remove the duplicates.
Looking for something else?
Read our complete tutorial index of Calc, Writer, Impress and Draw.
I am doing this exact process but get the error “This range does not contain a valid query.” Can you help me resolve this error?
You have to select the list first. Then choose option from menu.
I have made a video and updated additional comments to address the errors. See above.
Excel:
1. Select the range of cells
2. Click Data > Remove Duplicates.
3. Click OK.
And this shows exactly why open source is where it is…
Well, this is what it is. You can always request a feature here: https://bugs.documentfoundation.org/
Do not Work!
My version is:
Version: 7.1.3.2 / LibreOffice Community
Build ID: 10(Build:2)
CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: gtk3
Ubuntu package version: 1:7.1.3-0ubuntu0.21.04.1
Calc: threaded
But a simple spreadsheet, where I just want to remove duplicate rows, this filter tool doesn’t Work! When I perform the procedure, all records are gone. When I apply the filter again, the duplicate records come back.
This doesn’t seem to actually remove the duplicate rows. It only reduces the height of the rows containing the duplicates to a very low value so it appears like they aren’t there. However if you expand the height back up the values are there, so if you are counting the data by formula the duplicates remain?
Yes, you are right. It’s unfortunate that this is the only way so far. So for better clarity, I have updated the article at the end to avoid confusion.
How come you do not respond to the users that say these steps do not work? It appears there are many users that receive this message: “This range does not contain a valid query.”
You have to select the list first. Then choose option from menu.
Even after selecting the list and then trying to trace out the duplicate, there appears a dialogue box saying “This range does not contain a valid query.”
It would be easier if you could explain what you mean exacly by “the list”. I have exactly 1 column with values in my spreadsheet and after choosing this column as a range it still return above message/error.
I have made a video and updated additional comments to address the errors. See above.
(note: I did figure a way out that works, you can skip to the end to see how)
Hey, just tried doing this, and I noticed if I have 3 entries that are identical, this process will remove the ones where the ENTIRE row is identical, but not when the values of the column are identical.
example I have
A B
1 – bob $7
2 – bob
3 – bob
If I use this filter (i understand how filters work, not getting into that here) it will hide either row 2 or 3, but not both. If I run the filter a second time, no change.
filter is:
A = not empty
no duplicates
Some testing I did and results:
B = not empty
no duplicates
it hid all the rows that had b as empty, even rows that had a unique A column entry. So still not what I need.
——————————–
A = not empty
and
B = empty
no duplicates
hides all duplicates, but only shows rows where B is empty.
——————————–
A = not empty
or
B = empty
no duplicates
does the same as only having A = not empty (shows duplicates still, if the entire row is unique)
——————————–
using the Advanced filter I get success!
data –> more filters –> advanced filter
first click the range selector, then select the cells to be checked (only column A), you can include the title row at the top, as long as you know it is a unique value, then simply check the no duplicates box.
Success!
Thanks. I have made a video and updated additional comments to address the errors. See above.
And it still does not work with my LibreOffice Calc on Ubuntu …
I have made a video and updated additional comments to address the errors. See above.
arindam,
Thank you for the help, video and pictures. Your writeup is very informative and this method, while not as easy as M$, gave me the results I needed.
Much appreciated.