Excel Formulas List: The Complete Reference Guide for 2026

Mastering a comprehensive excel formulas list is the single biggest productivity unlock for anyone who works with spreadsheets, whether you are tracking household budgets, building corporate dashboards, or analyzing massive datasets for business intelligence. Excel ships with more than 450 built-in functions across categories like lookup, logical, text, math, statistical, financial, and date-time, and learning the right ones in the right order turns hours of manual work into seconds of automated calculation. This guide walks through every formula category you will realistically need.

The most searched Excel formula in the world is vlookup excel, and for good reason: it answers the universal question of how to pull a matching value from one table into another. Right behind VLOOKUP sit functions like IF, SUMIF, COUNTIF, INDEX, MATCH, and the newer dynamic array functions FILTER, XLOOKUP, and UNIQUE. Together these dozen formulas handle roughly 80% of real-world spreadsheet tasks, and we will cover every single one with concrete syntax examples.

Excel formulas always begin with an equals sign, followed by a function name, an open parenthesis, comma-separated arguments, and a closing parenthesis. The structure looks like =FUNCTION(argument1, argument2, argument3), and Excel evaluates the innermost parentheses first. Understanding this order of operations is critical when you start nesting functions, because a misplaced comma or unbalanced bracket can silently return the wrong answer rather than throwing an error. We will show how to debug nested formulas step by step.

This article is organized so you can scan it like a reference manual or read it cover to cover. Each section pairs the formula syntax with a real-world example, common errors, and a tip for going faster. You will also find tabs comparing classic functions like VLOOKUP against the modern XLOOKUP, plus a downloadable mental model for choosing the right function based on whether your data is in a single column, a table, or a multi-dimensional array.

Beyond raw formulas, Excel productivity depends on a handful of complementary skills that this guide also covers in context. You will see when to use a formula versus a feature, such as when SUMIF makes more sense than a PivotTable, or when conditional formatting beats a nested IF. We also explain how to combine formulas with structural tools like tables, named ranges, and data validation lists, which makes your spreadsheets dramatically easier to maintain.

By the end you will have a working vocabulary of more than 50 formulas, a mental framework for selecting between similar functions, and a clear path forward for practicing what you have learned. The companion practice quizzes linked throughout this article are designed to reinforce each section, and they pair perfectly with the reference material so you can test recall the same day you read about a new function. Let us start with the formulas everyone should know first.

If you only have one hour to spend learning Excel formulas this week, prioritize SUM, AVERAGE, COUNT, IF, VLOOKUP or XLOOKUP, and CONCATENATE. These six functions form the backbone of nearly every spreadsheet ever built, and fluency with them is the difference between an Excel beginner and an intermediate user. Everything else in this guide builds on these foundations, so do not skip the basics even if you feel comfortable with more advanced functions.

Lookup formulas are the workhorses of any serious excel formulas list, and the king of them all is still vlookup excel even though Microsoft recommends migrating to XLOOKUP for modern workbooks. The classic VLOOKUP syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), and it scans the leftmost column of a table for a match, then returns a value from a column to the right. The fourth argument should almost always be FALSE for exact matches, because TRUE requires sorted data and silently returns wrong answers when your list is not sorted alphabetically.

XLOOKUP improved on VLOOKUP in five major ways: it works left or right, defaults to exact match, supports wildcards, returns custom not-found messages, and handles dynamic arrays. The syntax =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) is more verbose but far more powerful. If you are still on Excel 2019 or earlier you will not have XLOOKUP, in which case the INDEX/MATCH combination =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) provides the same flexibility with broader compatibility.

HLOOKUP works exactly like VLOOKUP except it searches across the top row instead of down the leftmost column, which is useful when your data is laid out horizontally with categories across the top. In practice horizontal data is rare in business spreadsheets, so HLOOKUP gets less attention, but understanding it makes pivoting between layouts much faster. MATCH alone returns the position of a value in a list, which is invaluable when you need to know where a record sits rather than what it contains.

INDEX takes a range and returns the value at a specific row and column intersection, with the syntax =INDEX(array, row_num, [column_num]). On its own it is rarely useful because you have to know exact positions, but paired with MATCH it becomes the most flexible lookup pattern in Excel. The combined formula =INDEX(C2:C100, MATCH(A1, A2:A100, 0)) does everything VLOOKUP does and works in both directions, which is why power users prefer it over VLOOKUP even today.

OFFSET and INDIRECT are advanced reference functions that build cell addresses dynamically. OFFSET returns a range a specified number of rows and columns away from a starting cell, which is useful for rolling calculations and dashboards. INDIRECT converts a text string into a cell reference, which lets you build formulas that change based on user input or dropdown selections. Both are volatile functions, meaning they recalculate on every change, so use them sparingly in large workbooks where performance matters.

The newest lookup family includes FILTER, UNIQUE, SORT, and SORTBY, which return entire arrays of results instead of single values. FILTER is particularly transformative because =FILTER(data, criteria) returns every row matching a condition, replacing what used to require array formulas or advanced filter features. Combined with UNIQUE, you can build de-duplicated, filtered, sorted views of any dataset in a single line of formula. These functions only exist in Microsoft 365 and Excel 2021 or later.

One subtle skill that separates beginners from intermediate users is knowing when to anchor references with dollar signs. The reference A1 is relative and shifts when you copy the formula, while $A$1 is absolute and never moves. Mixed references like $A1 or A$1 lock only the row or column, which is essential when building lookup formulas that you plan to drag across a range. Pressing F4 cycles through the four reference types, saving keystrokes when authoring complex formulas.

Text functions transform messy data into clean, structured information, and they are essential when importing data from external systems where formatting is inconsistent. The LEFT, RIGHT, and MID functions extract characters from a text string based on position, with syntax like =LEFT(text, num_chars) returning the first N characters. LEN returns the total number of characters, which you can combine with the others to extract everything except the last few characters using =LEFT(A1, LEN(A1)-4) for a flexible right-trim operation.

FIND and SEARCH locate the position of a substring within a larger string. FIND is case-sensitive while SEARCH is not, and both return the starting position of the match or a #VALUE error if no match exists. SUBSTITUTE replaces all occurrences of one substring with another, and you can target a specific instance with its fourth argument. REPLACE works similarly but uses positions instead of text matching, which is useful for fixed-width data formats like phone numbers or social security numbers.

CONCAT replaced the older CONCATENATE function and joins multiple cells or strings into one. TEXTJOIN is even more powerful because it accepts a delimiter and an ignore_empty flag, making =TEXTJOIN(", ", TRUE, A1:A10) the cleanest way to build a comma-separated list from a column while skipping blank cells. TEXTSPLIT, introduced in Excel 365, does the reverse by splitting a string into an array based on one or more delimiters, replacing the older Text to Columns wizard for many use cases.

Date and time functions are deceptively simple because Excel stores dates as serial numbers under the hood. TODAY returns the current date with no arguments and updates whenever the workbook recalculates, while NOW includes the current time as well. DATE assembles a date from year, month, and day arguments, which is essential when building dates from data stored in separate columns. YEAR, MONTH, and DAY extract individual components from a date for grouping or filtering purposes.

DATEDIF is one of Excel's hidden gems because it does not autocomplete but works in every version. The syntax =DATEDIF(start_date, end_date, unit) accepts unit codes like Y for full years, M for full months, D for days, and YM for months ignoring years. This makes calculating someone's age or tenure trivial: =DATEDIF(birthdate, TODAY(), "Y") returns whole years. NETWORKDAYS counts business days between two dates and accepts a holidays list as a third argument, which is essential for project scheduling.

Statistical functions extend basic aggregation to handle sampling, distribution, and inference scenarios. AVERAGE returns the arithmetic mean, MEDIAN returns the middle value, and MODE returns the most frequently occurring value. STDEV.S and STDEV.P calculate sample and population standard deviations respectively, while VAR.S and VAR.P do the same for variance. PERCENTILE and QUARTILE return distribution boundaries, which are essential for analyzing performance data or grading on a curve.

COUNTIF, SUMIF, and AVERAGEIF perform conditional aggregation with a single criterion, while their plural counterparts COUNTIFS, SUMIFS, and AVERAGEIFS accept multiple criteria. The syntax =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) is more verbose than SUMIF but vastly more flexible. SUMPRODUCT predates these functions and still has uses for array-based math that the IFS family cannot easily replicate, making it a power-user favorite for complex weighted calculations.

Beyond formulas themselves, Excel offers structural features that complement your excel formulas list and make spreadsheets dramatically more maintainable. Learning how to create a drop down list in excel using Data Validation is one of the highest-leverage skills because it restricts cells to a predefined set of values, eliminating typos that break lookup formulas. To set one up, select your target cells, go to Data and then Data Validation, choose List from the Allow dropdown, and either type values separated by commas or reference a range containing the allowed values.

Dropdown lists pair beautifully with INDIRECT to create dependent dropdowns, where the choices in one cell change based on the selection in another. Name your sub-ranges to match the values in the parent dropdown, then set the dependent cell's source to =INDIRECT(parent_cell). This pattern is the foundation of well-designed input forms and dashboards, and it prevents the common problem of users entering invalid combinations that break downstream formulas relying on exact text matches.

Knowing how to freeze a row in excel keeps headers visible as you scroll through large datasets, which sounds trivial but transforms the experience of working with hundreds or thousands of rows. Click View then Freeze Panes, and choose Freeze Top Row, Freeze First Column, or Freeze Panes to lock everything above and to the left of your active cell. For more flexible scrolling, splitting the window with View then Split lets you view two distant areas of the same sheet side by side.

Excel Tables, created with Ctrl+T or Insert and then Table, transform a range into a named, dynamic structure that grows as you add rows. Formulas referencing table columns use structured references like =SUMIF(Sales[Region], "West", Sales[Amount]) instead of A2:A100, which is self-documenting and never breaks when the table expands. Tables also enable automatic banded rows, integrated filtering, and total rows with a single click, making them the recommended container for any tabular data you plan to formula against.

How to merge cells in excel is a frequent question, but the honest answer is to avoid merging cells whenever possible because merged cells break sorting, filtering, and many formulas. If you merge cells purely for visual centering, use Center Across Selection from the Alignment dialog instead, which produces the same look without the structural problems. When you must merge, restrict it to title rows above your data, never within the data itself, and document the merge so future maintainers do not accidentally unmerge and lose alignment.

Named ranges replace cryptic cell addresses with descriptive labels that make formulas readable. Define a name by selecting a cell or range, clicking the Name Box on the left of the formula bar, and typing a name like TaxRate or RegionalSales. Reference it in formulas just like a cell address: =Price*TaxRate is far easier to audit than =B2*Sheet2!$F$1. Named ranges also work across sheets, accept formulas as values, and can be managed in bulk through the Name Manager on the Formulas tab.

Finally, formula auditing tools on the Formulas tab help you understand and debug complex spreadsheets. Trace Precedents draws arrows showing which cells feed into a formula, while Trace Dependents shows which formulas use a given cell. Evaluate Formula steps through a nested formula one calculation at a time, revealing exactly where unexpected values originate. The Watch Window monitors specific cells as you work elsewhere, which is invaluable when you need to confirm that a change in one area produces the expected effect across the workbook.

Building real fluency with the complete excel formulas list takes deliberate practice, and the fastest path is to combine three habits: daily reps on small problems, weekly review of new functions, and monthly application to a real project. Start each morning by recreating yesterday's formulas from memory in a blank workbook, which surfaces gaps in your knowledge faster than any tutorial. The discipline of typing the syntax by hand, including parentheses and commas, embeds the patterns in muscle memory.

Keyboard shortcuts compound your productivity once formulas become second nature. Press Alt+= to insert SUM around a selection, F4 to cycle reference types, Ctrl+Shift+Enter to enter legacy array formulas, and Ctrl+` to toggle between formula view and value view across the whole sheet. The F2 key enters edit mode on the active cell with the cursor at the end, which is far faster than double-clicking, especially when working with formulas that contain many arguments and nested functions.

When a formula returns an unexpected result, do not start rewriting it from scratch. Instead, click the cell, press F2 to edit, and use F9 on individual arguments to see what each piece evaluates to. This bottom-up debugging approach catches the specific argument causing the problem in seconds, where guessing and rewriting can waste hours. Press Escape rather than Enter after using F9 to avoid replacing the formula with the calculated value, which is a destructive mistake that catches even experienced users.

Documentation matters more than you think. Add comments to complex formulas using N("comment text") inside a SUM, which lets you embed notes without affecting the result. For example =SUM(A1:A10) + N("Excludes header row") returns the same value but documents intent. The newer LET function takes this further by letting you name intermediate calculations inside a single formula, which transforms incomprehensible 200-character formulas into readable, self-documenting expressions that future-you will thank present-you for writing.

Performance becomes critical as workbooks grow. Volatile functions like NOW, TODAY, RAND, OFFSET, and INDIRECT recalculate on every change, which slows large workbooks dramatically. Replace OFFSET with INDEX where possible, and consider switching calculation mode to Manual on heavy workbooks by pressing F9 to recalculate on demand. Reducing the number of array formulas, avoiding entire-column references like A:A, and using helper columns instead of mega-formulas all help keep large files responsive.

Practice quizzes accelerate retention because they force active recall, which is far more effective than passive reading. After finishing this guide, work through the linked quizzes covering basic Excel knowledge, formulas specifically, functions broadly, and multiple-choice questions designed for skill assessments. Score yourself, review the explanations for any wrong answers, and revisit those topics in this article. Repeat the cycle weekly, and within a month you will be answering formula questions instantly that previously required documentation lookup.

Finally, build a personal cheat sheet of the ten formulas you reach for most often. For most analysts that list includes VLOOKUP or XLOOKUP, IF, SUMIFS, COUNTIFS, INDEX, MATCH, TEXTJOIN, IFERROR, TODAY, and DATEDIF. Print it, pin it next to your monitor, and resist the urge to memorize every function in Excel. Mastery of a small core, combined with the ability to look up the rest when needed, is far more valuable than shallow familiarity with hundreds of functions you will rarely use in real work.