Ask Your Question

Filtering data based on "Condition" created during conditional formatting

asked 2020-12-03 10:18:40 +0100

SerafimF1 gravatar image

Hello everyone,

I'm having trouble finding a way to mark all cells with a certain condition, then filter for them. Basically, I need to mark all cells with a certain symbol ("/"), then filter so that I can only see those cells. The reason is that I need to do a text to columns iteration on a different cell in that same row.

Using conditional formatting, I can do various things to all cells containing "/". I can turn them green, red, whatever.

Is there a way to then filter them, i.e. to filter only the cells marked "good". Alternatively, is there a way to filter for all cells containing a specific symbol, which would circumvent this additional step?

Thank you!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-12-03 10:43:01 +0100

Opaque gravatar image

updated 2020-12-03 10:43:59 +0100


you seem to mix things - "Filtering" and "Conditional Formatting" are two different things. "Filters" are applied to content of cells and not to properties of cells. In turn "Conditional Formatting" changes properties of cells.

If you want to filter all rows containing /:

  • Select the range you want to filter
  • Data -> More Filters -> Standard Filter...
    Field name column name or header (e.g Data in screenshot below)
    Condition Contains
    Value /
  • OK

image description

Hope that helps.

edit flag offensive delete link more


This helps, thank you! I understood the difference between conditions and content for filters, but thought that this additional step (change condition, then filter by condition) was the only way to do this. Worked perfectly though, thank you!

SerafimF1 gravatar imageSerafimF1 ( 2020-12-03 10:45:31 +0100 )edit

If the answers works for you, please consider to click the check mark (✔) next to the answer. Thanks in advance ...

Opaque gravatar imageOpaque ( 2020-12-03 10:47:48 +0100 )edit

Quoting @Opaque: " In turn 'Conditional Formatting' changes properties of cells."
That's not quite correct. CF only overlays the respective attributes to the cell's area in the view. Cell properties remain unchanged. This is the relevant difference between the usage of ConditionalFormatting and the usage of the STYLE() function - if applicable in the given case.
There are also CF modes (AllCells) producing results in the view for which simply no cell attributes exist.

See: C:\fakepath\compareAttributeByCFwithByStyle1.ods

Lupp gravatar imageLupp ( 2020-12-03 12:19:42 +0100 )edit

Thanks for correcting my imprecise use of changes.

Opaque gravatar imageOpaque ( 2020-12-03 12:35:58 +0100 )edit

answered 2020-12-03 15:20:05 +0100

m.a.riosv gravatar image

Creating an advanced filter, with the same conditions as the CF, adding to it the filter conditions, could help to get what it's looking for.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-12-03 10:18:40 +0100

Seen: 31 times

Last updated: Dec 03 '20