Created attachment 70455 [details] example autofilter spreadsheet When using an autofilter in Excel, if there are blank cells then there is an entry in the list of cell values labelled "(blanks)" (without the quotes ""). You can uncheck/check its checkbox to hide/unhide the rows with blanks in the same way as rows with other values. The approach in Excel is consistent and logical - the only issue with it is that there is no way to know whether "(blanks)" means cells that are blank or cells that contain the string "Contains"). When using an autofilter in Calc, there is no entry in the list of cell values for blanks. Instead there are entries separate from the list of cell values, for "Empty" and "Not Empty" (and also "Top 10"). There are several problems with this. To demonstrate: (1) Open the attached spreadsheet. (2) Click the autofilter drop-down arrow in cell B1, and click "Empty". Only the rows with blanks will be visible (and if you click the arrow again you can know that it is filtering for blank cells because the "All" checkbox is unchecked). This is OK. (3) Click the arrow again and check the box next to one of the values. That item is shown and the "empty" rows are hidden. But what if you didn't want to hide the "empty" rows?! (Note that if you open a spreadsheet saved by Excel and filter for both blanks and some other values, then it is displayed correctly, but there is incorrect behaviour when you try to change the filter). (4) Click the arrow again and check the boxes to display the other values. It will make those rows visible, AND the "empty" rows. But you didn't tell it to show the "empty" rows! (5) Click the arrow again and choose "Not Empty". Only the "Not Empty" rows are displayed, as you would expect. (6) Click the arrow again. You would expect the boxes next to each value to be checked, and the "All" box to be checked in grey. But they are all unchecked!
Also see #45681
> The approach in Excel is consistent and logical - the only issue with it is that there is no way to know whether "(blanks)" means cells that are blank or cells that contain the string "Contains") Of course that issue could be addressed easily, e.g. by formatting it differently when it means cells that are blank, or by displaying quotes around all the other entries, but not the entry for cells that are blank.
reproducible with LO 4.0.2.2 (Win7 Home, 64bit)
> The approach in Excel is consistent and logical - the only issue with it is that there is no way to know whether "(blanks)" means cells that are blank or cells that contain the string "Contains") Oops - I meant to say the string "blanks", not the string "Contains".
This bug report is rather exaggerated. Yes, AutoFilter does not allow to show both empty and somehow-valued rows. This is one of it's limitation. Probably it could be overcome by making "Empty" part of main list and giving it checkbox on it's own. But then, it does work consistently (especially since Bug 45681 is "caused" by misunderstanding of how AutoFilter window works). It is also logical - if you want to see only empty cells, then cells with value should be hidden. If anything, it does not work as Excel user might expect. And since LO aims to be MS Office replacement (hence all interoperability effort), this is probably valid enhancement request. I rephrased bug title to better say what it is really about. As of last step in "demonstration" section: I believe it is totally different issue, filed as Bug 68113.
Step (4) doesn't make sense either. Do you want to file it as a separate bug as well? I think it demonstrates that the current gui design doesn't really work...
Hi Miroslaw, I understand your position but I don't agree because I am taking an ordinary user position: Using the example provided, this ordinary user first wants to see lines with "little brother of 1" and "big brother of 3", he checks 2 boxes, result is OK. But now he wants to add "pseudonym of 5", he would naturally continue the same method and check the 3rd box isn’t? But it does not work and we cannot be sure that he will think to click “Not empty”! What do you find logical in changing the method for the last item? I am used to work around such problems, and I can understand to click on “Not empty” for my own use, but I can't imagine saying to ordinary users to read documentation for the 3rd item. Maybe this is not a bug, and rather an improvement, but really important, not only for me, but mainly for ordinary users. Such improvement can be made for instance with a box specially for “Empty” or using a shaded box for “All” when all items are checked, or any other means keeping the intuitive use of the filter. Regards,
Michel: Unfortunately, the use case you have provided has been not covered by either this bug nor Bug 45681. And yes, I can agree with this one. Checking third option also shows empty cells which is not expected if "All" were to stand for "All checkboxes above" (as I believe it does). I have filed it as new one, Bug 68406. So, to sum up: - this bug is about inability to show both empty cells and cells with selected value. It is logical (since "Empty" and "Non empty" sets are disjoint, no single element can be in both of them; if you get all elements from "Empty", you may not get a single one from "Non empty"), but not in pair with common sense (people think "I want entire Empty set and few items from Non Empty"). Also, Excel users might not expect that. - Bug 45681 is about contextual nature of Autofilter (it depends on values present after applying other filter rules). For me, not valid. - Bug 68406 is about not expected result of selecting all checkboxes on list. It can lead to impression that "All" stands for "All values" (not "All checkboxes above). For me, this is a bug and should be fixed. We will get consistent behavior of "All" button then. I will leave these three related. If anyone has better idea how "All" should really work and how handle these issues, please disagree and provide some arguments.
Thank you, you helped me to clarify my thinking To avoid adding to the confusion, I'll let you manage all these bugs, I'll just follow them and add comment if necessary
The requested enhancement can not be found in 4.2.2.1. This enhancement should be built in the next major release.
I tested this as part of bug 81577 on Mac OS X Lion 10.7.5 (11G63) using: LO Version: 4.3.1.2 Build ID: 958349dc3b25111dbca392fbc281a05559ef6848 The 'Empty tickbox' is available now, and it works as expected. The All tickbox effectively disables any selection and all tickboxes are ticked automatically. As far as I see this is a nice consistent behaviour. The problem regarding the menu item 'Not Empty' is not completely resolved. It does work, but there's no indication that its selected. It can be undone by selecting the All tickbox (and that's good). The only remaining problem is now that there is no indication that the 'Not Empty' menu item has been used. When used, all tickboxes are unmarked, while they should all except the 'Empty' tickbox should be ticked. The same situation occurs when the 'Empty' menuitem is selected. Only the empty tickbox should be ticked, but it isn't. In both situations the arrow button starting the pulldown menu is correctly indicating that a selection is in effect.
(In reply to Rob from comment #11) > I tested this as part of bug 81577 on Mac OS X Lion 10.7.5 (11G63) > using: LO Version: 4.3.1.2 > Build ID: 958349dc3b25111dbca392fbc281a05559ef6848 > > The 'Empty tickbox' is available now, and it works as expected. > > The All tickbox effectively disables any selection and all tickboxes are > ticked automatically. As far as I see this is a nice consistent behaviour. > > The problem regarding the menu item 'Not Empty' is not completely resolved. > It does work, but there's no indication that its selected. It can be undone > by selecting the All tickbox (and that's good). > > The only remaining problem is now that there is no indication that the 'Not > Empty' menu item has been used. When used, all tickboxes are unmarked, while > they should all except the 'Empty' tickbox should be ticked. > > The same situation occurs when the 'Empty' menuitem is selected. Only the > empty tickbox should be ticked, but it isn't. > > In both situations the arrow button starting the pulldown menu is correctly > indicating that a selection is in effect. I appeared to have fileds with onlt spaces in them. Therefore, it looked like an "Empty" tickbox was added. Once I emptied the field, I found to my disappointment that the tickbox disappeared.
Excel has a tickbox "{blanks)". Since Excel left and right trims all values Empty means anything containing nothing or only spaces. The side effect of this is that "aaa", " aaa", "aaa " and " aaa " are all reslut in 1 tickbox. Wether that's technical OK is debatable, but it does result in a nice user interface...
Shoud this bug be closed as a duplicate of Bug 65505 - FILTER: Auto filter checkbox missing for "empty" cells? Bug 65505 is clean, with simple steps to reproduce, and I think these two bugs are talking the same issue.
There is a typo in my description here is a correction of that sentence: The approach in Excel is consistent and logical - the only issue with it is that there is no way to know whether "(blanks)" means cells that are blank or cells that contain the string "blanks".
Submitted a patch at https://gerrit.libreoffice.org/#/c/13573/. -- Deena Francis Affiliation : Libre Data Consultancy Services (P) Ltd.
Pushed to master https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=726430db96434f64037af719ad649ab109add5d1