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

=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,"-","")