Ask Your Question

How to create predefined table filters?

asked 2020-05-23 11:45:40 +0200

kdev gravatar image

updated 2020-07-20 10:01:52 +0200

Alex Kemp gravatar image


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?

edit retag flag offensive close merge delete



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

Opaque gravatar imageOpaque ( 2020-05-23 12:26:36 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-05-23 12:40:11 +0200

Opaque gravatar image

updated 2020-05-23 12:50:43 +0200


see my comment and the following sample file: C:\fakepath\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 (✔) next to the answer.

edit flag offensive delete link more


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

kdev gravatar imagekdev ( 2020-05-27 13:09:03 +0200 )edit

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 )

Opaque gravatar imageOpaque ( 2020-05-27 13:28:12 +0200 )edit

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

kdev gravatar imagekdev ( 2020-05-27 13:41:48 +0200 )edit

Resetting could be done via Tools -> Customize... -> Tab: Keyboard. Search for Reset, select FunctionReset 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).

Opaque gravatar imageOpaque ( 2020-05-27 13:51:00 +0200 )edit

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?

kdev gravatar imagekdev ( 2020-05-27 23:35:15 +0200 )edit

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

Opaque gravatar imageOpaque ( 2020-05-27 23:39:23 +0200 )edit

Yes, still doesn't remove the filter :/

kdev gravatar imagekdev ( 2020-05-27 23:53:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-23 11:45:40 +0200

Seen: 60 times

Last updated: May 23 '20