Excel formulas are instructions you enter into cells that perform calculations, look up data, test conditions, or manipulate text. Every formula starts with an equals sign (=) — that's how Excel knows you're entering a calculation rather than plain text. From there, you combine cell references, values, operators, and functions to tell Excel exactly what to compute.
The difference between a formula and a function matters when you're learning. A formula is the entire expression you enter in a cell — like =A1+B1 or =SUM(A1:A10). A function is a named operation that Excel provides — SUM, AVERAGE, IF, VLOOKUP — that you use inside your formula. Most people use the terms interchangeably, and that's fine for everyday work. The important thing is understanding what each one does and when to reach for it.
Excel has over 500 built-in functions organized into categories: math and trigonometry, statistical, lookup and reference, text, logical, date and time, financial, and more. You don't need to know all of them. In practice, maybe 20–30 functions cover 95% of real spreadsheet work. Master those, and you can handle nearly anything that comes up on the job or in a certification exam.
Why do Excel formulas matter for certification prep and job performance? Because spreadsheet skills are directly tested. The Microsoft Office Specialist (MOS) Excel exam tests formula construction, function syntax, and the ability to solve data problems — not just button-clicking. Excel certification candidates consistently report that formula questions are the most common source of errors on the exam. Understanding the logic behind formulas — not just memorizing syntax — is what separates candidates who pass from those who don't.
The most commonly used Excel formulas are the simplest ones. If you're just getting started, these are what you need to master first — they appear in virtually every spreadsheet you'll encounter professionally.
=SUM(range) adds all numbers in a range. =SUM(B2:B20) totals everything from B2 to B20. You can also sum non-contiguous cells: =SUM(B2, D5, F8). SUM is the single most used Excel function — it handles sales totals, budget summaries, attendance counts, and thousands of other basic aggregation tasks.
=AVERAGE(range) returns the mean of a range. =COUNT(range) counts cells that contain numbers. =COUNTA(range) counts non-empty cells (including text). =MIN(range) and =MAX(range) return the smallest and largest values. These five functions handle the majority of basic data summarization work in real spreadsheets.
=IF(condition, value_if_true, value_if_false) is Excel's most important logical function. It checks whether a condition is true and returns different results depending on the answer. For example: =IF(B2>=60, "Pass", "Fail") returns "Pass" if the value in B2 is 60 or higher, and "Fail" otherwise. IF formulas power everything from grade calculations to inventory alerts to sales performance flags — any situation where you need Excel to make a decision based on data.
The =SUMIF(range, criteria, sum_range) function adds values only when they meet a condition. =COUNTIF(range, criteria) counts cells that meet a condition. These conditional aggregation functions are workhorses in real data analysis: total sales by region, count of customers with orders above a threshold, sum of hours worked by a specific employee. Once you understand SUMIF and COUNTIF, SUMIFS and COUNTIFS (which handle multiple conditions) follow naturally.
SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, ROUND, MOD, ABS, SUMIF, SUMIFS, COUNTIF, COUNTIFS. These handle numeric calculations, totals, and conditional aggregation — the backbone of financial and data reporting spreadsheets.
VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP (Excel 365), OFFSET, INDIRECT. Used to retrieve data from tables and ranges based on a lookup value — essential for merging datasets, building dashboards, and cross-referencing lists.
IF, AND, OR, NOT, IFS, IFERROR, IFNA, SWITCH. These formulas test conditions and return results based on true/false logic. Nested IF statements, error handling with IFERROR, and multi-condition logic with IFS and AND/OR are all standard professional skills.
CONCATENATE (&), LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, FIND, SUBSTITUTE, TEXT. Text functions let you clean, split, combine, and reformat text data — critical for working with names, addresses, codes, and imported data.
TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF, NETWORKDAYS, EDATE, EOMONTH. Date functions calculate durations, add intervals, find business days between dates, and extract date components from full datetime values.
MEDIAN, MODE, STDEV, VAR, PERCENTILE, RANK, LARGE, SMALL, CORREL. These functions go beyond AVERAGE to give you distribution, spread, and ranking information — used in analytics, reporting, and quality control work.
VLOOKUP is arguably the most discussed and tested Excel formula — and for good reason. It lets you search a column for a specific value and return a corresponding result from the same row. The full syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Breaking that down: lookup_value is what you're searching for. table_array is the range that contains your data. col_index_num is which column in that range to return the result from (1 = first column, 2 = second column, etc.). range_lookup should almost always be FALSE (or 0) — this forces an exact match.
Example: =VLOOKUP(A2, $D$2:$F$100, 2, FALSE) looks up the value in cell A2 within the range D2:F100 and returns the value from the second column of that range. If A2 contains an employee ID and D2:F100 contains employee IDs, names, and departments, this formula returns the employee's name.
VLOOKUP has two important limitations: it can only search the leftmost column of your table and return values to the right, and it's slower than alternatives on large datasets. That's why experienced Excel users prefer INDEX/MATCH for professional work. INDEX/MATCH combines two functions: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). It's more flexible — it can look in any direction, handles column insertions without breaking, and processes faster. On an Excel certification exam, you'll need to demonstrate both approaches.
For Excel 365 users, XLOOKUP has largely replaced VLOOKUP. It handles left-to-right and right-to-left lookups in a single formula, returns entire arrays, and has built-in error handling. But VLOOKUP remains the standard for compatibility across older Excel versions, so it still dominates in most workplaces and appears on all versions of the MOS exam.
Three common VLOOKUP errors trip up beginners and exam candidates: using TRUE for an exact match (it will return the closest value instead), forgetting to lock the table array with absolute references ($D$2:$F$100), and counting the wrong column number. If VLOOKUP is returning #N/A errors, the lookup value likely doesn't exist in the first column of your table — or there's a format mismatch (number stored as text vs. actual number). Check for invisible spaces using TRIM() on both the lookup value and the table data.
The IF function is Excel's decision-making engine. Basic syntax: =IF(logical_test, value_if_true, value_if_false). Nested IF statements let you test multiple conditions in sequence — each value_if_false can itself be another IF statement. But nested IFs become hard to read quickly. Excel 2019 and later offer IFS, which tests multiple conditions more cleanly: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F").
IFERROR is essential for professional spreadsheets — it keeps error messages from appearing in reports and dashboards. Always wrap lookup formulas in IFERROR when the lookup value might not always exist in the table.
Text functions are essential when working with imported data, databases, or any spreadsheet that contains names, addresses, codes, or mixed-format entries. The most common operations: combining text from multiple cells, extracting parts of a text string, and cleaning up formatting inconsistencies.
TRIM is often the first thing to try when a VLOOKUP returns errors even though the value looks correct — invisible spaces in the source data or lookup cell are a frequent culprit.
Excel stores dates as serial numbers (January 1, 1900 = 1), which is why date arithmetic works. You can subtract one date from another to get the number of days between them. Date functions let you extract components, add intervals, and calculate business-day durations.
Note that DATEDIF is a hidden function — it works but doesn't appear in autocomplete. You must type it manually. It's tested on some Excel certification exams specifically because of this quirk.
Once you start working with PivotTables, you'll encounter formulas that interact with them. GETPIVOTDATA retrieves specific values from a PivotTable: =GETPIVOTDATA("Sales", $A$3, "Region", "East") pulls the sales total for the East region from a PivotTable with its top-left corner at A3. This formula is auto-generated when you click a PivotTable cell while building a formula — useful for building reports that pull from PivotTable data.
Calculated fields let you add custom formulas directly inside a PivotTable. Instead of creating a helper column in your source data, a calculated field computes a new metric within the PivotTable itself — like profit margin (Revenue - Cost) or conversion rate. To add one: PivotTable Analyze → Fields, Items, & Sets → Calculated Field. The formula uses field names rather than cell references.
Every Excel formula follows the same basic structure: start with =, then combine cell references, operators, values, and functions in the correct order. Excel evaluates formulas using standard mathematical order of operations — multiplication and division before addition and subtraction — so parentheses matter. =2+3*4 returns 14, not 20. =(2+3)*4 returns 20. When in doubt, use parentheses to make the evaluation order explicit.
Cell references are the building blocks of most formulas. A1 is a relative reference — it shifts when you copy the formula to another cell. $A$1 is an absolute reference — it stays fixed regardless of where you copy it. $A1 locks the column but lets the row shift. A$1 locks the row but lets the column shift. Press F4 while your cursor is inside a cell reference in the formula bar to cycle through all four reference types.
Range references use a colon: A1:A20 refers to all cells from A1 to A20. You can extend ranges across multiple columns: A1:C20 covers three columns and twenty rows. Entire column references (A:A) work in formulas but are slower on large spreadsheets — prefer explicit ranges when performance matters.
Named ranges make formulas dramatically more readable. Instead of =VLOOKUP(A2, $D$2:$F$500, 2, FALSE), a named range lets you write =VLOOKUP(A2, EmployeeTable, 2, FALSE). Define a name by selecting the range, clicking the Name Box (the cell address box at the top left of the screen), and typing the name. Named ranges are also absolute by default, which eliminates the need to manually add dollar signs for lookup table references.
Excel Tables (created with Ctrl+T or Insert → Table) give formulas structured references automatically. When you reference a column inside a table, Excel uses the column header name in brackets: =[@Sales]*1.08 multiplies the Sales column value in the current row by 1.08. Table formulas auto-fill to new rows as data is added — you don't need to manually extend the formula range. This makes Excel spreadsheets with dynamic data much easier to maintain.
The Formula Auditing tools (Formulas tab → Formula Auditing) are essential for troubleshooting. "Trace Precedents" draws arrows showing which cells feed into the selected formula. "Trace Dependents" shows which cells depend on the selected cell. "Evaluate Formula" lets you step through a complex formula one calculation at a time, showing the intermediate result at each step — invaluable for debugging nested IF statements or complex SUMPRODUCT formulas that return unexpected results.
Keyboard shortcuts speed up formula work considerably. Ctrl+` (backtick) toggles between showing formula results and showing formula text in cells — useful when you want to see all formulas in a worksheet at once. Ctrl+Enter enters the same formula in all selected cells simultaneously. Ctrl+Shift+End extends your selection to the last used cell, helping you identify the actual size of your data range before building formulas over it.
Excel formula errors appear as codes starting with # — each one points to a specific problem. Knowing what they mean is faster than guessing at fixes.
#N/A — "Not Available." Usually appears in VLOOKUP and INDEX/MATCH when the lookup value can't be found. Check for spaces in the lookup value (use TRIM), format mismatches (number vs. text), or typos in the lookup value itself. Wrap in IFERROR to hide the error in production spreadsheets.
#VALUE! — The formula expects a number but got text, or expects text but got a number. Frequently appears when you perform arithmetic on cells that contain text mixed with numbers. Check that all cells in a math formula contain numeric data. The TEXT function and VALUE function can convert between types.
#REF! — A cell reference is invalid — typically because rows or columns referenced in the formula were deleted. This is why using named ranges or tables instead of raw cell ranges makes spreadsheets more durable. If you see #REF!, check for recently deleted rows or columns in the formula's reference area.
#DIV/0! — Division by zero. The formula is attempting to divide by a cell that contains zero or is blank. Use IFERROR or IF to handle this: =IFERROR(A1/B1, 0) returns 0 instead of the error when B1 is zero.
#NAME? — Excel doesn't recognize part of the formula. Usually caused by a typo in a function name, a missing quote around text, or using a function that doesn't exist in your Excel version. Double-check the spelling and syntax of every function in the formula.
Circular reference errors occur when a formula refers to its own cell — directly or through a chain of references. Excel warns you when this happens. Circular references are occasionally intentional (iterative calculation), but usually they're mistakes. Use the "Trace Precedents" and "Trace Dependents" buttons in the Formulas tab to visualize which cells are feeding which formulas when debugging.
Once you're comfortable with the fundamentals, these formulas separate intermediate users from advanced ones — and they're increasingly tested on professional certification exams.
SUMPRODUCT is one of the most versatile Excel functions. It multiplies corresponding values in arrays and sums the results — but it's also used as an alternative to SUMIFS for complex multi-condition aggregation, and it can perform weighted averages without an extra column. =SUMPRODUCT((A2:A100="East")*(B2:B100>1000)*C2:C100) sums column C only where column A is "East" AND column B exceeds 1000.
INDIRECT returns the reference described by a text string. It's used to create dynamic references where the worksheet name or column letter changes based on another cell's value. Useful for dashboards that pull data from different sheets based on a dropdown selection, but it's volatile (recalculates on every change) so use it sparingly on large spreadsheets.
OFFSET returns a reference offset from a starting cell by a specified number of rows and columns — used to create dynamic named ranges and rolling-window aggregations. =SUM(OFFSET(B1, 0, 0, COUNTA(B:B), 1)) sums column B dynamically, expanding as new data is added.
Array formulas (entered with Ctrl+Shift+Enter in older Excel, just Enter in Excel 365 with dynamic arrays) let a single formula perform calculations across multiple cells simultaneously. The FILTER, SORT, UNIQUE, and SEQUENCE functions introduced in Excel 365 replaced many array formula use cases with simpler syntax — if you're working in a modern environment, these are worth learning.
For anyone preparing for the Microsoft Excel certification exam, the formula categories above cover the vast majority of what's tested. The MOS Excel Expert exam goes deeper into named ranges, table-structured references, advanced array formulas, and complex nested functions. The Associate-level MOS exam focuses on the core functions — SUM, AVERAGE, COUNT, IF, VLOOKUP, basic text and date functions — in practical, task-based scenarios rather than abstract formula syntax questions.
Practice is the only thing that makes formulas stick. Working through real data problems — even simple ones — builds the kind of intuition for formula construction that reading about syntax can't replicate. Use a real dataset (your own budgets, lists, schedules) or download a sample spreadsheet and try solving problems rather than following tutorials step by step. The moment you construct a VLOOKUP that works on data you care about, the syntax becomes memorable in a way that a textbook example never quite achieves.
One underrated learning technique: take any formula you don't fully understand and strip it down to its simplest version in a blank spreadsheet with dummy data. If SUMPRODUCT confuses you, build a two-column version with five rows and trace through what each part returns.
Understanding the building blocks of a complex formula in isolation makes the full version click immediately — much faster than trying to understand it in the context of a sprawling, real-world spreadsheet where a dozen other things are happening simultaneously. Formula troubleshooting is a skill in itself, and deliberate practice on small examples develops it faster than waiting for problems to arise in production files.
The fastest way to learn Excel formulas is hands-on practice with real data scenarios. Our Excel skills guide walks through the most important functions with worked examples and interactive exercises. If you're preparing for a job test or Microsoft certification, start with the practice questions to identify which formula types need the most work before your exam date.