How to do it in Excel
Short answers to everyday tasks. Each one gives the formula first, then shows it working in a sheet you can change, with the common variations and what to watch out for.
Counting
- How to count cells with text in Excel:
=COUNTIF(A2:A7, "*") - How to count unique values in Excel:
=COUNTA(UNIQUE(A2:A7)) - How to find duplicates in Excel with a formula:
=COUNTIF($A$2:$A$7, A2)>1 - How to remove duplicates in Excel with a formula:
=UNIQUE(A2:A7) - How to count cells between two numbers in Excel:
=COUNTIFS(A2:A6, ">=60", A2:A6, "<=90") - How to find the most common value in Excel:
=INDEX(A2:A7, MATCH(MAX(COUNTIF(A2:A7, A2:A7)), COUNTIF(A2:A7, A2:A7), 0)) - How to number rows automatically in Excel:
=SEQUENCE(COUNTA(B2:B4))
Sums and averages
- How to calculate a running total in Excel:
=SUM($B$2:B2) - How to sum values between two dates in Excel:
=SUMIFS(B2:B5, A2:A5, ">="&D2, A2:A5, "<="&E2) - How to use SUMIF with OR (this or that) in Excel:
=SUMIF(A2:A5, "North", B2:B5)+SUMIF(A2:A5, "South", B2:B5) - How to calculate a weighted average in Excel:
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4) - How to calculate a median with a condition in Excel:
=MEDIAN(FILTER(B2:B6, A2:A6="Red"))
Lookups
- How to look up a value with two criteria in Excel:
=XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="L"), C2:C5) - How to look up a value to the left in Excel:
=XLOOKUP("P-2", B2:B4, A2:A4) - How to compare two columns in Excel:
=IF(COUNTIF($B$2:$B$5, A2)>0, "In both", "Only in A")
Text
- How to combine first and last name in Excel:
=A2&" "&B2 - How to split first and last name in Excel:
=TEXTBEFORE(A2, " ") - How to check if a cell contains specific text in Excel:
=ISNUMBER(SEARCH("urgent", A2)) - How to remove extra spaces in Excel:
=TRIM(A2) - How to capitalize the first letter in Excel:
=PROPER(A3)
Dates
- How to calculate age from a date of birth in Excel:
=DATEDIF(B2, C2, "Y") - How to get the day of the week from a date in Excel:
=TEXT(A2, "dddd")
Percentages and rounding
- How to calculate a percentage of a total in Excel:
=B2/SUM($B$2:$B$5) - How to calculate percentage change in Excel:
=(C2-B2)/B2 - How to round to the nearest 5 in Excel:
=MROUND(A2, 5)