[SOLVED!] How to conditional format duplicate cells (case insensitive)?

[librelibre]

In Calc, I need to conditionally format duplicate cells but ignoring case (case insensitive). But so far, I couldn’t get it to work. Duplicate cells are indeed highlighted, but duplicate cells with different upper/lower-case letters are not recognized unfortunately.

If possible, I’d like to use the built-in predefined “is duplicate” function instead of a custom formula, but will settle for the latter if that’s really the only solution.

I also tried to change options > calc > calculate > case insensitive but that had no effect unfortunately.

I have looked everywhere but couldn’t find the answer. Many thanks for your help.

EDIT: Thanks to @Villeroy and @JohnSUN - you both solved the problem with your COUNTIF formula. Apparently COUNTIF is case insensitive whereas the built-in “is duplicate” function cannot be made insensitive (which I would have preferred) ? Unfortunately I can only mark 1 answer as correct apparently? But you both solved my problem. Thank you very much!

[Zizi64]

Please upload your ODF type sample file here.

[JohnSUN]

May be so?

[librelibre]

@Zizi64: Please upload your ODF type sample file here.

Thanks for helping. What in my question was not clear? Please help me clarify by pointing to the specific parts you did not understand and what was unclear to you. Thank you.

[Villeroy]

CF_89322.ods (17.1 KB)

[JohnSUN]

@librelibre

What in my question was not clear? Please help me clarify by pointing to the specific parts you did not understand and what was unclear to you.

It is not clear why anyone who wants to answer a question should spend more than two-thirds of the time preparing test data. It’s much easier to take an already prepared dataset and return a ready-made solution, isn’t it? Don’t take it personally - most of the questioners are abusing our time. I hope that next time you will take this wish into account - let’s work on a solution together

[Zizi64]

@librelibre

What in my question was not clear? Please help me clarify by pointing to the specific parts you did not understand and what was unclear to you.

A sample file can contain ALL of the information about the problem, but a description and an image only partially can do it.
In many cases the questioners can solve the problem, when he/she prepares the sample file.

[Villeroy]

Spreadsheets are case-insensitive by default.

[sokol92]

@Villeroy

case-insensitive by default.

Unfortunately, this is not the case in current versions of LO.
With a “clean” profile, open Calc and type

="a"="A"

P.S. Battling artificial intelligence (see previous versions of this answer).