- 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)))