Hi! Is there a feature "Sort or filter by cell background color" in Libreoffice Calc? If not, is there a hack to filter cell by background color? Thanks. Edited

English
#10

I basically do that when I create spreadsheets (having extra columns for sorting), but I regularly receive files from MS-Office users who tell me “just sort by the color” and it is tiresome explaining every time that my tool cannot do that. They are not trying to annoy me, they just use their tool the way they always do.

Please allow me to quote somebody else:
[…] 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.

Source:
https://bugs.documentfoundation.org/show_bug.cgi?id=76258#c8

#11

This is the dumbest answer to a legitimate question. Don’t be so frightened, Excel has been doing it successfully for a while and since Libreoffice Calc is the poor man’s Excel, so folks, myself included, are looking for this time saving feature.

#12

I’m still wondering why the feature “filter by cell background color” is not yet implemented in Calc LibreOffice… such a basic feature

#13

and since Libreoffice Calc is the poor man’s Excel

OMG. How people are misled… (irrespective to my opinion on this request, which I personally don’t need, but suppose to be implemented sooner or later anyway).

#14

minus 10 for implementation!
The feature is neither basical nor actually useful. To the contrary it would cause problems and errors in different ways, disscussed over and over in real formus.
Nowadays I waste time again and again due to the habit of users to prefer stubborn and silly date formats - and often not being aware of the needs of text-based communication.
I really don’t need users not knowing of the fact that often hundreds if not thousands of colors are not distinguishable to the human eye or shown very differently on different screens, that there are no reliable ways to define a term like “red” beyond the usage in a palette, and that palettes cannot be guarenteed to be long-term stable let allone to be compatible across applications - but expecting me to waste my remaining time with helping them out of the mess they made.

#15

@Lupp: agree. However, I am sure that in practice someone will implement that. Too much wanted by many.

#16

Quoting @mikekaganski:

…I’m sure … someone will implement that …

As an incurable optimist I don’t feel quite sure, but I’m afraid, I’m wrong.
This is one of the cases where my suspicion tells me that MS-And-All developers also know how bad and dangerous such a feature is. If they actually implemented it, it must have been due to being forced by a mangement decision based on one truth: The only reason for anybody to buy/use our software is its incompatibility. They cannot take the risk to be compatible. But they may simulate an approach.

(I wouldn’t search for it now, but actually I once wrote a bit of Basic code to help somebody to get a rough binning of our 2^24 colors under the labels “red”, “yellow”, and something else. I told him to exclusively use my help to find his cell ranges for re-coding the information into actual data. That was an interesting piece of “research and use” on my level of knowledge about colors, but I won’t do it a second time.)

#17

Here’s a real life use case of why sort by color is useful. I work with software that creates reports. When I import my tens of thousands of rows of data, the software generates a report with the same data, and color codes the rows that failed to import. THOUSANDS of them. It WOULD be very USEFUL to just sort by color, so all the thousands and thousands of rows that errored out can float up so I can deal with them quickly. Instead, I have to literally waste half a day trying to do this other ways. Being an academic snob about how people use data, shouldn’t impact real life applications. I agree that information shouldn’t be contained in formatting… however WHEN IT IS… you need a tool that can handle it. I’d rather have a robust method of sorting by colors built in, than having to figure one out every time I encounter this problem. For those of you that know better, don’t do it. But when you have to work on someone else’s data, sometimes you don’t have the choice.

#18

@Lupp you can’t see the utility of combing Conditional Formatting with Sort by Format? What are you missing? It makes for an extremely easy UX to find duplicates – for example – with Conditional Formatting and then also sort those duplicates to the top for analysis by sorting by the same format parameter you just used in conditional formatting. This simple combination of features allows users to not learn what the F this “=VLOOKUP(A22,E:F,1,0)” means. I just want to easily “tell” Calc, “highlight all the dupes black; now bring all the cells highlighted black to the top.” Do you have an easy way to do this that already exists because I’ll shut up about Sort by Format if there is.

#19

There is a simple reason why libreoffice needs the filter by color option. Because google sheets and microsoft excel do it.

Increasingly I’m working with others in groups, and by so doing am being herded into the google documents realm because it’s “free.” When I want to download a spreadsheet shared from someone else, and the entire thing is dependent on this filtering by color capability because that’s the way THEY built it, it immediately renders LibreOffice useless to me, adding more pressure to have to drop libreoffice and be stuck with google/Microsoft.

It may not be a purists dream codewise, but Google, Microsoft, and the rest of the millions and millions of their users don’t care about that. They care about what it does and the way they are now accustomed and want to do it.

PLEASE adapt or increasingly I’ll be forced off the LibreOffice platform!

OR, better yet, create a cloud-based libreoffice platform to compete head on, if only there were a way to fund it!

#20

MS and Google will never be fully compatible with one another,and none of them can accept a fully compatible FLOSS competitor. Whatever nonsense LibO might implement in the bad traditions cherished by MS, MS will have the power to create new incompatibilities.
Swallow the baits if you want. …
By the way: This is a user forum. You shouldn’t expect to influence the development this ways. If you want to request a feature, or to support a request by someone else, you need to search https://bugs.documentfoundation.org for related topics.
Once MS and Google have succeeeded to force everybody into their clouds and under their “services”, hooking will be cancelled, and engulfing will be the maxim.

#21

Lupp, thanks for the note. Ultimately, you and I are in agreement. The question then becomes, how to prevent it from happening.

#22

(Slighly off topic)
We don’t know the future, and the force isn’t with me when trying to influence it.
Politics globally should ensure their priority over economism and the related tendency of marginalizing well-considered concepts. Independent of the “system” they seem to fail. It may be another case of the evil GameOfPower. Historic powers also failed in the end…
Anyway: One day everything now actually happening will be part of what preceeded the glorious age to come. “It will be have been good.” We also are successors to failing powers.

#23

Using color to mark up data for significance makes some sense.

Using such coloration as a condition for sort, filtering or summaries is a bad idea, because color is not an exact value.

Color is a visual clue. Vision varies from person to person, depends on lighting situation (white balance and such) and may be perceived differently depending on previous eye exposure. Also, color is a product of 3 or 4 distinct magnitudes (Luminance/hue/saturation, 3 additive primaries or 4 subtractive primaries) which can sometimes yield similar coloration (to our perception) from very different magnitudes.

In other words: Creating a solution which makes sense to the average user is a fairly overwhelming task. Also, because the input is not deterministic, it is not good input for a typical spreadsheet task. With limited developer resources available, this is not likely to be given priority.

Yes, there is a hack, as I have seen mentioned. No, I will not search for it and test it for you. Do a search. You are likely to find ut.

If you must do things the Excel way (be it out of own desire or client demand), at best use Excel.

#24

“color is not an exact value” is not true. On your computer it definitely is very exact (most common is a RGB value).

#25

On your computer it [color] definitely is very exact

The RGB value, its rendering on screen/print (after translation to CMYK) and our perception of it are three contexts which do not have a predetermined, exact congruence. Marking something in red and then searching for red is not likely to succeed, simply because rendering technology and our perception is not able to match the computer’s theoretical color resolution and repeatability.

#B080A7 is certainly an exact value. I maintain that Mauve is not. You may disagree.

#26

I agree that people don’t always perceive colors the same way, absolutely correct. But that is not relevant to the question of being able to sort by color in LibreOffice. We don’t speak to Office (yet) but give it specific commands. For example: put all cells with background color #XXXXX on top. I really don’t understand the aversion in this topic to making this possible :slight_smile:

#27

Of course, it is simple for LibreOffice Calc development, to implement this by a few lines of code, and in fact it’s simple for a user to get the intend ed result based on a helper function. This would not change the fact thate there are 16777216 “different” colors, and no reasonable way to descibe their “degree of similarity” or any kind of “natural order” to a certain level of rationality.
If we start talking of such granular features like put all cells with background color #XXXXX on top any reasonable structure in sheets will be lost, and the next few hundred feature requests will follow.
Find all cells of same CellBackColor as the one having the focus. is OK.

But: Software handles colors by palettes , and palettes aren’t specified by any common standard.

#28

This statement makes no sense to me. “Using such coloration as a condition for sort, filtering or summaries is a bad idea, because color is not an exact value.” In my case I use Format—>Conditional—>Condition—>Cell value—>is duplicate—>Apply Style—>Error to mark cells with duplicate content. What is the point of having a function to set a background color for cells and then say there’s no reason to use the color as a filter or sort function? Having identified all cells containing a specific color I need to somehow copy the rows with cells having that specific color, and only those rows, to another sheet. Manually copying thousands of rows with duplicate content defeats the value of being able to identify them.

#29

Even if Calc had a filter based on cell attributes it couldn’t filter based on conditionally overlaid attributes because they don’t get asigned to the cells but only applied to the cell areas in the view.
This is something you cannot change “just so”.
I hadn’t any Excel at hand for many years now, but I would assume Excel also doesn’t filter based on conditional formatting. That’s for efficiency reasons.
However, I can’t know. Excel seems to not be software, but just a bag of features. This “programming paradigm” has lots of disadvantages, but adding a feature is the one thing that’s rather simple this way.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.