20 Essential Excel Functions Every Professional Should Know
Why Learn Excel Functions?
Excel remains one of the most widely used tools in the professional world. Yet many people still perform calculations manually or repeat the same tasks over and over. By learning just 20 core functions, you can cut repetitive work time in half — or more.
This guide covers the 20 most essential Excel functions organized by category, each with practical examples you can use right away.
Basic Aggregate Functions
1. SUM — Add Numbers
The most fundamental Excel function. Adds all numbers in a range.
=SUM(A1:A10) — Returns the total of cells A1 through A10.
2. AVERAGE — Calculate Mean
Returns the arithmetic mean of a range.
=AVERAGE(B2:B20) — Average of values in B2 through B20.
3. COUNT — Count Numbers
Counts cells that contain numeric values.
=COUNT(C1:C100) — Number of cells containing numbers.
4. COUNTA — Count Non-Empty Cells
Counts all non-empty cells regardless of data type. Useful for tallying survey responses or tracking entries.
=COUNTA(D1:D50) — Count of all non-blank cells.
5. MAX / MIN — Largest / Smallest
Find the maximum or minimum value in a range.
=MAX(E1:E30) — Largest value. =MIN(E1:E30) — Smallest value.
Text Functions
6. LEFT / RIGHT / MID — Extract Text
Pull specific characters from a string.
=LEFT(A1, 3)— First 3 characters (e.g., "Invoice" → "Inv")=RIGHT(A1, 4)— Last 4 characters (e.g., "2026-04" → "-04")=MID(A1, 5, 3)— 3 characters starting at position 5 (e.g., "ABC12345" → "234")
7. CONCATENATE / TEXTJOIN — Combine Text
Join text from multiple cells. TEXTJOIN (Excel 2019+) is the modern replacement with a delimiter option.
=CONCATENATE(A1, " ", B1)— "John" + " " + "Smith" = "John Smith"=TEXTJOIN(", ", TRUE, A1:A5)— Joins with commas, ignoring blanks
8. TRIM — Remove Extra Spaces
Strips leading, trailing, and duplicate internal spaces. Essential when cleaning imported data.
=TRIM(A1) — " John Smith " → "John Smith"
9. LEN — Count Characters
Returns the number of characters in a cell. Useful for validating character limits.
=LEN(A1) — "Excel" → 5
Conditional & Logic Functions
10. IF — Conditional Logic
Returns different values based on a condition. One of the most-used functions in all of Excel.
=IF(A1>=60, "Pass", "Fail") — Returns "Pass" if A1 is 60 or above, otherwise "Fail".
11. IFS — Multiple Conditions
Handles multiple conditions without nested IF statements (Excel 2019+).
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F") — Assigns a letter grade based on score.
12. SUMIF — Conditional Sum
Adds values only where a condition is met.
=SUMIF(B2:B100, "Sales", C2:C100) — Sums column C where column B equals "Sales".
13. COUNTIF — Conditional Count
Counts cells that match a criterion.
=COUNTIF(D2:D100, "Complete") — Counts how many cells say "Complete".
14. AND / OR — Compound Conditions
Evaluate multiple conditions at once. Most powerful when combined with IF.
=IF(AND(A1>=60, B1>=60), "Pass", "Fail")— Both scores must be 60+ to pass.=IF(OR(A1="VIP", B1>=10000), "Priority", "Standard")— VIP status or spending over 10,000 triggers priority.
Lookup & Reference Functions
15. VLOOKUP — Vertical Lookup
The classic lookup function. Searches a column and returns a value from the same row.
=VLOOKUP(A2, Sheet2!A:D, 3, FALSE) — Finds A2 in Sheet2 column A, returns the value from the 3rd column.
Note: The lookup value must be in the first column of the range. Always use FALSE for the last argument to require an exact match.
16. HLOOKUP — Horizontal Lookup
The horizontal counterpart to VLOOKUP. Searches across a row instead of down a column.
=HLOOKUP("Revenue", A1:F3, 3, FALSE) — Finds "Revenue" in the first row, returns the value from row 3.
17. XLOOKUP — The Modern Lookup
The successor to VLOOKUP that removes its biggest limitations (Microsoft 365, Excel 2021+).
=XLOOKUP(A2, Sheet2!B:B, Sheet2!D:D, "Not found") — Searches column B for A2, returns column D value, or "Not found" if missing.
Advantages over VLOOKUP: Lookup column does not need to be first, supports left-to-right and right-to-left search, built-in default value for missing results.
18. INDEX / MATCH — The Flexible Combo
A powerful pairing that works in any direction and is not limited by column position.
=INDEX(C2:C100, MATCH(A2, B2:B100, 0)) — Finds A2 in column B, returns the corresponding value from column C.
Pro tip: INDEX/MATCH is often faster than VLOOKUP on large datasets because it does not need to scan entire rows.
Date Functions
19. TODAY — Current Date
Returns the current date, updating automatically each day. Great for deadline tracking and countdown calculations.
=TODAY() — Returns today's date (e.g., 2026-04-17).
=A1 - TODAY() — Days remaining until the date in A1.
20. DATEDIF — Date Difference
Calculates the difference between two dates in years, months, or days. Useful for tenure calculations, age, and project timelines.
=DATEDIF(A1, TODAY(), "Y")— Full years between A1 and today (e.g., age).=DATEDIF(A1, B1, "M")— Months between two dates.=DATEDIF(A1, B1, "D")— Days between two dates.
Pro Tips for Real-World Use
1. Use absolute references ($). Lock cell references with $A$1 so formulas do not shift when copied. Press F4 to toggle reference types quickly.
2. Wrap lookups with IFERROR. =IFERROR(VLOOKUP(...), "N/A") — Displays a fallback value instead of an ugly #N/A error.
3. Name your ranges. Go to Formulas > Define Name to give meaningful names to frequently used ranges. Your formulas become self-documenting: =SUMIF(Department, "Sales", Revenue).
4. Prefer XLOOKUP over VLOOKUP when available. The syntax is cleaner, left-direction lookups work natively, and the built-in default parameter eliminates the need for IFERROR wrappers.
5. Combine functions for power. IF + AND, INDEX + MATCH, SUMIF + MONTH — combining functions lets you solve complex business problems with a single formula. For example: =SUMIFS(Revenue, Department, "Sales", Month, ">="&DATE(2026,1,1)) sums sales revenue from January 2026 onward.
Master these 20 functions and you will be able to automate over 80% of typical spreadsheet work. Start with the ones most relevant to your daily tasks and build from there.