How to create predefined table filters?


image description

This table has autofilter enabled in the headings (| ColA | ColB | ColC |).

I want to create predefined so that instead of manually select each option in the dropdown, I can just “click” or do something quicker. Example of custom filters:

ColA=1, ColB=2, ColC=3

ColA=2, ColB=1, ColC=2

Is there some built-in functionality or any hack that does this?

Your example doesn’t ever work if ColA=1, ColB=2, ColC=3 means 3 conditions should be fulfilled at the same time. Anyway: What you are looking for seems to be LibreOffice Help - Advanced Filter in combination with Named Ranges having set option [x] Filter in Range Options (which makes the named range available in drop down Read Filter Criteria From when applying an advanced filter).


see my comment and the following sample file: AdvancedFilters.ods

To test:

  • Select columns A:C
  • Data -> More Filters -> Advanced Filter...
  • Select FILTER1 or FILTER2 from drop down Read Filter Criteria From
  • Click OK

(This answer doesn’t describe how to define a named range - see LibreOffice Help - Naming Cells) for more information)

Hope that helps.

If the answer helped to solve your problem, please click the check mark (:heavy_check_mark:) next to the answer.

That indeed works in your file but i’ve been trying to understand the logic to reproduce it but i’m not getting it. How do you define de filters? How do you define the filter conditions? What are the steps to reproduce that?

Have you checked sheet Filter Definitions? These are the Filter Definitions. These little sub-tables define how your data should be filtered when you apply any named filter (just consider the named range a name of the filter) and obviously you must not delete 'em. These tables repeat the structure or you original Data table incl. header and then show the values/conditions to be filtered. Depending on there position there is an logical AND or OR connection.

To define a FILTER1 do

  • Select cells A2:C3 (in sheet Filter Definitions)
  • Sheet -> Named Ranges & Expression -> Define
  • Provide a Name
  • Expand Range Options
  • Set option [x] Filter

To define FILTER2 repeat the same steps for A8:C9

(This is all visible on the screenshots in my file and probably you may want to read the LibreOffice Help - Advanced Filter from my comment above once more )

Ok, now I was able to reproduce your example (this video also helps). Do you know an easy/fast way to activate and deactivate this filters (like a shortcut)?

Resetting could be done via Tools -> Customize... -> Tab: Keyboard. Search for Reset, select Function Reset Filter and select a shortcut key in Shortcut Keys. Finally click button Modify and the desired shortcut key appears in pane Keys. The same way you could assign a shortcut, to bring up the Advanced Filter dialog but you still need to select the proper filter name (and to select data columns advance).

Function Reset Filter doesn’t work for advanced filters. Currently i can only remove them doing ctrl+z. Is there other way/function to remove advanced filters that were applied/activated?

For me it worked - otherwise I hadn’t posted. Had you selected the filtered columns in advance (just as it is required for applying the filter this is also required for resetting a filter)?

Yes, still doesn’t remove the filter :confused: