Redesigned autofilter popup

Posted on by Kohei Yoshida

I’m happy to announce that I’ve managed to squeeze this new feature in just in time for the 3.5 code freeze.

What’s new?

As I’ve mentioned briefly in G+, I’ve been working on brushing up the age-old autofilter popup window in the past few weeks. I have no idea how old the old one is, but it’s been there for as long as I remember. In case anyone needs a reminder as to what the old one looks like, here it is.

It’s functional, yet very basic. While this has served us for many years since the last century, it was also clear that the world has since moved on, and the people has started craving for modern looks and eye candies even in the office productivity applications. Clearly, it was time for a change.

In contrast to the old, here is how the new one looks:

I don’t know about you, but I really like the new one better. :-)

Motivation

Aside from updating the aged look of the old popup, I was also motivated to introduce the new popup for its ability to allow selection of multiple values from the selection list.

As you can see in the screenshot, the old one allowed only one value to be selected for each given column, which was not only very limiting but also caused interoperability issues with Excel documents, especially with those created in Excel 2007 and newer. In fact, adding this feature has been my long-term goal, ever since I began working on OpenOffice.org code base professionally. Because of this background, I had my personal attachment to fulfill that goal, and I’m really glad to have finally landed this feature 4 years and one name change (OOo to LibreOffice) later!

Laying the foundation

You may think that this new popup looks somewhat familiar. That’s because the same popup is also used as the pivot table (formerly data pilot) field member selection popup. I’ve touched on this previously on my blog, and you’ll probably notice the similarity when comparing the screenshot of the new popup with the screenshot of the pivot table popup included in that post.

Internally these two use the same code. In fact, when I developed that feature for the pivot table, I intentionally designed it to be re-usable, precisely so that I could use it for the autofilter popup at a later time.

So, the hard part of implementing the new popup had already been finished. All I had to do was to put the autofilter functionality into the popup and launch it instead of the ugly old one, which is precisely what I did to bring the new popup into reality. I also had to refactor the code that performs the filtering to allow multi-value matching, which was, while invisible to the users, not a trivial task.

Going forward

The work is not totally done yet. As of this writing, the xlsx filter has not been fully adopted to take advantage of the new multi-selection capability, but that’s my next task, and I expect that to be done in time for 3.5.

Also, the menu still looks very basic, and contains only the same set of options that the old popup had. This was done deliberately in order for us to ship it in time for 3.5, by avoiding the rather expensive process of re-designing the menu part of the popup. But I expect we work on the re-design post-3.5, to make it even better and more usable. Note that the new popup is fully capable of doing sub menus, which gives us all sorts of possibilities.

Anyhow, that’s all I have to say about this at the moment. I hope you guys will enjoy the new and shiny autofilter popup! :-)

Notes for testing

As with any new features, this one needs lots of testing. I’ve written new unit test to cover some parts of it, but unit test can’t cover all corners of use cases (especially those involving UI interactions), and manual testing from real users is always appreciated. Some of the affected areas I can think of are:

  • Built-in functions MATCH, LOOKUP, HLOOKUP and VLOOKUP that use the core filtering code which I’ve heavily refactored.
  • Import and export of the existing filtering rules, with ods, xls, and xlsx.
  • Filtering with pivot tables, which shares parts of the filtering code that has been refactored.
  • Standard and advanced filter dialogs

So, watch out for the next daily build that includes this feature!

Published by Kohei Yoshida

LibreOffice contributor, spreadsheet nerd, and machine learning beginner.

29 thoughts on “Redesigned autofilter popup”

  1. Thank you for this addition. It was much needed! Now if only somebody would do the colour picker properly (not only set colours but also the ability for user to pick his own colour while working with the document and not beforehand) and implement it on the tool bar, … But thank you, I will finally have compatible spreadsheets with MS Office… Regards, Luka

  5. Hi Kohei,
    The competitive product (say not the name) has an additional functionality in the same popdown window: sorting. In LibreOffice people tend to first filter and then sort the filter result, witch from my POW gives a wrong result. Only sorting first and then filtering gives the desired result.

    Do you have any plans for implementing a build in sorting in the AutoFilter?

    By the way: Great work you do every day. Kohai – you make a big different.

    1. Hi Leif,

      Sorting? Sure. That would be a possibility if that improves Calc’s usability in general. For now, I have no concrete plans as to what to add to the menu; so, anything is possible. I’ll keep the sorting functionality in the back of my head. Thanks for your feedback.

      2. I too desire / require sorting in the filter menu.

        Also after the filter menu is closed, please auto-scroll the current sheet up to the top so that users don’t think there are no results with the current filter and really, they just need to scroll up for them.

  6. Hi, Kohei san. That’s one of very rare situations that i want say THANK YOU MAN and bless You God!

  7. PARABÉNS!!!
    Uso muito o Auto Filtro e estava sentido falta desse recurso no LibreOffice. Eu e muitos outros que fazem uso dessa suíte, estamos torcendo pelo seu sucesso.

    P.S.: Será que ainda dá pra incorporar a pesquisa através de digitação de palavras como ocorrer com o MSO?

  9. wow thats awesome it will be big help in the migration from that famous comercial aplication
    the another thing cool can be upgrade the pivot tables system….

  10. I think the new redesign of the autofilter stays this way I can’t use Libre Office Calc anymore.. Not in a way I will like it..
    Most of the times I use it for learning spanish.. I’ve got more than 10.000 words (with the translation) and I keep adding new words whenever I find a new word in the book I’m reading..
    The current autofilter option is perfect for this to see if a word is already on the list or not.. By typing a word in the auto filter popup I can check if it’s already on the list or not..
    Hope it will be possible to change the new popup filter to make it work like the current one in the future release.. (BTW: At the moment the auto filter is extremely slow for the purpose I’m using it for.. Much too slow..)

    1. You can use selection list for that purpose. Just move the cursor to the bottom row and hit Alt-(down arrow), then you’ll get the popup much like the old autofilter popup.

  11. Ok.. That’s a little workaround for me.. Thanks.

    But when I find a word that’s on my list I also would like to see the translation.. A lot of times I write down words for which I’m not sure what the meaning is.. So the word can be on my list but I forgot what the meaning was or I’m not sure..

    So for my (maybe not very effective) way of learning another language the redesigned autofilter will not be as workable anymore.. For me it would be nice if there will be two separate autofilters.. One basic like the old one and a new more enhanced like the new one.. Thanks for your answer..

    1. For your use case, perhaps VLOOKUP (it’s a built-in cell function) is the right tool to use.

  13. The new popup autofilter have a very nice design, and work very well.
    And the 3.5 version of LibreOffice, seems to be a very good version, first of all, by the great reduction on the bugs and the performance improvement.

    But, (always a but), the popup seems to have been loosed the ability to use the letters/numbers in the keyboard to arrive at one item. This was very useful in long list selection.

  14. Please arrange the autofilter tab order works well its hangs at somepoint.may be replace the tab with down arrow can select the value from filter

    2. The sort by Font and Background color is a great idea!!

      I support that sorting method 100%. Great idea!

  16. The location of the drop down window, as currently designed, along the left border of the column, is not a good design. This is because it now hides the values in the current column, which may be useful to see when selecting a value in the drop down list. I’ve been using 3.5 for a while and find that I do indeed often like to see the values currently displayed there (and now hidden by the drop down).

    I better design choice would be to align the left border of the dropdown window with the dropdown button (further right) and leave cell values visible.

    Reinhard

  18. Nice update on the autofilter. We’re using LibreOffice 3.5 at work and are apart from some performance problems pretty happy with it. Unfortunately we found a quite annoying problem with the new autofilter. The Cells in the column which is filtered have some very long content, which looks always the same at the beginning and differs only at the end. Unfortunately the window for the Autofilter is too small in breadth so that when trying to filter you can’t see the whole lenght of the content, which makes it very hard to find the content you want to filter for. Any advice on how to solve that problem?

  20. I really appreciate you work and effort to improve over age old autofilter.

    I have one small issue with current implementation in v3.5. Old implementation of autofilter we can jump to the intended word in list by typing the initial few letters. That was very handy and didn’t require use of mouse. now i have to use mouse to scroll till i reach the intended filter item. Could you please add that functionality? or may be the option between old-filter v/s advance filter? Thanks.

  21. The options you gave me for a workaround don’t really work for me.. Please take yogesh’s questions into consideration.. Thanks.

  22. This is NOT anything that is helpful to me. I use auto-filter all day long, what used to take me less then a second now takes me seconds.

    Is there anyway to revert? If not, this makes LibreOffice about useless to me.
    Use case: I only want to see red from a list of 10 colors.
    Old way: click type r, select, I see what I need.
    New way: click, unselect all, scroll through list, select, click ok.
    I’m not sure how this is “better” it’s a total waste of my time, and personally not what the auto-filter is for. What you created was another full filter that was always there.

Comments are closed.