Ask Your Question
0

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

asked 2017-11-01 14:43:45 +0200

ndaty gravatar image

updated 2020-08-07 12:55:11 +0200

Libreoffice 6.4.5 Ubuntu 20.04 LTS

edit retag flag offensive close merge delete

Comments

This is not a good title. Please change it so that it summarizes the question. Then provide more details in the question body. Is this cell background color or text color, and are styles involved? Also, give an example of how the colors should be sorted. What colors come first?

Jim K gravatar imageJim K ( 2017-11-01 15:08:20 +0200 )edit

The question seems clear enough to me. Excel has had this for some time now...after setting up auto-data filters to column tops, on pulling down any of those column-heading menus, one will see an obvious "sort by color..." option that's not in the same place in Calc, if it exists anywhere. It lets the user filter cells by color, of course, which is a great visual method I use for tagging rows I want to follow up on. I'll keep looking.

Dave S gravatar imageDave S ( 2018-03-21 04:49:30 +0200 )edit

@Dave S: Why don't you open a new question and add a link to this one? First, take a look at guidelines for asking. Recognize that many of us do not use Excel, so be sure to explain exactly what is needed.

Jim K gravatar imageJim K ( 2018-03-21 18:23:37 +0200 )edit

Question reformulated

ndaty gravatar imagendaty ( 2020-08-07 12:55:42 +0200 )edit

The question seems clear enough to me

Question reformulated

The question is formulated bad in the sense that all the question is fit into the title, and the description is almost empty, while it should be like this:

  • title:

e.g. Sort or filter by cell background color in Libreoffice Calc (so it gives a brief idea what it is about)

  • description:

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? I'm using Libreoffice 6.4.5 on Ubuntu 20.04 LTS. Thanks.

Just a style issue, not a big deal :-)

And no, there's no such feature yet... tdf#76258, tdf#95520.

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-07 13:19:46 +0200 )edit

If there was such a feature it would encourage users to code relevant information using CellBackColor instead of explicit cell content. Such ways of "coding" are bad and dangerous for many reasons.
In addition users filtering by color would very likely also expect the filter to work with conditionally applied (CF) colors not understanding the difference. If I am asked I will opt against such a feature.

Lupp gravatar imageLupp ( 2020-08-07 14:28:14 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-06-01 15:10:31 +0200

hjek gravatar image

updated 2018-06-01 15:12:50 +0200

Cell formatting, such as font and colour, should only change the presentation of a cell, not its underlying value. Imagine if calculations involving cell values (such as sorting) could depend on formatting: Changing the font (or any part of the formatting) of a cell in some spreadsheet could actually break a calculation if a particular formula depended on certain values being in Comic Sans size 5 with orange background with a cell width of 0.1.

I am somewhat frightened that a question like this is even asked (and asked often), as that would mean that if this functionality was available, people would indeed use it. You can get the colour of a cell using macros, but please don't.

Instead of embedding information within the formatting, you should store it as a separate value. For example, if you have a column called fruit where one cell has the value apple, and you want to record that the apple is green, then do not just apply a green colour to the text apple. Instead create a colour column where the cell on the same row as apple has the value green.

edit flag offensive delete link more

Comments

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

w2016 gravatar imagew2016 ( 2019-04-05 13:05:55 +0200 )edit

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.

errerr gravatar imageerrerr ( 2020-04-25 21:02:25 +0200 )edit

I'm still wondering why the feature "filter by cell background color" is not yet implemented in Calc LibreOffice... such a basic feature

ndaty gravatar imagendaty ( 2020-08-07 12:58:00 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-07 13:43:50 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-08-07 14:48:49 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-07 14:51:02 +0200 )edit

Quoting @Mike Kaganski:

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

Lupp gravatar imageLupp ( 2020-08-07 15:11:16 +0200 )edit

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

isgoren gravatar imageisgoren ( 2020-09-24 21:16:48 +0200 )edit
0

answered 2020-08-07 15:30:24 +0200

keme gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-11-01 14:43:45 +0200

Seen: 6,739 times

Last updated: Aug 07