Excel spreadsheet formulas are the engine behind every meaningful calculation you make in a workbook. They turn flat data into living, breathing answers—totals that update when a number changes, lookups that pull a customer name from another sheet, dates that calculate themselves from a single anchor cell. If you have ever stared at a column of numbers and wondered how to summarize them without a calculator, formulas are the answer.
Formulas are also the single biggest reason people get hired into analyst, finance, operations, and admin roles. Excel proficiency is non-negotiable in those fields. The good news: a formula is just an instruction. You type an equals sign, you tell Excel what to do, and Excel does it.
This guide walks through the formulas hiring managers actually test for, the syntax quirks that trip up self-taught learners, and the practical scenarios where each one earns its keep. You will see how SUM, AVERAGE, IF, VLOOKUP, INDEX/MATCH, XLOOKUP, COUNTIF, SUMIF, and the date functions fit together. By the end, the formula bar should feel less like a foreign language and more like a tool you reach for on instinct.
Excel formulas are also collaborative tools. The same workbook you use to calculate quarterly totals can be opened by a colleague to verify the math, then by a manager to approve the figures, then by an auditor to confirm compliance. Each person sees the formula and can trace exactly how a number was derived. That transparency is impossible with calculator-driven math or paper ledgers.
The numbers tell a story. Even though Excel ships with hundreds of functions, you only need about a dozen to handle 80% of real-world tasks. That is good news for anyone preparing for a job test or certification.
Instead of trying to memorize every function in the library, focus on the core thirteen: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, IF, IFS, VLOOKUP, XLOOKUP, INDEX/MATCH, CONCAT, and TEXT. Master those and you can handle most spreadsheet challenges without breaking a sweat.
Formulas are not just about getting a number on the screen. They are about building workbooks that hold up under pressure. When a CFO asks for a quick sales total by region, you do not want to rebuild the report—you want a SUMIF that already knows the answer. When HR sends a list of employee IDs and asks for matching salaries, you want VLOOKUP or XLOOKUP doing the heavy lifting in seconds.
Stop thinking of formulas as math. Think of them as questions you ask the spreadsheet. SUM asks: what is the total? IF asks: is this true or false? VLOOKUP asks: find this value somewhere else and bring back a matching piece of data.
When you frame formulas as questions, picking the right one becomes intuitive. You match the question in your head to the function that asks it. That mental shift is the single most useful thing a beginner can learn.
Start with the absolute basics: arithmetic operators. Excel uses + for addition, - for subtraction, * for multiplication, / for division, and ^ for exponents. Mix these with cell references and Excel treats them like a calculator. A formula like =A1*B1 multiplies the contents of A1 and B1.
Order of operations follows standard math rules. Parentheses first, then exponents, then multiplication and division, finally addition and subtraction. Wrap your logic in parentheses when in doubt. A formula like =(A1+A2)/2 averages two cells. Without parentheses, =A1+A2/2 would only divide A2 by two before adding A1.
From there you graduate to functions. A function is a named formula Excel ships with built-in logic. =SUM(A1:A10) adds A1 through A10. =AVERAGE(B1:B20) returns the mean. =MAX and =MIN return the largest and smallest values. =COUNT counts the numeric cells; =COUNTA counts every non-empty cell.
These five functions alone will carry you through countless everyday tasks: monthly totals, attendance counts, sales averages, top performers, lowest stock levels. Master them before moving on.
Basic math operators: + - * / ^. Used for direct calculations between cells or values without a function name.
SUM, AVERAGE, MIN, MAX, COUNT. Summarize ranges of data into a single answer.
IF, IFS, AND, OR, NOT. Make decisions inside a formula and return different results based on conditions.
VLOOKUP, HLOOKUP, XLOOKUP, INDEX/MATCH. Find values in tables and return related information.
CONCAT, LEFT, RIGHT, MID, TEXT, TRIM, UPPER, LOWER. Manipulate and clean strings of text.
TODAY, NOW, DATE, DATEDIF, EOMONTH, WEEKDAY. Calculate durations, deadlines, and calendar logic.
The six categories above are the buckets every Excel formula falls into. Knowing the bucket helps you find the function faster. If someone asks you to count how many cells contain Approved, you instantly know you need an aggregation function with a condition. That points to COUNTIF.
If they ask you to grab the manager name for a given employee ID, you reach for the lookup bucket and pull out VLOOKUP or XLOOKUP. If they ask to flag late orders, you reach for the logical bucket and write an IF statement.
The logical bucket deserves special attention because IF is the workhorse of business spreadsheets. =IF(A1>100, "High", "Low") evaluates whether A1 is greater than 100 and returns High if true, Low if false.
You can nest IFs for multiple thresholds or use the cleaner IFS function in newer Excel versions. AND and OR let you check multiple conditions: =IF(AND(A1>100, B1="Yes"), "Pass", "Fail"). These logical formulas are how you build commission calculators, status flags, eligibility checks, and grade reports.
SUM adds a range: =SUM(B2:B100). Simple and ubiquitous. The conditional version, SUMIF, adds only when a condition is met: =SUMIF(A2:A100, "East", B2:B100) totals B values where column A says East.
SUMIFS handles multiple conditions: =SUMIFS(B2:B100, A2:A100, "East", C2:C100, ">1000") sums east-region sales over $1,000. These three handle most reporting tasks you will face in a business setting.
IF returns one value if a test is true, another if false: =IF(A2>=70, "Pass", "Fail"). Nested IFs handle multiple thresholds: =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F"))).
Use IFS in Excel 2019 and newer for cleaner syntax: =IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F"). Easier to read, easier to debug.
Syntax: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup]). Searches the first column of a table for a value and returns data from a specified column.
Example: =VLOOKUP(A2, EmployeeTable, 3, FALSE) finds A2 within EmployeeTable and returns the third column. Always use FALSE for exact match. TRUE is for approximate matching and is rarely what you want.
The modern replacement for VLOOKUP and HLOOKUP. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). Cleaner syntax, returns a custom message if not found, and searches left or right.
Example: =XLOOKUP(A2, B:B, D:D, "Not Found") finds A2 in column B and returns column D, displaying Not Found on misses. If your version supports it, default to XLOOKUP.
The classic alternative to VLOOKUP. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). More flexible than VLOOKUP because the lookup column can be anywhere in the table.
Example: =INDEX(C:C, MATCH(A2, B:B, 0)) returns the value in column C where column B matches A2. Verbose but reliable, especially on older Excel versions without XLOOKUP.
Lookup formulas separate competent Excel users from expert ones. VLOOKUP was the standard for two decades, but it has limitations. It only searches left to right and breaks when you insert columns.
XLOOKUP, introduced in Excel 365, fixes both problems and adds a clean syntax for handling missing values. If your workplace runs current Excel versions, XLOOKUP should be your default. If you are stuck on older versions, learn INDEX/MATCH. More verbose than XLOOKUP but more powerful and reliable than VLOOKUP.
Here is a real example. You have 5,000 product SKUs in column A and prices in column F. A coworker hands you 200 SKUs and asks for matching prices. With XLOOKUP, the formula =XLOOKUP(G2, A:A, F:F, "SKU not found") delivers the answer in one cell. Drag it down 200 rows.
Without lookup formulas, you would be eyeballing rows or doing 200 manual matches. The time savings are not incremental. They are an order of magnitude.
Error handling is where intermediate users level up. Every formula can fail in predictable ways. The difference between a brittle workbook and a robust one is how you wrap the failure cases.
IFERROR is the easiest tool: =IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Available") returns Not Available instead of an ugly #N/A. IFNA is more targeted. It only catches #N/A errors and lets other errors surface, which can be useful for debugging.
Once you start writing formulas that other people will use, error handling becomes table stakes. A manager opening your dashboard does not need to see #DIV/0! splashed across half the cells. They need to see clean values or a message that tells them what is happening.
IFERROR turns a fragile spreadsheet into a polished tool. It is a small wrapper with outsized impact on the perceived quality of your work.
Beyond the conditional sums, you should know the IS-family of functions: ISBLANK, ISNUMBER, ISTEXT, ISERROR. They return TRUE or FALSE and pair perfectly with IF to test for conditions. =IF(ISBLANK(A2), "Missing", A2) replaces empty cells with the word Missing. =IF(ISNUMBER(A2), A2*1.1, 0) applies a markup only when the cell holds a number, returning zero otherwise.
Practical tip: when a formula is not behaving, break it into pieces. Suppose =IFERROR(VLOOKUP(TRIM(A2), Employees, 3, FALSE), "Missing") is returning Missing for a name you can see in the Employees table.
Start by isolating each layer. In a blank cell, type =TRIM(A2) and verify the cleaned value. Then in another cell type =VLOOKUP(TRIM(A2), Employees, 3, FALSE) without the IFERROR wrapper. That will reveal whether the issue is the lookup itself or the error handler.
Nine times out of ten, the answer is hidden whitespace, a leading apostrophe turning numbers into text, or a typo in the column index. The discipline of decomposing the formula reveals the bug fast.
Absolute references catch a lot of beginners. When you write =B2*$C$1 and drag it down, the B2 changes to B3, B4, and so on (relative), but $C$1 stays locked (absolute). That dollar sign is the difference between a formula that works for one row and one that scales to 10,000 rows.
Press F4 while editing a reference to cycle through absolute and mixed modes. Fastest way to lock columns or rows without typing dollar signs by hand.
One more debugging tip: name your helper cells with clear labels in the adjacent column. When a formula spans five helper cells, labeling each (Raw, Trimmed, Looked Up, Capitalized, Final) makes the workbook readable months later when you have forgotten what each step does.
One area beginners often skip is date and time formulas, but they show up constantly in real work. =TODAY() returns the current date and updates every time the workbook recalculates. =NOW() does the same with a timestamp included.
=DATEDIF(start, end, "d") calculates the days between two dates, useful for age calculations, project durations, and contract terms. =EOMONTH(date, 0) returns the last day of the date’s month, perfect for monthly cutoffs. =WEEKDAY(date) returns a number from 1 to 7 representing the day of the week.
Text formulas matter too, especially when data arrives messy. =TRIM(A2) strips leading, trailing, and double spaces. =LEFT(A2, 3) grabs the first three characters. =RIGHT(A2, 4) grabs the last four. =MID(A2, 5, 2) grabs two characters starting at position five.
=CONCAT(A2, " ", B2) joins first and last names with a space. These cleanup formulas are how you turn imported CSV data into something you can actually use. Without them, dirty data stops your analysis before it starts.
One concept that confuses newcomers is the difference between values and references. When you type =5+3 into a cell, you get 8 and that result stays put no matter what changes elsewhere. When you type =A1+B1, you get the live answer based on whatever A1 and B1 contain right now. Change A1 and the result updates instantly.
This is the magic of spreadsheets and also the most common source of frustration. If someone changes a source cell upstream from your formula, your output changes too. Sometimes that is exactly what you want. Sometimes it breaks a snapshot you wanted to preserve. Excel lets you paste values (Paste Special, Values Only) to freeze a calculation into static data when you need to lock it down.
Conditional formulas are where spreadsheets earn their reputation as decision engines. COUNTIF counts cells matching a condition: =COUNTIF(A2:A100, "East") returns the count of cells in that range equal to East. AVERAGEIF averages only cells meeting a condition: =AVERAGEIF(A2:A100, "East", B2:B100) averages B values where A equals East.
The plural versions handle multiple criteria. COUNTIFS, SUMIFS, AVERAGEIFS each accept pairs of range and criterion arguments. The order of arguments switches between the singular and plural versions, which trips up beginners constantly. SUMIF puts the sum range last; SUMIFS puts it first. Memorize this and you avoid hours of debugging.
You will also encounter the cousin functions: SUMPRODUCT for multiplying and summing arrays in one step, AGGREGATE for handling errors and hidden rows automatically, and SUBTOTAL for filtered ranges that respect Excel filtering. Each has a niche where it shines. SUMPRODUCT in particular is a Swiss army knife that some analysts use in place of SUMIFS because it has been around longer and runs on older Excel versions.
Excel 365 or 2021+. Default choice for new lookups. Handles missing values cleanly, searches in any direction, survives column inserts.
Older Excel versions or when you need to look up values to the left of the key column. More verbose than XLOOKUP but very reliable.
Legacy workbooks where the team is comfortable with VLOOKUP. Only when the lookup column is to the left of the return column.
Rare cases where data is laid out horizontally instead of vertically. Modern practice is to transpose the data and use VLOOKUP or XLOOKUP instead.
Named ranges are the secret weapon of professional Excel users. Instead of writing =SUM(B2:B100), you select the range, name it Sales (Formulas tab, Define Name), and write =SUM(Sales). The formula reads like English and you can rename it without rebuilding every dependent formula.
Pair named ranges with tables (Insert Table on the Insert tab) and your workbook becomes self-documenting. Table columns get automatic structured references like Sales[Amount]. Adding a row extends every formula automatically. No more dragging formulas down when new data arrives. This combination is what separates a beginner-built workbook from one a senior analyst would ship to production.
The path from beginner to confident formula user is shorter than most people think. You do not need to memorize every function. You need to recognize patterns. When you see a column of numbers, your brain should reach for SUM, AVERAGE, or COUNTIF.
When you see two tables that need to talk to each other, your brain should reach for VLOOKUP, XLOOKUP, or INDEX/MATCH. When you see a column that needs a decision, your brain should reach for IF or IFS. That pattern-matching instinct comes from practice, not from studying.
Practice deliberately. Open a sample dataset and set yourself a question. What is the total sales for region East in Q2? Write the formula without looking anything up. Get it wrong, look up what you needed, and try again tomorrow with a different question.
After two weeks of daily practice on real datasets, you will be writing SUMIFS and XLOOKUPs without breaking stride. The hiring managers running Excel screening tests are not looking for someone who has memorized every function. They are looking for someone who can break a problem into formula-sized pieces and solve it.
That skill is what every practice test, certification exam, and on-the-job test is really measuring. Build it deliberately, and the formulas will follow. Take a practice test now to see where you stand and which formulas need more reps.
Beyond memorizing functions, the most underrated skill is keyboard fluency. Ctrl+; inserts today’s date as a static value. Ctrl+Shift+: inserts the current time. Alt+= autofills SUM for the column above. F4 toggles absolute references while editing. F9 evaluates a selection in the formula bar. Mastering these shortcuts compounds over time. An analyst who knows fifteen keyboard combinations writes formulas twice as fast as one who relies on the mouse, and that speed difference is what hiring managers notice during live screening tests.
Consistent practice on real datasets is the surest path from beginner curiosity to confident formula craftsmanship that hiring managers genuinely value during interviews.