Ask Your Question

How to filter rows using row color in libre calc? [closed]

asked 2015-02-15 13:12:10 +0100

Karthik VAS gravatar image

updated 2015-02-15 13:14:32 +0100


i am using libre calc for past 1 week. i use spreadsheets a lot for my work. and filter optioon is the key for my workings. but in filter i find no way to filter my results using the row colors.

can anyone please help me out regarding the filter of rows using colors?

thanks in advance

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-15 21:28:43.975882


@Karthik VAS - This reads as if you used another spreadsheet software before. I won't know that one. Maybe it offers an option to filter by colours, LibreOffice Calc does not, as far as I know. There are reasons. If you are interested in the reasons I see, and in ways to do filtering in connection with colours in Calc, please tell me.

Lupp gravatar imageLupp ( 2015-02-15 14:59:43 +0100 )edit

From this vid - starting from a little past 1:00 - the appeal of 'filter by color' should be clear.

I'm thinking the developers here are overthinking this. As for my own use, especially during tax season, I prepare rough spreadsheeted records and want to flag various items to follow up on, or to move elsewhere. Fill colors work great for this - but I need to filter them later...

Dave S gravatar imageDave S ( 2018-03-21 05:09:54 +0100 )edit

I spent the time to watch that clip just to learn that it shows a few colored cells, but doesn't mention filtering based on colors. If I missed something, please explain!
What actually is shown is sorting ascending and an automatized version of filtering for which you apply the tools in Calc via Data > Sort ascending and Data > AutoFilter respectively.
If you are actually interested in filtering based on color, please read first my old answer below and the post linked-in there. -->

Lupp gravatar imageLupp ( 2018-03-21 16:13:15 +0100 )edit

I used up 10 min then to find out that you rather wanted to point to this clip. At about 3 min there is demonstrated filtering by color. This is only an additional way in the example to filter for Yes|No|Maybe contents.
The design of that sheet was not that bad to code the respective information only by color.
LibreOffice doesn't support this mistake at all.

Lupp gravatar imageLupp ( 2018-03-21 16:23:46 +0100 )edit

Hi! There is a solution. Make a special macro function CELL_BACKCOLOR(SHEET;ROW;COLUMN) with the help of this thread It's qute easy. Here is a sample of usage. After opening a saved document or after changing anything related, these function need a hard recalculation (Ctrl+Shift+F9).

DYJ gravatar imageDYJ ( 2018-08-05 12:59:15 +0100 )edit

This is an urgent advice to not use the "introspective" functions as a means for the functionality of spreadsheets. Exclusively use them to support a redesign of your sheets dedicating cells (coiumns) to contain the respective information explicitly. "Villeroy", the author of the functions you pointed to, would also strongly advise so.

Lupp gravatar imageLupp ( 2018-08-05 15:03:18 +0100 )edit

3 Answers

Sort by » oldest newest most voted

answered 2015-02-15 14:42:08 +0100

m.a.riosv gravatar image
edit flag offensive delete link more


JohnSUN gravatar imageJohnSUN ( 2015-02-15 14:46:57 +0100 )edit

answered 2015-02-15 15:38:41 +0100

pierre-yves samyn gravatar image

Hi - The macro solution is probably the most efficient. But I can suggest a workaround...

If the color is applied by styles, and if the number of styles is limited, you can use "unused" properties of styles to differentiate them.


In the example attached the format of the styles are different.

  • Yellow: negative number in yellow
  • Orange: unprotected style
  • Red: the thousands separator is enabled
  • Green: negative numbers are in brackets

These attributes can be tested by the CELL function with a formula such:

edit flag offensive delete link more

answered 2015-02-15 15:30:14 +0100

Lupp gravatar image

updated 2015-02-15 15:34:37 +0100

Another related topic is found here in the forum. The first paragraph of the first post there is explaining in short the need for such features as an indicator of bad design in a spreadsheet. Villeroy, the author of that post, also supplies BASIC code helping in the rectification, and advice concerning its usage.

I attach an example handling the colours and the filtering by an additional explicit information:ask46352FilteringByColour001.ods

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-02-15 13:12:10 +0100

Seen: 28,877 times

Last updated: Feb 15 '15