Using colors as a definition of filtering makes more easier and usable the filter function in LibreOffice Calc. There are some workarounds but all of them are not user friendly.
seems to be interesting to filter by colors
Hi, Filter by color function(in Excel)used by many users in our company. It is a very easy way to categorize various data and browse through. Having this functionality will be very nice, also it is noted in Bruce Byfield's cons pros list: http://www.datamation.com/applications/how-microsoft-office-tops-libreoffice-11-features-2.html (Item 9). Best regards, Zeki
*** Bug 87308 has been marked as a duplicate of this bug. ***
*** Bug 43926 has been marked as a duplicate of this bug. ***
Hi, I care about getting this fixed, so I'm offering USD 20.00 via FreedomSponsors to the the person who'll fix it. Offer link: https://freedomsponsors.org/issue/607/filterenchancement-filter-by-colors-in-libo-calc You can also join me and throw in a few bucks there. If you fix this issue please use that site to request your payment. Best regards, Zeki
*** Bug 89582 has been marked as a duplicate of this bug. ***
*** Bug 89812 has been marked as a duplicate of this bug. ***
This is an important feature. Now I know what you're thinking. You're a developer, sorting data by formatting just seems absurd. But the answer to "Train has overturned, people are trapped" is not "Train is designed to be operated in an upright fashion". Highlighting names by background color is a natural way for people to work. They frequently do it. They will keep doing it until proper data filtering is taught in elementary school.
This is the first obvious missing feature that i noticed when comparing with the MS Excel features. I hope it will be implemented in the near future. Plays nice with contional formatting.
*** Bug 108046 has been marked as a duplicate of this bug. ***
Fortunately I had also MS Office available, just received Excel sheet, where conditional formatting was used heavily and without Excel's "Filter by color" feature finding those relevant cells would have been a real pain. So pls. add this neat feature also to LO Calc asap.
*** Bug 125190 has been marked as a duplicate of this bug. ***
*** Bug 54246 has been marked as a duplicate of this bug. ***
*** Bug 128935 has been marked as a duplicate of this bug. ***
Changing enhancement priority to 'high' since the number of people in CC is higher than 20
I'm also interested to see this feature added to Libre Office Calc.
I am very interested too.
Use Case - Highlighting duplicates using "Conditionals" and giving a background colour. Now, want to sort/filter the column according to colour.
Se ocupa mucho con regularidad!
Hi MAU, please English. You can.
Agreeing with that which others have posted, this is an important missing feature. The ability to sort and filter by color is a feature many casual users take advantage of in similar software, especially in conjunction with conditional formatting. This may be deemed absurd by advanced users and data scientists, but to a casual user it can help to visually organize the data and helps them manipulate data with greater ease. Is it so incredulous to cater to a novice user without bemoaning about how they're "doing it wrong"? In my opinion, the absurd thing is to lecture casual users about the follies of format-based filtering and sorting, as I've frequently seen users do in AskLibO. A user is not a developer. It isn't a bad thing to make software easier for them to use.
Your paper is basically the finish of all that you've learned while you're at college. You've had a very long time to begin pondering your subject, and miracle about the holes that have shown up as you've examined. This is your opportunity to do some genuine research out in the field, and distribute a genuine, scholarly bit of work. By: https://articleoftheweek.com/2020/02/14/options-of-best-banquet-halls-in-jaipur/
i have a need for this feature in my work because the clients "think" in these terms. Besides, i don't see why anyone would say this is an unnecessary feature because clearly a lot of people are waiting to have LO implement this for over 6 years.
I have a use case for sorting/filtering by text color. My large stock spreadsheet has text in a column formatted by color based on the result of "last_price - cost_per_share" where - <0 is red text for a loss ==0 black text for break even >0 green text for a profit As you can imagine, with a large number of rows filtering/sorting by text color (not background color) would be very useful and allows me to see at a glance those stocks which have been profitable.
there is an attempt to do calculations depending on cell colors by using macro / user code in: https://ask.libreoffice.org/en/question/260041/do-math-only-if-the-cell-has-particular-color/ perhaps some of that is useful here?
QuickPayPortal is one largest & world’s most famous medical bill paying online patient portal for the easiness of patients. The www.quickpayportal.com is also known as Patient Portal. quickpayportal has made online bill portion essentially less difficult. QuickPayPortal entrance is an online bill portion. https://quickpayportal.red
Is there a way to contact the sites admins about this spam (#26)? Or does the tag do it for us.
I also started to create a scientific file with "of course it can be sorted by colour" why shouldn´t it? All macro solutions I came across used the already existing value BGCOLOR. What this report is asking for is just a graphical interface within the 'sort...' function to make use of this value. A checkmark under options would be enough to start with. This could also activate another slide to set whether using hex or dezimal value or set-up your own order according to all in this document active/set BGCOLOR values. Again a defend why this background colour usage is not obsolete or wrong; Why clutter the view with an additional column that contains the same information, in addition human unreadable? Unreadable, that´s why regular people are not going to do it, never! Thanks for adding and more usability
Conditional formatting works per Cell Style and you can search right now for cell styles. Sort doesn't has the option but we could add the condition Cell Style with values listing up the available styles. Now I wonder if we really want to clutter the UI with another option to search for and filter by colors. Admittedly it's easier to quickly highlight a cell per color and one needs to get familiar with styles. But it's the more flexible choice. What I don't understand is sorting per color (or style). Makes not much sense to me.
(In reply to Heiko Tietze from comment #29) > Conditional formatting works per Cell Style and you can search right now for > cell styles. Sort doesn't has the option but we could add the condition Cell > Style with values listing up the available styles. > > Now I wonder if we really want to clutter the UI with another option to > search for and filter by colors. Admittedly it's easier to quickly highlight > a cell per color and one needs to get familiar with styles. But it's the > more flexible choice. > > What I don't understand is sorting per color (or style). Makes not much > sense to me. I guess the filter by color would work no matter how to color was set (cell style, or directly applied). No need to exclude one option here.
(In reply to Samuel Mehrbrodt (allotropia) from comment #30) > I guess the filter by color would work no matter how to color was set (cell > style, or directly applied). No need to exclude one option here. We have an option "Cell Style" in the search dialog that turns the find/replace fields into dropdowns listing all cells styles. If we allow searching for colors we need another checkbox- and this clutters the dialog further.
> If we allow searching for colors we need another checkbox- and this clutters the dialog further. Well, let's be clever. Currently, if column A contains the following records: ''' a 1 2 3 4 ''' Then If I set an autofilter, I get the following in the dropdown: ''' Sort Ascending Sort Descending Top 10 Empty Not Empty Standard Filter... (Search items...) All 1 2 3 4 (Empty) ''' Well, 1. the "(Empty)" is automatically added to the list of there are empty items in the record, and there will be no "(Empty)" in the autofilter if there is no empty records. As a result, the "Empty" and "Not Empty" autofilter items are not needed at all in my opinion. 2. Sort Ascending and Sort Descending: why not just group them into "Sort", and provide two sub-items instead? 3. A group named "Filter By:" can be added, in which the following should be put into this group: "Cell Color Is", "Top 10", and even "Empty" and "Not Empty" in case you do want to keep them. Anyway, filter by color is a very useful feature, no matter whether is is a good way to do things in spreadsheet or not. Think of the situation that someone send me a spreadsheet in which he has marked some cells in question with color and I need to locate these cells quickly. He did this simply because Excel (or even WPS) provided this feature and he is used to do things this way...
(In reply to Heiko Tietze from comment #31) the "(Empty)" is automatically added to the list of --> the "(Empty)" is automatically added to the list if
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5a4bd9bfbdb881368c202bc766f893a5c672621e tdf#76258 Filter by colors It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Sorry that still no mockup and clear input has been made. Some comments on the patch: * The color checkboxes don't get checked; there are no combination green+yellow possible * Color filter is not reflected in the values (tested with 1,2,3 in green/yellow/red and expect filter=green to show 1 checked) * Setting a value filter deletes the color filter * Standard filter has no color option (my take would be to safe this advanced filter for standard and keep autofilter as simple as possible) * Exciting the dropdown somewhere else but the items is not taken as cancel (expect nothing happens on click somewhere else but some filter is applied) and eventually crashed the application for me
Created attachment 171626 [details] Prior art Great work Samuel! To help addressing design questions raised by Heiko, here is some prior art :). Also: do you plan to add OOXML import-export support?
Thanks, yes I checked Excel and Google Docs for comparison. This is still WIP, I will consider Heikos comments. I will also add ODF and OOXML import/export (currently nothing is saved).
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e2bdd31c0231444f1ba1463bffb5656c230c3073 tdf#76258 Add ODF import/export It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
*** Bug 142078 has been marked as a duplicate of this bug. ***
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/69b376d21dfe43482f67ece137421a7487f0cbae tdf#76258 Various fixes for color filter It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/3c8b248b5a7395b174fc265e3237bd79aeb2455f tdf#76258 Add OOXML import for color filter It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5b01ad53aba40956a164500c4267990f7d2d8dd7 tdf#76258 Use correct icon size for color filter popup colors It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/518d0d8283b6d732cffa102c6b7c2fe01d1ec348 tdf#76258 Skip color items in filter dialog for now It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
If we still don't want to have some different META for this, then I'll write here some feedback for current state of this nice feature: 1. We need an opportunity to drop filter by color as it is in Excel 2. We should move color icon drop-down list to right from Autofilter window 3. We need an item like "Without fill" for filtering of cell without filling 4. I see color artifacts in bottom icon in the color list on Windows Samuel, thank you for this really very demanded feature!
(In reply to Samuel Mehrbrodt (allotropia) from comment #30) > (In reply to Heiko Tietze from comment #29) > > Conditional formatting works per Cell Style and you can search right now for > > cell styles. ... ... > > What I don't understand is sorting per color (or style). Makes not much > > sense to me. ACK > > I guess the filter by color would work no matter how to color was set (cell > style, or directly applied). No need to exclude one option here. How can this be true? From my experience I would judge that colors (or any attributes) applied based on CONDITIONAL formatting only are overlaid to the cell areas in the viewed ranges, but not assigned to the cells. What cell properties can be used to decide if an attribute is conditionally set? Is the new feature implemented based on non-published properties/methods? Is it implemented by means basicallly independent of the API structure?
Missed to mention: Concluding from old experiences, and based on a check just made a few minutes ago, I would claim that CF conditions not even are evaluated for cells "clearly" outside of the view.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/950eb673b9ad4bc380b13281af577fc8c6842007 tdf#76258 Fix color filter popup position It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b8cfea65ddcba33572e9687022de3c813711e298 tdf#76258 Add OOXML export for color filter It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/ade343ea8b498efb88473a0a3879c2711d7dd33c tdf#76258 Skip automatic colors in color filter popup It will be available in 7.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Thanks for all your feedback. I consider this bug fixed now. Please test, and file for each issue you find a separate bug (same for further enhancement requests).
This is mentioned in https://wiki.documentfoundation.org/ReleaseNotes/7.2 but a screenshot would be nice (I didn't get how this works, so couldn't add myself). Help should also be created, I'll open a new See Also bug.
(In reply to Wolfgang Jäger from comment #45) (Bump) ... > (Colors)... applied based on CONDITIONAL formatting only are overlaid to the > cell areas in the viewed ranges, but not assigned to the cells. Nobody else commented on the CF aspect, but I still am afraid users interested in filtering by colors might expect without notice that it also works with CF. (There were a few questions in the askbot to that effect.) What about this issue?
(In reply to Wolfgang Jäger from comment #52) > Nobody else commented on the CF aspect, but I still am afraid users > interested in filtering by colors might expect without notice that it also > works with CF. > (There were a few questions in the askbot to that effect.) > > What about this issue? For me it doesn't metter why a cell is colored in a certain way. If it is colored, then i want it to be included in the filtering process. Just my two cents.
(In reply to prempa from comment #53) > (In reply to Wolfgang Jäger from comment #52) > > > Nobody else commented on the CF aspect, ... > > For me it doesn't metter why a cell is colored in a certain way. > If it is colored, then i want it to be included in the filtering process. > Just my two cents. That's exactly what I was afraid of. Most users won't distinguish between the cell (the object) and the area in the view used to display it. However, currently a cell being shown with some specific background color due to CF does not get this color assigned as its .CellBackColor, and for cells outside the view the CF conditions aren't even evaluated. As far as I can tell having studied the issue using the API and testing with related effects, filtering by cell attributes shown due to ConditionalFormatting can't work. I'm hoping for a comment by the patch-committer (Samuel Mehrbrodt) telling me if I am right, or if there was a way to include coloring by CF.
(In reply to Wolfgang Jäger from comment #54) > (In reply to prempa from comment #53) > > (In reply to Wolfgang Jäger from comment #52) > > > > > Nobody else commented on the CF aspect, ... > > > > For me it doesn't metter why a cell is colored in a certain way. > > If it is colored, then i want it to be included in the filtering process. > > Just my two cents. > > That's exactly what I was afraid of. > > Most users won't distinguish between the cell (the object) and the area in > the view used to display it. However, currently a cell being shown with some > specific background color due to CF does not get this color assigned as its > .CellBackColor, and for cells outside the view the CF conditions aren't even > evaluated. > > As far as I can tell having studied the issue using the API and testing with > related effects, filtering by cell attributes shown due to > ConditionalFormatting can't work. > > I'm hoping for a comment by the patch-committer (Samuel Mehrbrodt) telling > me if I am right, or if there was a way to include coloring by CF. So I just tested this, and colors set by conditional formatting are ignored by the color filter. I now think this is the correct behavior, since the original background/text color is still there, just overlaid by conditional formatting, as you explained. Please keep this bug closed, and if there are issues, open a new bugreport.
(In reply to Samuel Mehrbrodt (allotropia) from comment #55) > So I just tested this, and colors set by conditional formatting are ignored > by the color filter. I now think this is the correct behavior, since the > original background/text color is still there, just overlaid by conditional > formatting, as you explained. I think if a user see a color on screen, want the filter to operate on that color. It doesn't matter how many color are in background: if on screen a cell is red and I ask a filter by "red color" then I expect the filter to act on that red I see on screen.
(In reply to Samuel Mehrbrodt (allotropia) from comment #55) | So I just tested this, and colors set by conditional formatting are ignored > by the color filter. I now think this is the correct behavior, since the > original background/text color is still there, just overlaid by conditional > formatting, as you explained. > > Please keep this bug closed, and if there are issues, open a new bugreport. But that is not the way color filter works in Excel. For better compatibility the criterion should be the actual, effective color of the cell.
The most important thing however would be similar behavior than in Excel to avoid interoperability problems.
(In reply to Regina Henschel from comment #57) > > But that is not the way color filter works in Excel. For better > compatibility the criterion should be the actual, effective color of the > cell. I agree. This would be useful for a better understanding by the users coming from Excel.
(In reply to Regina Henschel from comment #57) > But that is not the way color filter works in Excel. For better > compatibility the criterion should be the actual, effective color of the > cell. Ok, please file a new bug for that.
I think the criteria of filtering the actual color on the screen is not because of compatibility with Excel. It is because filtering by a color you don't see is non-sense.
(In reply to luis prox from comment #61) > I think the criteria of filtering the actual color on the screen is not > because of compatibility with Excel. It is because filtering by a color you > don't see is non-sense. Neither the one way nor the other you can avoid nonsene. Someone having set arbitrary cells explitly to a "red" color attribute may expect this to be a kind of data he wants to filter for - independent of what (other) color may be *temporarily* overlaid conditionally based on different data. The nonsense is the usage of colors as a data surrogate itself. And the only reason to consider its implementation is that users coming from Excel seem to be encouraged to stick to that bad idea.
Well, I'm going to chime in here. I've been following this bug but remained silent. Suppose i have a spreadsheet where one cell in each row is colored based on criteria from a variety of cells in that row and other rows. Wouldn't it make sense to be able to sort based on cell color? As a user I would expect this sort to be on visible color and not some underlying cell color attribute which i might have no knowledge of. If it doesn't work that way then I would expect a plethora of bugs to be filed as users don't see what they are certain it should be. It reminds me of the macro security feature where you have links to external data that you want to update automatically and it doesn't work even when you select automatic update, so the end result is those users just setting macro security as low as possible. The right configuration is to set another option in another part of the menu and then it will work (mostly) as expected - but that's a whole different story The law of unintended consequences at work.
(In reply to Eric from comment #63) > ... > Suppose i have a spreadsheet where one cell in each row is colored based on > criteria from a variety of cells in that row and other rows. Wouldn't it > make sense to be able to sort based on cell color? As a user I would expect > this sort to be on visible color and not some underlying cell color > attribute which i might have no knowledge of. That's what I expected interested users would excpect... However, in one or another way you should have knowledge of what attributes you *explicitly* applied to a cell (or to the style you assigned to it). Anyway: Conditionally overlaid attributes are neither evaluated for the complete range which got set the CF, nor are they assigned to the respective cells. The conditions are only evaluated for cells in the view, and only applied to the area of the view (cell grid) where also the content or value is shown. This is a reasonable and efficient way to do what's actually needed. I'm afraid, a filtering by CF-dependent colors would require a very deep re-design of all the CF related code, and additional memory to keep the results all the time to ensure correct working of filter-sensitive functions and routines. But I'm not a developer, of course
Verified in: Version: 7.2.0.0.alpha1+ (x64) / LibreOffice Community Build ID: aa9cb8e14749e7fb7a83b55a2bb095501f731a18 CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win Locale: hu-HU (hu_HU); UI: hu-HU Calc: threaded
(In reply to Samuel Mehrbrodt (allotropia) from comment #55) > (In reply to Wolfgang Jäger from comment #54) > > (In reply to prempa from comment #53) > > > (In reply to Wolfgang Jäger from comment #52) > > > > > > > Nobody else commented on the CF aspect, ... > > > > > > For me it doesn't metter why a cell is colored in a certain way. > > > If it is colored, then i want it to be included in the filtering process. > > > Just my two cents. > > > > That's exactly what I was afraid of. > > > > Most users won't distinguish between the cell (the object) and the area in > > the view used to display it. However, currently a cell being shown with some > > specific background color due to CF does not get this color assigned as its > > .CellBackColor, and for cells outside the view the CF conditions aren't even > > evaluated. > > > > As far as I can tell having studied the issue using the API and testing with > > related effects, filtering by cell attributes shown due to > > ConditionalFormatting can't work. > > > > I'm hoping for a comment by the patch-committer (Samuel Mehrbrodt) telling > > me if I am right, or if there was a way to include coloring by CF. > > So I just tested this, and colors set by conditional formatting are ignored > by the color filter. I now think this is the correct behavior, since the > original background/text color is still there, just overlaid by conditional > formatting, as you explained. > > Please keep this bug closed, and if there are issues, open a new bugreport. Bug 144662 opened
> So I just tested this, and colors set by conditional formatting are ignored > by the color filter. I now think this is the correct behavior, since the > original background/text color is still there, just overlaid by conditional > formatting, as you explained. > > Please keep this bug closed, and if there are issues, open a new bugreport. Bug 144662 opened
Hi, I have tried the new version of Libreoffice, and i have seen that this enhancement fix the issue. Very thaks and greetings!
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d148ed642f2aef2c2cc8615296cc648249a4878d tdf#76258 Fix xlsx export of color filter colors It will be available in 7.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Samuel Mehrbrodt committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/9c2bf889bdd8d83b58996b8fdc6727fec75cdcd3 tdf#76258 Fix xlsx export of color filter colors It will be available in 7.2.3. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
This is an important feature. https://wikiguide.tips/gmail-sign-up
I have learned a lot of lessons after reading this article
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6ec2d6461fe9c41025ce2bde13ddeb02acb01cb3 tdf#76258: sc: Add UITest It will be available in 7.6.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.