The complete excel functions list is the backbone of every spreadsheet workflow, from simple budget tracking to enterprise financial modeling. Microsoft Excel ships with more than 500 built-in functions across 14 categories, and mastering even the top 50 will transform how you handle data, automate calculations, and build dashboards. Whether you are preparing for a job interview, studying for a certification, or just trying to stop manually summing columns, this reference covers the exact syntax, real-world examples, and edge cases for every formula category you need to know in 2026.
Functions in Excel are predefined formulas that perform specific calculations using arguments you supply. Instead of writing =A1+A2+A3+A4+A5, you write =SUM(A1:A5) and Excel does the heavy lifting. The savings compound quickly: a financial analyst using SUMIFS, INDEX-MATCH, and XLOOKUP in combination can complete in 15 minutes what would take two hours with manual cell references. That efficiency is why Excel skills appear on 82% of finance, operations, and analyst job descriptions on LinkedIn.
The most-searched function on Google is vlookup excel, with over 27,000 monthly queries in the United States alone. VLOOKUP has been Excel's signature lookup function since 1985, but it now shares the spotlight with XLOOKUP (introduced in 2020) and the dynamic array functions FILTER, SORT, UNIQUE, and SEQUENCE that arrived with Microsoft 365. Knowing when to use each one is the difference between a workbook that calculates in seconds and one that locks up on 50,000 rows.
This guide organizes Excel's library into the seven categories you will actually use: lookup and reference, math and trigonometry, text manipulation, logical operators, date and time, statistical, and financial. Within each category, we explain the syntax, walk through a concrete example with real numbers, flag the common mistakes (like the dreaded #N/A error in VLOOKUP), and show you the newer dynamic array alternative when one exists. You will also learn how to merge cells in excel and how to create a drop down list in excel using data validation, since those operations frequently pair with formulas.
Beyond pure functions, modern Excel power users combine formulas with structural features like Tables, Named Ranges, and Power Query. A formula referencing Sales[Revenue] is far more readable and resilient than one referencing B2:B5847, and it auto-expands when new rows are added. We will show you how to wire these structures together so your workbooks remain maintainable months after you build them.
If you are studying for certification exams like Microsoft Office Specialist (MOS) Excel Associate or Expert, the function categories in this guide map directly to the exam objectives. Microsoft's Expert exam (MO-201) specifically tests advanced formulas including nested IFs, array formulas, INDEX-MATCH, and the date-time functions NETWORKDAYS, WORKDAY, and EOMONTH. Practice with real exam-style questions is the fastest way to commit syntax to memory.
By the end of this article, you will have a working reference covering every essential excel functions list entry, code snippets you can copy directly into your worksheets, and a clear understanding of which function to reach for in any common business scenario. Bookmark this page — you will return to it more often than you think.
VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, INDIRECT, OFFSET, CHOOSE. Used for retrieving values from tables, building dynamic reports, and linking data across sheets. The most-tested category on certification exams.
SUM, SUMIF, SUMIFS, SUMPRODUCT, ROUND, ABS, MOD, RAND, RANDBETWEEN, POWER, SQRT. Handles aggregation, rounding, and arithmetic. SUMIFS is the workhorse of conditional totaling in finance dashboards.
IF, IFS, AND, OR, NOT, XOR, IFERROR, IFNA, SWITCH. Builds decision logic into your spreadsheets. Nested IFs handle complex grading scales, commission tiers, and approval workflows in just a few lines.
CONCAT, TEXTJOIN, LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, TRIM, UPPER, LOWER, PROPER, LEN, TEXT, VALUE. Cleans messy imports, parses names, and formats output for reports.
TODAY, NOW, DATE, YEAR, MONTH, DAY, WEEKDAY, NETWORKDAYS, WORKDAY, EOMONTH, DATEDIF, EDATE. Critical for scheduling, aging reports, and any HR or project management calculation.
The lookup and reference category is where most Excel users live, and vlookup excel remains the gateway function. VLOOKUP searches the leftmost column of a table for a value and returns a corresponding value from another column. The syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). For example, =VLOOKUP("Smith", A2:D100, 3, FALSE) finds "Smith" in column A and returns the value from column C of the same row. The FALSE argument is critical — it forces an exact match instead of approximate.
The most common VLOOKUP error is #N/A, which means the lookup value was not found. The second most common is returning the wrong value because the lookup column was not the leftmost column. VLOOKUP can only look right, not left. That single limitation is what drove Microsoft to release XLOOKUP in 2020. XLOOKUP can look in any direction, returns a clean #N/A you can customize, supports approximate matching with binary search, and uses simpler syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]).
For the millions of Excel users still on 2019 or earlier, INDEX-MATCH is the classic alternative that beats VLOOKUP on flexibility. =INDEX(C2:C100, MATCH("Smith", A2:A100, 0)) does exactly what XLOOKUP does — look up "Smith" in column A and return the matching value from column C — and it works in every version of Excel since 2003. Power users prefer INDEX-MATCH because the return column is independent of the lookup column, so inserting new columns will not break the formula.
HLOOKUP works like VLOOKUP but searches across rows instead of down columns. It is rarely useful in modern workflows because data is almost always organized vertically, but it shows up in legacy workbooks. CHOOSE lets you pick the Nth value from a list: =CHOOSE(2, "Red", "Green", "Blue") returns "Green". Combined with WEEKDAY, CHOOSE can convert a date into a day name without using TEXT.
INDIRECT and OFFSET are the volatile functions of the lookup family. They build cell references from text strings, which makes them powerful for dynamic dashboards but slow on large workbooks because they recalculate every time anything changes. Use them sparingly. A common INDIRECT pattern is sheet-switching: =INDIRECT("'"&A1&"'!B2") pulls cell B2 from whatever sheet name is typed in A1.
For more on filtering and arranging data after you have looked it up, see our guide on how to add a filter in Excel. Filters and lookup functions are frequently used together — you might use XLOOKUP to enrich a dataset with reference data, then filter the result to show only matching records.
Dynamic arrays added FILTER, SORT, UNIQUE, and SEQUENCE to the lookup toolkit. =FILTER(A2:D100, B2:B100="East") returns every row where column B equals "East", with no helper columns and no need to drag formulas down. The result spills automatically into adjacent cells, and the source data can be re-sorted with =SORT(FILTER(...)). This combination has quietly replaced PivotTables for many lightweight reporting tasks.
SUMIFS is the most powerful aggregation function in Excel and the single biggest productivity unlock for analysts. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). To sum all sales for the East region in Q1: =SUMIFS(D2:D1000, B2:B1000, "East", C2:C1000, "Q1"). Up to 127 criteria pairs are supported, so you can slice data along many dimensions without a PivotTable.
COUNTIFS and AVERAGEIFS follow the exact same pattern. SUMPRODUCT predates SUMIFS and is still useful for weighted averages: =SUMPRODUCT(scores, weights)/SUM(weights) calculates a weighted GPA in one cell. ROUND, ROUNDUP, and ROUNDDOWN control display precision, while MROUND rounds to the nearest multiple — perfect for pricing tiers ending in $0.99 or $0.95.
TEXTJOIN replaced the clunky CONCATENATE function in Excel 2019. Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2]...). To combine first name, middle initial, and last name with spaces while skipping blanks: =TEXTJOIN(" ", TRUE, A2, B2, C2). The ignore_empty argument is the secret sauce — no more double spaces when middle names are missing.
LEFT, RIGHT, and MID extract substrings by position. FIND and SEARCH locate the position of a character within text — SEARCH is case-insensitive, FIND is case-sensitive. SUBSTITUTE replaces specific occurrences: =SUBSTITUTE(A1, "-", "") strips all hyphens from a phone number. TRIM removes extra spaces, and CLEAN strips non-printable characters — both essential after pasting from PDFs or web pages.
IF is the gateway logical function: =IF(condition, value_if_true, value_if_false). Nested IFs handle multiple conditions: =IF(score>=90, "A", IF(score>=80, "B", IF(score>=70, "C", "F"))). Excel 2019 introduced IFS, which flattens this into =IFS(score>=90, "A", score>=80, "B", score>=70, "C", TRUE, "F") — easier to read and edit.
IFERROR wraps any formula and substitutes a custom value when the formula returns an error: =IFERROR(VLOOKUP(...), "Not found"). Use this anywhere users will see your output. AND and OR combine conditions: =IF(AND(A1>100, B1="Active"), "Qualified", "No"). SWITCH is cleaner than nested IFs when matching exact values like product codes or status flags.
Instead of =SUMIFS(D2:D5000, B2:B5000, "East"), define D2:D5000 as Sales and B2:B5000 as Region. Your formula becomes =SUMIFS(Sales, Region, "East"). Use Formulas → Define Name or press Ctrl+F3. Named ranges also work across sheets and survive row insertions, making your workbook far more maintainable.
Statistical functions handle everything from simple averages to regression analysis. AVERAGE, MEDIAN, and MODE.SNGL cover central tendency. STDEV.P (population) and STDEV.S (sample) calculate standard deviation — pick STDEV.S 95% of the time since you almost always have a sample, not the entire population. VAR.P and VAR.S follow the same pattern for variance. PERCENTILE.INC and QUARTILE.INC let you compute distribution thresholds for performance reviews, salary bands, or grading curves.
RANK.EQ and RANK.AVG assign ranks to values in a list, useful for leaderboards and sales contests. LARGE and SMALL pull the Nth largest or smallest value: =LARGE(B2:B100, 3) returns the third-highest revenue. COUNTBLANK counts empty cells in a range, which is invaluable for data quality checks. The forecast family — FORECAST.LINEAR and FORECAST.ETS — performs simple linear regression and exponential smoothing forecasts directly in a worksheet.
Financial functions are where Excel earns its keep in finance and accounting departments. PMT calculates loan payments: =PMT(rate/12, term*12, -principal) returns the monthly payment for a mortgage or auto loan. FV (future value), PV (present value), and NPV (net present value) handle time-value-of-money calculations for investment analysis. IRR returns the internal rate of return for a series of cash flows, and XIRR handles uneven cash flow dates.
For depreciation, SLN computes straight-line, DB and DDB compute declining balance, and SYD computes sum-of-years-digits. Bond pricing uses PRICE, YIELD, and DURATION. These functions follow the conventions of the Excel financial library introduced in the 1990s, so syntax is consistent: rate first, period count second, then payment, present value, future value, and type.
Date and time functions deserve their own deep dive. DATEDIF is a hidden gem that calculates the difference between two dates in years, months, or days: =DATEDIF(birth_date, TODAY(), "Y") returns age in completed years. NETWORKDAYS counts business days between two dates, optionally excluding a list of holidays — perfect for SLA tracking and project deadlines. WORKDAY does the reverse, adding business days to a start date.
EOMONTH returns the last day of the month N months from a given date, which is essential for month-end reporting: =EOMONTH(TODAY(), 0) returns the last day of the current month. EDATE adds calendar months while preserving the day number. WEEKDAY returns a number 1-7 representing the day of the week, which combined with CHOOSE gives you day names without TEXT formatting.
When your formulas need to summarize variability, our deep-dive on the standard deviation formula in Excel walks through STDEV.P versus STDEV.S with worked examples. The choice between these two functions is one of the most-missed questions on Excel certification exams, so it pays to commit the rule to memory: STDEV.S when you have a sample of a larger population, STDEV.P when your data is the entire population you care about.
Dynamic arrays are the biggest Excel upgrade in a generation, and they completely changed how power users write formulas. Released in 2020 to Microsoft 365 and Excel 2021, dynamic arrays let a single formula return multiple values that spill automatically into adjacent cells. =UNIQUE(A2:A1000) returns every distinct value from the range, with no helper columns and no Remove Duplicates command. The spilled range is referenced with the # operator: A2# refers to the entire spilled output.
FILTER is the centerpiece. =FILTER(data, criteria, [if_empty]) returns only rows matching your condition: =FILTER(Sales, Sales[Region]="East", "No matches"). Combine with SORT and UNIQUE: =SORT(UNIQUE(FILTER(Customers, Customers[Status]="Active"))) returns a deduplicated, alphabetized list of active customers as a single formula. This pattern has replaced advanced filters and intermediate PivotTables for many ad-hoc reporting tasks.
SEQUENCE generates a series of numbers: =SEQUENCE(10) returns 1-10 vertically. Combined with DATE, it creates date series: =DATE(2026, SEQUENCE(12), 1) returns the first day of every month in 2026. RANDARRAY generates random number grids of any size, perfect for Monte Carlo simulations. These functions eliminate the need to drag formulas down or build helper columns for many common tasks.
The TEXTSPLIT, TEXTBEFORE, and TEXTAFTER functions added in 2022 modernize text parsing. =TEXTSPLIT(A1, ",") splits a comma-separated string into separate cells. =TEXTBEFORE(A1, "@") extracts the username portion of an email address. These replace combinations of LEFT, RIGHT, MID, and FIND that previously required nested formulas.
LAMBDA, also released in 2022, lets you build custom reusable functions without VBA. =LAMBDA(x, y, x^2+y^2)(3,4) returns 25. Save the lambda to the Name Manager and you have a custom function callable like any built-in. Companions BYROW, BYCOL, MAP, REDUCE, and SCAN apply lambdas across arrays — these are how power users now write what used to require macros.
The PIVOTBY and GROUPBY functions arrived in 2024 and bring PivotTable-style aggregation to formulas: =GROUPBY(Sales[Region], Sales[Revenue], SUM) returns a sorted summary by region in a single cell. Filtering, sorting, and percent-of-total calculations all happen with arguments, making these the cleanest aggregation tools Excel has ever shipped.
To navigate large datasets where dynamic arrays return long spilled ranges, you will want to lock headers in place. Our guide on Freeze Panes in Excel covers exactly how to keep your column headers visible while scrolling through hundreds of rows of FILTER or SORT output, so you never lose context as you analyze the results.
Practical mastery of the excel functions list comes from building real workbooks, not just reading reference pages. Start by picking three functions you do not know yet, and force yourself to use them on tomorrow's tasks. If you have never used TEXTJOIN, replace your next CONCATENATE with it. If you only use VLOOKUP, rebuild one formula with INDEX-MATCH and notice the flexibility difference. Repetition under real conditions is what moves syntax from cheat sheet to muscle memory.
Use the F2 key to edit a cell in place, F4 to toggle absolute references ($A$1 vs A1), and F9 to evaluate part of a formula before committing. Selecting part of a formula in the formula bar and pressing F9 shows you exactly what that fragment returns, which is the fastest way to debug nested IFs or array formulas. The Formulas → Evaluate Formula button steps through evaluation one operation at a time, color-coding each calculation.
Convert your data ranges to Tables (Ctrl+T) before writing formulas. Tables auto-expand when new rows are added, support structured references like Sales[Revenue] instead of B2:B5847, and give every formula referencing them the same automatic expansion. A SUMIFS formula referencing a table will continue working when your data grows from 1,000 to 10,000 rows without any manual updates.
Learn the keyboard shortcuts for common operations: Ctrl+; inserts today's date, Ctrl+Shift+: inserts the current time, Alt+= auto-sums the column above, and Ctrl+Shift+Enter (in legacy Excel) submits an array formula. In modern Microsoft 365, dynamic arrays usually eliminate the need for Ctrl+Shift+Enter, but it still matters when you open older workbooks.
Audit your formulas with Formulas → Trace Precedents and Trace Dependents. These commands draw arrows showing which cells feed into a formula and which cells use a formula's output. On inherited spreadsheets, this is the fastest way to understand what someone else built. Watch Window lets you monitor specific cells while editing far away in the workbook — invaluable for catching cascading errors.
Finally, document complex formulas with the N function: =SUM(A1:A100)+N("Excludes refunds, see cell B1 for total"). The N function returns 0 for text, so the comment does not affect the calculation. For really critical workbooks, add a separate Documentation sheet listing every named range, every key formula, and the business logic behind it. Your future self will thank you when you reopen the file in six months.