All formulas

=VLOOKUP(E2,A2:B10,2,FALSE)

VLOOKUP with an Exact Match

Look up a value in another table and return the matching result — with the FALSE argument that stops VLOOKUP returning wrong answers.

=XLOOKUP(E2,A2:A10,B2:B10,"Not found")

XLOOKUP: The Modern Lookup Formula

XLOOKUP replaces VLOOKUP with a simpler, safer lookup: exact match by default, looks in any direction, and has a built-in not-found message.

=INDEX(B2:B10,MATCH(E2,A2:A10,0))

INDEX + MATCH Lookup

The classic flexible lookup: MATCH finds the row, INDEX returns the value. Works in every Excel version and looks in any direction.

=COUNTIFS(A2:A20,"Sales",C2:C20,"Overdue")

Count Rows That Meet Multiple Conditions

Use COUNTIFS to count rows matching several conditions at once — like overdue tasks in one department — in Excel and Google Sheets.

=SUMIFS(C2:C20,A2:A20,"Sales",B2:B20,">100")

Sum Values That Meet Multiple Conditions

Use SUMIFS to total only the rows that match your conditions — like sales for one region above a threshold — in Excel and Google Sheets.

=COUNTIF(C2:C11,"Complete")/COUNTA(C2:C11)

Calculate a Completion Percentage

Divide completed items by total items with COUNTIF and COUNTA to get a live completion rate for tasks, orders, or projects.

Calculate Days Overdue

Subtract the due date from today to see how many days late each item is — clamped to zero so future dates don't go negative.

=MAX(0,TODAY()-B2)

=IF(AND(B2<TODAY(),C2<>"Complete"),"Overdue","On Track")

Flag Overdue Tasks Automatically

Mark a task Overdue when its due date has passed and it isn't complete, using IF with AND — the backbone of every tracker.

=LEFT(A2,FIND(" ",A2)-1)

Extract the First Name from a Full Name

Split "Ana Torres" into just "Ana" — a universal LEFT + FIND version, plus the cleaner modern formulas for Excel 365 and Google Sheets.

Remove Extra Spaces from Text

TRIM strips leading, trailing, and doubled spaces that break lookups and comparisons — the first fix for any imported data.

=TRIM(A2)

Combine First and Last Names

Join name columns into one full-name column with & or TEXTJOIN — including the trick that avoids stray spaces when a cell is blank.

=A2&" "&B2

=IF(COUNTIF(B:B,A2)=0,"Missing","In list")

Find Values Missing From Another List

Check every value in one list against another and flag the ones that don't appear anywhere in the second list.

Remove Duplicates and Keep One of Each

Get a clean list with each value appearing exactly once, without deleting anything from your original data.

=UNIQUE(A2:A20)

Count How Many Times Each Value Appears

Put a count next to every row showing how many times its value appears in the column — any count above 1 means it's a duplicate.

=COUNTIF(A:A,A2)

=COUNTIF(C2:C20,"Complete")

Count Rows With a Specific Status

Count how many rows in a status column say Complete — or any other exact text — with a single COUNTIF formula.

=IF(A2="","Blank","Filled")

Find Blank Cells in a Column

Flag every empty cell in a column with a simple IF check, so missing entries stand out instead of hiding in the data.

Count Blank Cells in a Range

Get a single number showing how many cells in a range are empty — a quick data-completeness check with COUNTBLANK.

=COUNTBLANK(A2:A20)

=COUNTIF($A$2:$A$20,A2)>1

Highlight Duplicate Values With Color

Use a COUNTIF rule in conditional formatting to automatically color every cell whose value appears more than once.

=AND($B2<TODAY(),$C2<>"Complete")

Highlight Overdue Rows Automatically

A conditional formatting rule that colors the whole row when a due date has passed and the task still isn't marked Complete.

Calculate Days Remaining Until a Due Date

Subtract today from the due date to count down the days left — clamped to zero so past-due items don't show negative numbers.

=MAX(0,B2-TODAY())

Calculate Percentage Change Between Two Values

Measure growth or decline between two numbers with (new-old)/old, formatted as a percent — the standard month-over-month math.

=(B2-A2)/A2

=SUMIFS(C:C,B:B,">="&DATE(2026,7,1),B:B,"<"&DATE(2026,8,1))

Sum Values for a Single Month

Total every transaction that falls inside one month by bracketing SUMIFS between the first of the month and the first of the next.

Group Dates by Month with a Helper Column

Turn each date into a "2026-07" label with TEXT so you can pivot, sort, and subtotal by month — the setup step behind every monthly report.

=TEXT(B2,"yyyy-mm")

Rank Values from Highest to Lowest

Assign each row its position in the list — 1 for the biggest number — with RANK, without sorting the data itself.

=RANK(B2,$B$2:$B$20,0)

Calculate a Running Total Down a Column

Build a cumulative sum with SUM($B$2:B2) — the anchored-start range expands one row at a time as you fill down.

=SUM($B$2:B2)

=XLOOKUP(E2,A:A,B:B,"",0,-1)

Pull the Latest Record for a Name

Get the most recent entry for a person or item with XLOOKUP searching bottom-up — perfect for logs where new rows land at the end.

=MID(A2,FIND(" ",A2)+1,100)

Extract the Last Name from a Full Name

Pull "Torres" out of "Ana Torres" — a universal MID + FIND version, plus cleaner one-liners for Excel 365 and Google Sheets.

=MID(A2,FIND("@",A2)+1,100)

Extract the Domain from an Email Address

Pull "acme.com" out of "ana@acme.com" with MID and FIND — perfect for grouping contacts by company.

Capitalize Names Properly

PROPER turns "ana torres" or "ANA TORRES" into "Ana Torres" — the one-function fix for shouty or lowercase imports.

=PROPER(A2)

Split Text into Columns by a Delimiter

Break "Ana,Sales,Chicago" into separate columns with one formula — TEXTSPLIT in Excel 365, SPLIT in Google Sheets.

=TEXTSPLIT(A2,",")

=IF(B2>=70,"Pass","Fail")

Create a Pass/Fail Status Column

Turn a score column into clear Pass or Fail labels with a single IF — the simplest and most-used conditional formula there is.

=IFS(B2>=90,"Excellent",B2>=70,"Pass",TRUE,"Fail")

Create a Status with Multiple Conditions

Grade scores into Excellent, Pass, or Fail with IFS — cleaner than nested IFs, with a TRUE catch-all so nothing slips through.

=MAXIFS(C:C,A:A,"Sales")

Find the Highest Value in a Category

MAXIFS returns the largest number that matches a condition — like the biggest deal in the Sales department — in one formula.

=MINIFS(C:C,A:A,"Sales")

Find the Lowest Value in a Category

MINIFS returns the smallest number that matches a condition — the cheapest quote, the earliest date, the lowest score per group.

Calculate Budget Variance in Dollars and Percent

Subtract budget from actual to get variance in dollars, then divide by budget to get variance percent for any line item.

=B2-A2

Calculate Profit Margin From Revenue and Cost

Divide profit by revenue to get profit margin — the share of every sale you actually keep — and avoid mixing it up with markup.

=(B2-A2)/B2

Calculate an Invoice Due Date From Payment Terms

Add your payment terms to the invoice date — like =B2+30 for net-30 — to get due dates that update automatically.

=B2+30

Calculate CAGR (Compound Annual Growth Rate)

One formula turns a start value, end value, and number of years into the steady annual growth rate — the honest way to compare growth.

=(B2/A2)^(1/C2)-1

Calculate Return on Investment (ROI)

Divide the gain by what you put in — (return − cost) / cost — to see how hard each dollar worked, as a clean percentage.

=(B2-A2)/A2

Calculate Gross Profit and Gross Margin

Revenue minus cost of goods gives gross profit in dollars; divide by revenue and you have the margin percentage in the next column.

=B2-A2

Calculate Simple Interest on a Loan

Principal × rate × years gives the interest a simple-interest loan charges, and one more step gives the total you'll repay.

=A2*B2*C2

Calculate Average Order Value (AOV)

Divide total revenue by the number of orders to get AOV — with an IF guard so months with zero orders don't blow up in #DIV/0!.

=B2/C2

Calculate Customer Churn Rate

Customers lost divided by customers at the start of the period gives your churn rate — and 1 minus churn gives retention.

=B2/A2

Calculate Runway in Months From Cash and Burn

Cash in the bank divided by monthly burn tells you how many months you can operate — with a guard for the happy case of zero burn.

=A2/B2

Convert Currency With a Fixed Exchange Rate

Multiply each amount by an exchange rate stored in one $-anchored cell, so the whole column converts and updates from a single rate.

=A2*$E$1

Calculate the Days Between Two Dates

Subtract one date from another to count the days between them — order to delivery, invoice to payment, project start to finish.

=B2-A2

Count Business Days Between Two Dates

NETWORKDAYS counts only Monday-to-Friday days between two dates — the right math for SLAs, payment terms, and turnaround times.

=NETWORKDAYS(A2,B2)

Add Business Days to a Date

WORKDAY jumps forward a set number of working days, skipping weekends — ideal for promised ship dates and payment-terms deadlines.

=WORKDAY(A2,10)

Get the Month Name From a Date

TEXT with the "mmmm" format turns any date into its month name — July, not 7 — for report labels and monthly groupings.

=TEXT(A2,"mmmm")

="Q"&ROUNDUP(MONTH(A2)/3,0)

Get the Quarter From a Date

Turn any date into Q1–Q4 with one formula — divide the month by 3 and round up — for quarterly sales, pipeline, and budget rollups.

Find the Last Day of the Month for Any Date

EOMONTH returns the month-end date for any date — the standard tool for billing cutoffs, month-end close, and end-of-month due dates.

=EOMONTH(A2,0)

Get the First Day of the Next Month

EOMONTH plus one day lands on the 1st of the following month — the clean way to compute billing starts and new-period kickoff dates.

=EOMONTH(A2,0)+1

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Check Whether a Date Falls on a Weekend

WEEKDAY with return type 2 numbers the week Monday=1 to Sunday=7, so anything above 5 is a weekend — one IF turns that into a label.

=IF(ISNUMBER(SEARCH("rush",A2)),"Priority","Standard")

Flag Rows Where a Cell Contains Specific Text

Label an order Priority when its notes mention "rush" — SEARCH finds the word anywhere in the cell and IF turns the result into a clean status.

=IF(AND(B2>=10,B2<=20),"In range","Out of range")

Test Whether a Number Falls Between Two Values

AND checks both ends of the range in one IF — label order quantities that qualify for a carton discount, with the boundaries counted correctly.

=IF(A2<>"","Filled","Missing")

Test Whether a Cell Is Filled or Empty

Flag rows with a missing PO number using a simple not-equal-to-empty test — and know when it disagrees with ISBLANK on formula-made blanks.

=IF(B2>=100,4.5,IF(B2>=50,5.25,6))

Build Three-Tier Results with Nested IF Statements

Put one IF inside another to return a third outcome — tiered unit pricing by order quantity, checked from the strictest condition down.

=IF(OR(A2="Rush",B2>500),"Escalate","Normal")

Flag a Row When Any One Condition Is True

OR inside IF fires when any test passes — escalate an order if it shipped Rush or its value tops $500, without writing two formulas.

=IF(AND(A2="Open",OR(B2>30,C2="High")),"Review","OK")

Combine AND with OR in a Single IF Formula

Nest OR inside AND to express rules like "Open, and either older than 30 days or high priority" — the grouping decides everything.

Return a Blank Instead of a Zero

Swap zeros for empty cells so reports read cleanly — and understand how the invisible "" quietly changes averages computed on that column.

=IF(A2=0,"",A2)

=IF(COUNTIF($D$2:$D$5,A2)>0,"Approved","Not approved")

Check Whether a Value Appears in a List

COUNTIF counts how often a value appears in a reference list — wrap it in IF to mark each vendor Approved or Not approved in one pass.

=XLOOKUP(E2,A2:A5,C2:C5)

INDEX MATCH vs XLOOKUP: Which Lookup to Use

Use XLOOKUP when you have it — INDEX MATCH remains the answer for older Excel and true two-way row-and-column lookups. Both verified side by side below.

=SUMIFS(B2:B50,A2:A50,"East",B2:B50,">1000")

SUMIFS vs SUMPRODUCT: When Each One Wins

SUMIFS wins for plain AND conditions; switch to SUMPRODUCT when you need OR logic or a calculation inside the condition.

=IFNA(VLOOKUP(D2,A2:B10,2,FALSE),"Not on list")

IFERROR vs IFNA: Which Errors to Catch

IFNA only catches #N/A — perfect for lookups; IFERROR hides every error, including the typos and broken refs you need to see. Default to IFNA.

COUNT vs COUNTA vs COUNTBLANK: Which to Use

COUNT tallies numbers only, COUNTA anything non-empty, and COUNTBLANK the gaps — three answers from the same column, verified on one grid.

=COUNTA(A2:A50)

SEARCH vs FIND: When Case Sensitivity Matters

SEARCH ignores case and accepts wildcards, so it's the everyday choice; FIND is for exact-case matches — and errors with #VALUE! on a case miss.

=SEARCH("lamp",A2)

=TEXTJOIN(", ",TRUE,A2:A5)

CONCATENATE vs TEXTJOIN: Which Way to Combine Text

TEXTJOIN takes the delimiter once and can skip blanks; CONCATENATE makes you glue every piece by hand — use TEXTJOIN wherever your Excel has it.

TODAY vs NOW: Which Date Function to Use

TODAY returns just the date; NOW adds clock time that breaks date comparisons — use TODAY for deadlines, NOW only when the hour matters.

=TODAY()

=VLOOKUP(E2,A2:C5,3,FALSE)

VLOOKUP vs HLOOKUP: Which One Fits Your Table

VLOOKUP reads tables that run down the page — nearly all of them; reach for HLOOKUP only when your headers run across the top.

=IFS(B2>=90,"Gold",B2>=75,"Silver",TRUE,"Bronze")

IF vs IFS: When to Stop Nesting

IFS reads as flat condition-result pairs, best for three or more tiers with a TRUE catch-all; nested IF still wins for one either/or and pre-2019 Excel.

=INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G2,B1:D1,0))

Two-Way Lookup with INDEX and MATCH

Pull a value from a grid by matching both a row label and a column header — one MATCH finds the row, another finds the column.

=XLOOKUP(F2&"|"&G2,D2:D5,C2:C5)

Look Up a Value Using Multiple Criteria

Match on two columns at once — like region AND product — by joining them into one helper key and looking that up with XLOOKUP.

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

Extract Text Between Two Characters

Pull the text inside parentheses or between any two markers — FIND locates both characters and MID grabs what sits between them.

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1

Count the Words in a Cell

Count words by counting spaces: strip the spaces out with SUBSTITUTE, compare lengths, and add 1 — with TRIM handling messy spacing.

=MID(A2,FIND(",",A2)+2,99)&" "&LEFT(A2,FIND(",",A2)-1)

Swap Last Name, First Name into First Last

Turn "Torres, Ana" into "Ana Torres" — MID pulls everything after the comma, LEFT grabs everything before it, and & rejoins them.

Remove Characters from the Start, End, or Anywhere

Strip the first N characters with MID, the last N with LEFT and LEN, or every copy of a specific character with SUBSTITUTE.

=MID(A2,3,999)

Lock a Cell Reference with the Dollar Sign

The $ sign stops a reference from shifting when you copy a formula — $E$1 stays pinned on every row while B2 moves with it.

=B2*$E$1

=IF(B2<=C2,"Reorder","OK")

Flag Inventory Items That Need Reordering

Compare stock on hand to each item's reorder point with IF, so low items flag themselves as Reorder the moment counts drop.

Calculate Sales Commission With a Flat or Tiered Rate

Multiply sales by the commission rate for flat payouts, or nest IF to apply tiered rates that rise with performance.

=B2*C2

=COUNTIF(D:D,"Interview")

Track Job Applications by Status With COUNTIF

Count how many applications sit at each stage — Applied, Interview, Offer, Rejected — with COUNTIF on your tracker's status column.

Calculate Each Item's Percentage of the Total

Divide each row by the grand total with an anchored SUM to see what share each category, product, or region contributes.

=B2/SUM($B$2:$B$6)

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

Calculate a Weighted Average

SUMPRODUCT divided by SUM gives an average where big items count more — the correct math for average price, blended rates, and scores.

=DATEDIF(B2,TODAY(),"Y")

Calculate Years Between Two Dates

DATEDIF returns completed years between two dates — the standard way to compute age, tenure, or account lifetime from a start date.

=AVERAGEIF(A:A,"Sales",C:C)

Calculate an Average by Category

AVERAGEIF averages only the rows matching a condition — average deal size per region, spend per vendor, hours per project.

=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

Count Unique Values in a Column

How many different customers, products, or codes does a column contain? One formula per platform — plus the classic that works everywhere.

=QUERY(A1:D20,"select A, D where D > 1000 order by D desc",1)

Filter and Sort Data With QUERY

QUERY runs a SQL-style select over your data — filter deals over 1,000 and sort them largest-first in a single Google Sheets formula.

=ARRAYFORMULA(B2:B10*C2:C10)

Apply One Formula to a Whole Column With ARRAYFORMULA

One ARRAYFORMULA in the top cell calculates every row below it — no more copying quantity × price down the column by hand.

=REGEXMATCH(A2,"@gmail\.com$")

Flag Rows That Match a Text Pattern With REGEXMATCH

REGEXMATCH returns TRUE when a cell matches a regular expression — flag customers on personal Gmail addresses in one Google Sheets formula.

=IMPORTRANGE("spreadsheet_url","Orders!A1:D20")

Pull Data From Another Spreadsheet With IMPORTRANGE

IMPORTRANGE streams a live block of data from one Google Sheets file into another — orders flow into your report without copy-pasting.

Show a Trend as a Mini Chart Inside a Cell

SPARKLINE draws a tiny chart inside a single cell — one per row shows every customer's sales trend at a glance, no chart objects needed.

=SPARKLINE(B2:M2)

=GOOGLEFINANCE("NASDAQ:AAPL","price")

Pull Live Stock Prices Into Your Spreadsheet

GOOGLEFINANCE fetches current and historical market prices straight into cells — a self-updating portfolio or holdings tracker in one formula.

=FILTER(A2:C10,C2:C10>1000,"No matches")

Filter Rows That Match a Condition

FILTER returns every row where a condition is true — a live, self-updating extract of the deals, orders, or invoices that match.

=LET(total,SUM(B2:B10),count,COUNTA(B2:B10),total/count)

Write Readable Formulas by Naming Values with LET

LET names the moving parts of a formula — define SUM(B2:B10) once as total, then reuse it — so long formulas get readable and faster.

Generate Number Sequences with One Formula

SEQUENCE spills a list of numbers from a single formula — row numbers, invoice numbers, or a date series with any start and step.

=SEQUENCE(10)

=SUMPRODUCT((A2:A10="Sales")*(B2:B10))

Sum with Conditions and Math Using SUMPRODUCT

Multiply TRUE/FALSE arrays and SUMPRODUCT adds only the matching rows — OR logic and row-by-row calculations that SUMIFS can't do.

=SWITCH(B2,"L","Lead","Q","Qualified","W","Won","Unknown")

Replace Nested IFs with a Clean SWITCH

SWITCH checks one value against a list of exact matches and returns the first hit — flatter and far easier to edit than nested IFs.

Calculate a Monthly Loan Payment with PMT

PMT returns the fixed monthly payment on a loan from the rate, term, and amount — the fast answer to what a loan costs per month.

=PMT(6%/12,60,-25000)

=XLOOKUP(E2,A2:A5,C2:C5)

XLOOKUP vs VLOOKUP: Which Lookup to Use

Both pull a matching value from another table — XLOOKUP does it with safer defaults. Here's when each one wins, and the traps when you switch.

=COUNTIFS(A2:A50,"East",B2:B50,">1000")

COUNTIF vs COUNTIFS: One Condition or Many

COUNTIF counts rows matching one condition; COUNTIFS handles any number — and works identically with one, so many people just always use COUNTIFS.

=SUMIFS(B2:B50,A2:A50,"East",B2:B50,">1000")

SUMIF vs SUMIFS: The Argument-Order Trap

SUMIF puts the sum range LAST; SUMIFS puts it FIRST. That reversal is the single biggest source of broken conditional totals — here's both patterns.

Calculate Sales Tax on an Order

Multiply the subtotal by the rate for the tax amount, or by one-plus-the-rate for the total — plus the formula that backs tax out of a total.

=B2*0.08

Calculate a Discounted Price

Multiply the price by one minus the percent-off to get the sale price — and see why a 20% then 10% discount is not 30% off.

=B2*(1-C2)

Calculate Your Break-Even Point

Divide fixed costs by price minus variable cost per unit to find how many units you must sell before the business stops losing money.

=B2/(C2-D2)

Catch Any Formula Error With IFERROR

Wrap a risky calculation in IFERROR to swap any error for a fallback like 0 — keeping totals, charts, and reports working.

=IFERROR(A2/B2,0)

Find and Replace Text With SUBSTITUTE

SUBSTITUTE swaps every occurrence of one piece of text for another — strip dashes from phone numbers, drop currency symbols, or fix separators in bulk.

=SUBSTITUTE(A2,"-","")