How can I achieve this formatting disregarding the capital case of first letter? i.e. cell value is duplicate with ignore-case?
Say, you want to apply the formatting to column A. Use Formula is condition.
You may use formula like COUNTIF($A:$A; A1)>1. The COUNTIF comparison is case-insensitive (at least in my testing), which would disregard case of all characters.
But if you want to specifically disregard case of only the first character, then a computation-intensive solution like SUMPRODUCT(REPLACE($A:$A;1;1;UPPER(LEFT($A:$A;1)));REPLACE(A1;1;1;UPPER(LEFT(A1;1)))) > 1 could do.
Possibly @erAck could give a better advise 
“cell value is duplicate with ignore-case” is perfectly matched with the COUNTIF()>1 example given.
But, caveat, COUNTIF() follows the wildcards/regex settings under Tools->Options->Calc->Calculate so may not produce the desired result if such meta characters are contained in the Criteria argument. To eliminate that this would be better suited: =SUMPRODUCT(A:A=A1)>1
Thank you, Mike. This worked. But the point is, again - usability and UX. I don’t think I should be writing this kind of formulas in order to achieve simple thing. Can you phrase in words what this formula is doing? looks like takes some range… but is the range $A to $A? and what is second argument for? didn’t get it.
I don’t even want to discuss if a train of thought like “whatever I am doing is a simple thing that must be doable without understanding application’s basics” is a correct one… and if that train of thought should be the same for accountant, a mathematician, a PhD of statistics, and a string theory specialist.
The formula says that: “count how many cells in the entire fixed column A (which is the range defined by $A:$A) are equal case-insensitively to current cell (which is what relative reference A1 stands for in the conditional formatting range that starts at A1); check if that count is greater than 1, which means current cell has another duplicating value somewhere”.
Clear. The only one confusing point was A1 as it kind of refers to particular slot. So A1 gets the value of every single cell in iteration.
Mike, can you please enhance your answer by taking this: All works fine, but when I copy (say) few rows of one column from file A, and paste that into file B:
- By using Paste, highlighting doesn’t work (even if later I align the formatting of those pasted rows with the formatting of above ones);
- By using Paste Special → [unformatted] text, it works good (in both options).
When you paste cells somewhere, the cells are pasted with all the formatting they had in the original location. This means, that they also bring their conditional formatting with them, including when they don’t have one. When you paste unformatted text, it naturally takes formatting of the target cells.
If you need to “align the formatting of those pasted rows”, you might use Clone Formatting tool, or you might need to adjust conditional formatting range using its management dialog. What procedure you used when “aligned formatting” is untold