Ask Your Question

Autofilter Usage [closed]

asked 2013-12-05 10:32:48 +0100

jnnewton gravatar image

When using autofilter in calc, there are two things that I can't figure out how to do, that I previously used heavily in excel.

  1. Where's the button to clear all the filter selections?
  2. How do i select several values from the checkbox list and include (or not include) blank cells?
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-22 11:00:26.276013

2 Answers

Sort by » oldest newest most voted

answered 2013-12-05 11:10:29 +0100

Pedro gravatar image
  1. There is no button. That is a bug/missing feature. IMO the "All" check box should show unchecked if any filter is currently applied. Since it is not, the solution is to Uncheck and then Check again the All tick box to Clear the filter selections.

  2. Uncheck the All box, and select the items from the list. Filter with Empty, Not Empty (under Top 10). Alternatively use the Standard Filter dialog to create more complex combinations.

edit flag offensive delete link more


  1. :( I've got 34 columns autofiltered. doing each is a pita. Thanks for clarifying though
  2. Can't get it to work. I don't know how to "filter with empty". If i click "Empty" or "Not Empty", it clears my checked selections and just gives me the Empty or Not Empty. If i want to filter 10 items from the check list plus the empties, standard filter is cumbersome. Can you clarify, I'd really like to learn this, especially now that it seems possible..
jnnewton gravatar imagejnnewton ( 2013-12-05 15:21:46 +0100 )edit


  1. Use menu Data > Filter > Remove Filter (clears the filter from all columns)
  2. Use the Standard Filter and set e.g. Column1 = Empty OR Column1 = 2

I can't provide a clearer example without knowing your data or what you are trying to filter...

Pedro gravatar imagePedro ( 2013-12-06 17:53:15 +0100 )edit

Sorry I'm late getting back to this. I've figured out how to do that for a single column. What about more than one? I would need this: (col1 = x OR col1 = empty) AND (col2 = y OR col2 = empty).

but standard filter like this: col1 = x OR col1 = empty AND col2 = y OR col2 = empty doesn't group the and / or's correctly. and it's going to get more difficult for more than two columns.

jnnewton gravatar imagejnnewton ( 2014-04-06 00:38:50 +0100 )edit

@jnnewton, what do you mean "doesn't group the and/or's correctly"? Can you provide an example of what you get and what you expect to get?

Pedro gravatar imagePedro ( 2014-04-06 18:07:55 +0100 )edit

Start Data: Make,Body; Ford,car; Chevy,car; Nissan,; Dodge,; Dodge,suv; Mazda,suv; Nissan,; ,truck; Ford,; ,; GMC,; GMC,truck; ,truck; Ford,; ,car; Honda,car; Honda,suv; ,; BMW,car; ,truck;

Standard filter for GMC or Blank in the Make column and truck or blank in the body column should result in this: ,truck; ,; GMC,; GMC,truck; ,truck; ,; ,truck;

Which i verified using excel at work. I cannot reproduce this with the standard filter in libre in any sensible manner.

jnnewton gravatar imagejnnewton ( 2014-04-07 00:21:17 +0100 )edit

Here, it's much easier to use SO than trying to format for this forum:

jnnewton gravatar imagejnnewton ( 2014-04-07 00:45:53 +0100 )edit

It would have been much easier if you had uploaded a sample file somewhere ;) It is indeed impossible using the standard filter (seems to be a bug in LO). If you use AOO you do get the same result as in Excel. There is probably a way to do this with Regular Expressions but it seems a little overkill...In any case it is annoying that you can 't simply have check a box for (Blanks)...

Pedro gravatar imagePedro ( 2014-04-07 03:32:09 +0100 )edit

is far as i see - and understand - the task is! possible with newer versions of LO calc, see -, just tried it in, works

newbie-02 gravatar imagenewbie-02 ( 2019-11-14 06:55:48 +0100 )edit

answered 2014-05-08 02:46:00 +0100

m.a.riosv gravatar image

updated 2014-05-08 02:52:02 +0100

I think it's possible with standard filter using regular expressions

In condition value:
(| is the OR in regular expressions, see the help for more detailed information)


image description(http://)

Another way is with the Advanced filter, and using regular expressions you can do a very complex filters.

Have you tried importing the excel file with LibreOffice?

A quick way to delete all filters is selecting the first Field name condition to -none- in the Standard filter. ..................................................................................
And you can add a button in Menu/Tools/Customize

image description

edit flag offensive delete link more


Miguel Ángel, the example provided (Make = GMC OR Make = empty) AND (Body = truck OR Body = empty) can not be solved with LO (unless you use Regular Expressions). The solution is simple:

Pedro gravatar imagePedro ( 2014-05-08 11:47:00 +0100 )edit

Pedro, I don't if it is so simple to implement like it seems. But it's a good idea.

m.a.riosv gravatar imagem.a.riosv ( 2014-05-08 21:18:47 +0100 )edit

Question Tools

1 follower


Asked: 2013-12-05 10:32:48 +0100

Seen: 1,658 times

Last updated: May 08 '14