Excel has over 450 functions. Most professionals use fewer than 25 regularly. Here's a practical reference covering the formulas that appear most often in real spreadsheets — with plain-English explanations that skip the formal syntax and focus on what you actually need to know.
Lookup Functions
VLOOKUP — Looks up a value in the first column of a range and returns a corresponding value from a column to the right. The "V" is for vertical. Classic use: find a customer ID in column A, return their name from column B. Limitation: can only look to the right; breaks if you insert columns.
XLOOKUP — The modern replacement for VLOOKUP. Finds a value in one range and returns a value from another range. Works in any direction. Simpler syntax. Returns a default value if not found instead of an error. Available in Microsoft 365 and Excel 2021+.
INDEX/MATCH — A two-function combination: MATCH finds the row position of a value, INDEX returns the value at that position. Together they replicate VLOOKUP but without the left-to-right limitation. Still widely used in older Excel versions where XLOOKUP isn't available.
Conditional Sum and Count
SUMIF — Sums values in one column where a corresponding column meets a condition. Example: sum all sales amounts where the region is "North".
See exactly what's wrong with your data — try Sohovi free — try Sohovi free.
SUMIFS — Like SUMIF but supports multiple conditions. Example: sum all sales where region is "North" AND month is "January".
COUNTIF — Counts rows where one column meets a condition. Example: count how many rows have "Pending" in the status column.
COUNTIFS — Like COUNTIF with multiple conditions. Example: count rows where status is "Pending" AND assigned to "Alice".
Logic Functions
IF — Returns one value if a condition is true, another if false. The most-used function in Excel. Nest multiple IFs to handle multiple conditions (though IFS is cleaner for this).
IFS — Tests multiple conditions in sequence and returns the value for the first true condition. Cleaner than nested IFs for more than two conditions.
IFERROR — Wraps a formula and returns a custom value if it produces an error. Use to replace ugly #N/A errors with a blank cell or a "Not found" message.
ISBLANK — Returns TRUE if a cell is empty. Use in IF statements to handle blank cells differently from cells with values.
Text Functions
LEFT, RIGHT, MID — Extract a specific number of characters from the start, end, or middle of a text string. Useful for parsing codes, extracting area codes from phone numbers, or splitting concatenated values.
TRIM — Removes leading, trailing, and extra internal spaces. Essential after importing data from other systems where spacing is inconsistent.
LEN — Returns the character count of a text string. Use to validate that a field doesn't exceed a length limit or to check if a field is truly blank vs. containing spaces.
Sohovi lets you set up validation rules for any column and instantly see which rows fall outside them — no code or SQL required.
CONCATENATE / CONCAT / & — Joins text strings together. The & operator is the simplest: ="Hello " & A1. CONCAT handles ranges in modern Excel.
Date Functions
DATE — Creates a date value from year, month, and day numbers. Useful when dates are stored as separate columns that need combining.
YEAR, MONTH, DAY — Extract the year, month, or day from a date value. Use to group data by month or year in pivot tables.
DATEDIF — Calculates the difference between two dates in days, months, or years. Useful for age calculations and tenure calculations. Note: DATEDIF is undocumented but works in all Excel versions.
TEXT — Formats a number or date as text in a specified format. Example: TEXT(A1,"MM/DD/YYYY") converts a date value to a text string in US format.
Advanced Functions
UNIQUE — Returns the unique values from a range. Available in Microsoft 365. Eliminates the need to manually remove duplicates when you just want a distinct list.
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
FILTER — Returns a filtered subset of a range based on a condition. Available in Microsoft 365. Replaces the need for autofilter + manual copy-paste when extracting data subsets.
SUMPRODUCT — Multiplies corresponding elements in arrays and sums the results. Useful for weighted averages and conditional sums that predate SUMIFS.
If you've inherited a formula you don't understand, paste it into Sohovi's free Excel Formula Explainer for a step-by-step plain-English breakdown.
Keep Reading
Paste Any Formula for an Instant Explanation →