Autofilter Usage

[jnnewton]

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?

[Pedro1]

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.

[jnnewton]

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…

[Pedro1]

@jnnewton,

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…

[jnnewton]

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.

[Pedro1]

@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?

[jnnewton]

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]

Here, it’s much easier to use SO than trying to format for this forum: https://stackoverflow.com/questions/22900941/libre-office-autofilter

[Pedro1]

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)…

[newbie-02]

is far as i see - and understand - the task is! possible with newer versions of LO calc, see - https://bugs.documentfoundation.org/show_bug.cgi?id=65505, just tried it in 6.2.7.1, works

[mariosv]

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

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

SampleFileFilter.ods

(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

[Pedro1]

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: https://bugs.freedesktop.org/show_bug.cgi?id=78259

[mariosv]

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