These are some of excel formulas I use often.

1. The following to to find the unique case sensitive entries in a column:

=REPT($A2,SUMPRODUCT(--EXACT($A2,$A$1:$A1))=0)

2. Formula to count frequencey in excel given two columns, the first comprising of data and the other comprising of the values whose frequency is to be counted:

=COUNTIF(A1:A1085,B1:B340)

3. Formula to highlight unequal cells in two columns for excel sheet

=NOT(A1:A100=B1:B100)

Same thing can done in LibreOffice using this:

(A1<>B1)

4. Case Sensitive Duplicate Highlighting in a Column:

In Conditional Formatting, use the following Formula:

=AND(E1<>"",SUMPRODUCT(--(EXACT(E1,$E$1:$E$10000)))>1)

5. Formula to highlight entries repeated in another column (column B):

=NOT(ISNA(VLOOKUP(A1,$B:$B,1,FALSE)))

6. Count word in an excel cell or cell range

=IF(LEN(TRIM(E2))=0,0,LEN(TRIM(E2))-LEN(SUBSTITUTE(E2," ",""))+1)

7. Count length of a specific character (e.g. '|') in a Cell

=LEN(C1)-LEN(SUBSTITUTE(C1,"|",""))

8. Compare two columns, another better formula:

=$J1:J4260<>$K1:K4260

9. Sum formula:

=SUMIF(B2:B5, "John", C2:C5)

the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

10. Split all the characters of a word in ensuing cells (i.e. Add a space after each character of a word, works only in Google Sheet)

`=REGEXEXTRACT(TO_TEXT(A1); REPT("(.)"; LEN(A1)))`