Ask Your Question

should we expect filtering based on cell background in ner future updates? [closed]

asked 2016-07-28 11:05:27 +0200

Prasad gravatar image

Are the developers going to introduce the functionality in libre calc where we can filter using cell background color as a condition. Currently we do it using a macro, which makes my file a bit heavy. Wondering if this functionality is in the to-do list of the devs?


edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-10 01:06:09.148327

1 Answer

Sort by » oldest newest most voted

answered 2016-07-28 12:00:57 +0200

Lupp gravatar image

updated 2016-08-03 15:44:21 +0200

The cell background you are speaking of, surely is the background colour, which presently is the only property you can edit via the 'Background' tab of the 'Format Cells...' dialogue.
For what reason do you feel sure that the colour will always be the only property under this node? There may already run a campaign to establish hatchings, colour scales or even images there. What about video clips? Feature requesters are very inventive.

Already today there are three backgrounds (colours) that may be associated with your cell:
1. The background colour set for the assigned cell style ('Default' by default).
2. A background colour applied by direct formatting (erasable by Ctrl+M).
3. A backround colour taken from a cell style applied conditionally.

Already today, again, CF is offering colour scales depending on statistics for cell ranges...

Thus my answer to your question: Hopefully "No". Hopefully never!

Questions how to sort/filter by colours come up now and then in this forum and in others as well. In cases where more details are given, it seems clear that the felt need for the feature is due to misconcepts concerning the keeping of data in spreadsheets. My advice in such cases: Never let a cell property other than its content be a relevant information. Relevant information (a selector e.g.) should always be explicit. Giving it this way you can base colouring on it via CF or with the help of the STYLE function. And: You can sort/filter, of course, based on contents.

By the way: Feature requests for LibreOffice are treated the same way as bug reports: File to .

(Editing with respect to the comment by the OQ:)
I do neither know how CF is treated in Excel nor in what way it is "translated" when Excel files are opened in Calc. Concluding from a few files I saw, and from the many automatically generated styles contained in them, it might be a bit complicated.
However, I do not expect the feature of "filter on conditionally applied colour" to get implemented in Calc.
What I said about the representation of data/properties to filter based on them should hold.
There must be a way to see the conditions applied for CF. > 'Format' > 'Conditional Formattong' > 'Manage' will hopefully work.
Now you see the 9 (?) conditions your CF is based on. Dedicate one column to the task and calculate there the number of the CF to apply based on those conditions. The condition placed first applying the first specific colour wil, if TRUE, return a 1 to the respective cell, 'NotFirstButSecond' will return 2 and so on. No condition met will result in 0 returned. You may simplify the formula based on specifica of your situation.
=(FirstCond)*1+NOT(CURRENT())*(SecondCond)*2+....+NOT(CURRENT())*(NinthCond)*9 might give a hint. If none of the conditions is met, 0 will be returned.
See attached example.

edit flag offensive delete link more


Actually the place I work for is getting data which is formatted conditionally. All the "completed" rows are green background and all the "pending" are pink, likewise there are 9 different sets. Then they apply filters in excel based on background cell. We are trying to migrate to LibreOffice provided all the functionalities could be ported. Hence, the query.

Prasad gravatar imagePrasad ( 2016-08-03 12:31:30 +0200 )edit

Agree with Lupp, filter based on the data columns that you use to determine conditional format, if not convenient from existing columns then add a helper column to use for the filter. This method would also work in Excel. You might make the content of the helper column more meaningful to the users by selecting text from the status, possibly using the CHOOSE function.

mark_t gravatar imagemark_t ( 2016-08-03 20:04:01 +0200 )edit

Question Tools

1 follower


Asked: 2016-07-28 11:05:27 +0200

Seen: 123 times

Last updated: Aug 03 '16