How-to guides

Clear, copy-paste answers to 462+ spreadsheet tasks — with the right formula for both Excel and Google Sheets. Browse by topic or jump straight to what you need.

Comparisons (X vs Y)

XLOOKUP vs VLOOKUP: which to use

XLOOKUP looks in any direction, defaults to exact match, and has a built-in not-found value. Here is when to use each over VLOOKUP.

SUMIF vs SUMIFS: when to use each in Excel and Sheets

Understand when to use SUMIF (one condition) versus SUMIFS (multiple conditions) for conditional summing in Excel and Google Sheets.

COUNTIF vs COUNTIFS: how they differ in Excel and Sheets

Learn the difference between COUNTIF (single condition) and COUNTIFS (multiple conditions) for counting in Excel and Google Sheets.

INDEX MATCH vs VLOOKUP: which is better in Excel and Sheets

Compare INDEX MATCH and VLOOKUP to decide which lookup method fits your needs in Excel and Google Sheets.

IF vs IFS: simplifying nested conditions in Excel and Sheets

Learn when to use IF vs IFS to handle multiple conditions without deeply nesting formulas in Excel and Google Sheets.

Excel ≠ Sheets

CONCAT vs CONCATENATE vs TEXTJOIN in Excel and Sheets

Understand the differences between CONCAT, CONCATENATE, and TEXTJOIN for joining text in Excel and Google Sheets with delimiters and ranges.

Excel ≠ Sheets

FIND vs SEARCH: case sensitivity in Excel and Sheets

Learn the key difference between FIND and SEARCH — FIND is case-sensitive, SEARCH is not — and when to use each in Excel and Google Sheets.

ROUND vs ROUNDUP vs ROUNDDOWN in Excel and Sheets

Master Excel and Google Sheets rounding functions: ROUND for standard rounding, ROUNDUP to always round away from zero, and ROUNDDOWN to always truncate.

FILTER vs QUERY in Google Sheets

Compare Google Sheets FILTER and QUERY functions: FILTER is simple and formula-driven, QUERY uses SQL-like syntax for powerful multi-condition data slicing.

Excel ≠ Sheets

UNIQUE function vs Remove Duplicates in Excel and Sheets

Learn when to use the UNIQUE formula versus the Remove Duplicates tool in Excel and Google Sheets to eliminate or surface distinct values.

Excel ≠ Sheets

IFERROR vs IFNA: which to use in Excel and Sheets

Understand when to use IFERROR to catch all errors vs IFNA to catch only #N/A errors in Excel and Google Sheets lookup formulas.

TEXT vs VALUE: converting between numbers and text

Learn how to use TEXT to format numbers as readable strings and VALUE to convert text back to numbers for calculations in Excel and Google Sheets.

SUBSTITUTE vs REPLACE in Excel and Sheets

Understand when to use SUBSTITUTE (replace by content) vs REPLACE (replace by position) for text manipulation in Excel and Google Sheets.

SUM vs SUMPRODUCT for conditional totals

Compare SUM with SUMPRODUCT for conditional totals in Excel and Sheets — SUMPRODUCT handles array conditions without Ctrl+Shift+Enter in older Excel.

VLOOKUP vs HLOOKUP: vertical vs horizontal lookups

Understand when to use VLOOKUP for column-based lookups vs HLOOKUP for row-based lookups, and why XLOOKUP is now preferred in Excel and Sheets.

INDEX vs OFFSET: stable vs volatile references in Excel and Sheets

INDEX and OFFSET can both return a cell by position, but INDEX is non-volatile and faster. Here is when to use each in Excel and Sheets.

COUNTA vs COUNT: counting text and numbers in Excel and Sheets

COUNT tallies only numbers; COUNTA tallies every non-empty cell including text. Here is how to pick the right one in Excel and Sheets.

MAX vs LARGE: top value vs the nth value in Excel and Sheets

MAX returns the single highest value; LARGE returns the nth highest. Here is when to use each in Excel and Google Sheets.

MIN vs SMALL: lowest value vs the nth lowest in Excel and Sheets

MIN returns the single lowest value; SMALL returns the nth lowest. Here is when to use each in Excel and Google Sheets.

SUMPRODUCT vs SUMIFS: weighted and conditional sums in Excel and Sheets

SUMIFS sums by criteria; SUMPRODUCT multiplies arrays before summing. Here is when each wins in Excel and Google Sheets.

TEXTJOIN vs CONCATENATE: joining with a delimiter in Excel and Sheets

TEXTJOIN joins a whole range with a delimiter and can skip blanks; CONCATENATE cannot. Here is when to use each in Excel and Sheets.

TODAY vs NOW: date only vs date and time in Excel and Sheets

TODAY returns the current date; NOW returns the current date and time. Here is when to use each in Excel and Google Sheets.

Excel ≠ Sheets

DATEDIF vs YEARFRAC: measuring durations in Excel and Sheets

DATEDIF returns whole completed years/months/days; YEARFRAC returns a fractional year. Here is when to use each in Excel and Sheets.

STDEV.P vs STDEV.S: population vs sample deviation in Excel and Sheets

STDEV.P measures spread for a full population; STDEV.S estimates it from a sample. Here is how to choose in Excel and Sheets.

MOD vs QUOTIENT: remainder vs whole-number division in Excel and Sheets

MOD returns the remainder of a division; QUOTIENT returns the whole-number part. Here is when to use each in Excel and Sheets.

CHOOSE vs SWITCH: selecting a value by index or match in Excel and Sheets

CHOOSE picks a result by position number; SWITCH picks by matching a value. Here is when to use each in Excel and Sheets.

NETWORKDAYS vs WORKDAY: counting vs projecting workdays in Excel and Sheets

NETWORKDAYS counts business days between two dates; WORKDAY projects a future date N workdays out. Learn which to use in Excel and Google Sheets.

VALUE vs NUMBERVALUE: converting text to numbers in Excel and Sheets

VALUE converts text in your locale's format to a number; NUMBERVALUE lets you specify the decimal and group separators explicitly. Learn which to use in Excel and Google Sheets.

RANK vs RANK.EQ: legacy and modern ranking in Excel and Sheets

RANK and RANK.EQ produce identical results; RANK.EQ is the modern name retained for clarity. Learn the difference and when each applies in Excel and Google Sheets.

Excel ≠ Sheets

AVERAGEIF vs AVERAGEIFS: one or many conditions in Excel and Sheets

AVERAGEIF averages values matching a single condition; AVERAGEIFS supports multiple criteria ranges and conditions. Learn which to use in Excel and Google Sheets.

LEFT vs MID: extracting text from a start point in Excel and Sheets

LEFT extracts characters from the beginning of a string; MID extracts from any position. Understand when each is the right choice in Excel and Google Sheets.

IFERROR vs ISERROR: handling vs detecting errors in Excel and Sheets

IFERROR replaces an error with a fallback value in one step; ISERROR detects whether an error exists and returns TRUE/FALSE. Learn which fits your formula in Excel and Google Sheets.

SORT vs SORTBY: sorting by self or by another column in Excel and Sheets

SORT sorts a range by one of its own columns; SORTBY sorts a range by a separate helper array or column not in the output. Learn which to use in Excel and Google Sheets.

How to calculate attainment against a quota

Calculate quota attainment in Excel or Google Sheets — divide actual results by target quota to express performance as a percentage, then layer in pacing and conditional formatting.

Modern Excel (LET, LAMBDA)

How to build a dynamic summary block with LET

Use LET to name intermediate calculations once and compose a dynamic summary block in Excel and Google Sheets — cleaner than nested SUMIFS repeated across cells.

How to use LET to write readable formulas

Simplify complex nested formulas by naming intermediate values with LET in Excel and Google Sheets — making formulas easier to audit and maintain.

How to write a recursive LAMBDA function

Build self-referencing recursive functions using LAMBDA with the Excel Name Manager or Google Sheets named functions — for tasks like factorial, Fibonacci, or tree traversal.

How to use LAMBDA with MAP across a range

Use MAP with a LAMBDA to apply a custom formula to every cell in a range in Excel 365 and Google Sheets, returning a transformed array without helper columns.

How to use LAMBDA with REDUCE for cumulative math

Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.

How to use LAMBDA with SCAN for a running balance

Use SCAN with a LAMBDA to compute a running balance or cumulative total in Excel 365 and Google Sheets, returning one result per row without helper columns.

How to use BYROW and BYCOL to apply a LAMBDA per row or column

BYROW and BYCOL apply a LAMBDA to each row or column of a range in Excel 365 and Google Sheets, returning a per-row or per-column result without helper rows or columns.

How to use MAKEARRAY with LAMBDA

MAKEARRAY generates a 2-D array of any size in Excel 365 and Google Sheets by calling a LAMBDA with each row and column index, letting you build multiplication tables, calendars, or computed grids.

How to save a LAMBDA as a named function

Save a LAMBDA as a named function via Excel's Name Manager or Google Sheets' Named Functions to reuse custom formulas like built-ins anywhere in your workbook.

Pivot alternatives (formula approach)

How to build a pivot-style summary with formulas

Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.

How to use GETPIVOTDATA to pull values from a pivot table

Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.

How to write formulas for a pivot table calculated field

Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.

How to calculate percent of total without a pivot table

Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.

How to group data by month using formulas instead of a pivot

Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.

How to group data by week using formulas instead of a pivot

Sum transaction data by week number or ISO week using SUMIFS and WEEKNUM in Excel and Google Sheets — a formula-only alternative to pivot date grouping.

How to find the top N per group with formulas

Rank and filter the top N rows within each category group using LARGE, FILTER, and SUMPRODUCT in Excel and Google Sheets — no pivot table needed.

How to count distinct values without a pivot table

Count unique values in a column using SUMPRODUCT(1/COUNTIF) in Excel or COUNTUNIQUE in Google Sheets — no pivot table required.

Excel ≠ Sheets

How to create a running total by group

Build a running total that resets for each group using SUMIFS with an expanding range anchor in Excel and Google Sheets — a formula alternative to pivot subtotals.

How to build a cross-tabulation with formulas

Replicate a pivot table cross-tab layout using SUMIFS in a row-column grid in Excel and Google Sheets — fully dynamic with no pivot refresh needed.

Google Sheets specific

How to use ARRAYFORMULA to apply a formula to a whole column

Wrap any formula in ARRAYFORMULA in Google Sheets to apply it to an entire column without copying it to each row.

Excel ≠ Sheets

How to use QUERY to filter data

Use Google Sheets QUERY with a WHERE clause to filter rows dynamically — Excel users can use FILTER as the equivalent.

Excel ≠ Sheets

How to use QUERY to sum by group

Use Google Sheets QUERY with GROUP BY and SUM to aggregate data by category — use SUMIF for the Excel equivalent.

Excel ≠ Sheets

How to use IMPORTRANGE from another spreadsheet

Pull live data from a different Google Sheets file using IMPORTRANGE, or replicate the pattern in Excel with Power Query.

Excel ≠ Sheets

How to combine ARRAYFORMULA with VLOOKUP

Apply a VLOOKUP to an entire column at once using ARRAYFORMULA in Google Sheets or spill arrays in Excel 365.

Excel ≠ Sheets

How to split text down a whole column with ARRAYFORMULA

Use ARRAYFORMULA with SPLIT to break delimited text across an entire column in one formula in Google Sheets.

Excel ≠ Sheets

How to build a pivot-style summary with QUERY

Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.

Excel ≠ Sheets

How to use QUERY to group by multiple columns

Use QUERY GROUP BY with multiple columns in Google Sheets to create multi-level summaries. Excel alternative uses SUMIFS with multiple criteria ranges.

Excel ≠ Sheets

How to use QUERY with WHERE, AND, OR

Filter QUERY results in Google Sheets using WHERE with AND and OR conditions. Excel users can replicate this with FILTER using * for AND and + for OR.

Excel ≠ Sheets

How to create a running total with QUERY

Build a running total in Google Sheets by combining QUERY for ordering with ARRAYFORMULA and SUMIF. Excel alternative uses a simple SUMIF with a locked anchor row.

Excel ≠ Sheets

How to combine QUERY with IMPORTRANGE

Combine QUERY and IMPORTRANGE in Google Sheets to filter and aggregate data from another spreadsheet file. There is no Excel equivalent — use Power Query to connect workbooks instead.

Excel ≠ Sheets

How to use cell references inside QUERY criteria

Inject cell references into a QUERY formula's WHERE clause in Google Sheets using string concatenation. Excel uses FILTER with direct cell references instead.

Excel ≠ Sheets

How to skip header rows in QUERY

Control how many header rows QUERY reads in Google Sheets using the headers argument — set it to 0, 1, or -1 to fix missing or doubled headers. Excel FILTER needs no equivalent setting.

Excel ≠ Sheets

How to use QUERY with date comparisons

Filter rows by date in Google Sheets QUERY using the date literal syntax. Excel alternative uses FILTER with comparison operators on date serial numbers.

Excel ≠ Sheets

How to use QUERY with LIKE wildcards

Use QUERY's LIKE operator with % wildcards to filter rows by partial text matches in Google Sheets. Excel alternative uses FILTER with SEARCH or ISNUMBER/FIND.

Excel ≠ Sheets

How to use QUERY for conditional aggregation

Use QUERY with WHERE and GROUP BY to conditionally aggregate data in Google Sheets — equivalent to SUMIFS or COUNTIFS but with richer filtering and label control.

Excel ≠ Sheets

Finance & SaaS metrics

How to calculate tiered commission

Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.

How to calculate gross margin

Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.

How to calculate compound interest

Compute a compounded future value with principal × (1 + rate)^years using POWER. Identical in Excel and Google Sheets.

How to calculate cost per unit

Divide total cost by total units to find cost per unit using a simple formula in Excel or Google Sheets.

How to calculate a selling price from a target margin

Use cost and target gross margin percentage to back-calculate the required selling price in Excel or Google Sheets.

How to calculate simple interest

Multiply principal, rate, and time to compute simple interest using a straightforward formula in Excel or Sheets.

How to calculate a loan payment

Use the PMT function to calculate periodic loan payments given a principal, interest rate, and number of periods.

How to calculate a monthly loan payment

Compute the exact monthly payment for any loan using the PMT function with monthly rate and total payment count.

How to calculate future value

Use the FV function to project the future value of an investment given a rate, number of periods, and optional payments.

How to calculate present value

Use the PV function to find today's value of a future cash flow or series of payments discounted at a given rate.

How to calculate markup

Divide the difference between selling price and cost by cost to calculate markup percentage in Excel or Google Sheets.

How to calculate profit margin

Divide net profit by revenue to calculate gross or net profit margin percentage in Excel and Google Sheets.

How to calculate sales tax

Multiply the pre-tax price by the tax rate to compute the sales tax amount, or by (1 + rate) to get the total price.

How to calculate VAT

Multiply the net price by the VAT rate to find the VAT amount, or use (1 + rate) to compute the VAT-inclusive total.

How to calculate a discount

Multiply the original price by the discount rate to find the discount amount, then subtract to get the discounted price.

How to calculate commission

Multiply sales revenue by the commission rate to compute the commission earned in Excel and Google Sheets.

How to calculate ROI

Calculate Return on Investment by dividing net gain by the initial cost, expressed as a percentage in Excel or Sheets.

How to calculate the break-even point

Divide fixed costs by the contribution margin (price minus variable cost) to find the break-even quantity.

How to calculate CAGR

Compute Compound Annual Growth Rate by raising the ratio of end value to start value to the power of (1/years) minus 1.

How to convert currency

Multiply an amount by an exchange rate to convert between currencies; use a rate table and VLOOKUP for multi-currency conversions.

How to calculate an hourly rate

Divide annual salary by total working hours per year to compute an effective hourly rate in Excel or Google Sheets.

How to calculate depreciation

Use the SLN function for straight-line depreciation or DB for declining balance; both work in Excel and Google Sheets.

How to calculate net pay

Calculate net pay by subtracting total deductions from gross pay using a simple formula in Excel or Google Sheets.

How to calculate overtime pay

Calculate regular and overtime pay by applying a higher rate to hours exceeding a threshold using IF and MAX in Excel and Google Sheets.

How to calculate a performance bonus

Calculate a tiered performance bonus as a percentage of salary based on a rating or achievement level using IF or IFS in Excel and Google Sheets.

How to prorate an amount

Prorate a monthly fee, salary, or charge based on the number of days active in a period using simple division and multiplication.

How to calculate a running balance

Build a running balance column in a ledger by referencing the previous balance and adding or subtracting the current transaction amount.

How to split a bill evenly

Divide a total bill evenly among a group and optionally add a tip, calculating each person's share with a simple formula.

How to convert an annual salary to monthly

Divide an annual salary by 12 to get the monthly gross amount, with options for semi-monthly and bi-weekly pay periods.

How to calculate GST

Calculate GST (Goods and Services Tax) on a pre-tax amount or extract GST from a GST-inclusive total in Excel and Google Sheets.

How to calculate income tax with slabs

Calculate progressive income tax using nested IF statements or a lookup table to apply different rates across salary brackets in Excel and Google Sheets.

How to calculate take-home pay

Calculate take-home (net) pay by subtracting income tax, social security, and other deductions from gross pay in Excel and Google Sheets.

How to calculate a remaining loan balance

Use PV or the CUMPRINC function to find the remaining principal balance on a loan after a given number of payments in Excel and Google Sheets.

How to work out the monthly savings to reach a goal

Use PMT to calculate the monthly savings amount needed to reach a future financial goal given a timeline and interest rate.

How to calculate a tip and total

Calculate a tip amount and grand total from a subtotal and tip percentage using straightforward multiplication in Excel and Google Sheets.

How to build a loan amortization schedule

Build a full loan amortization schedule row by row using PMT, IPMT, and PPMT in Excel and Google Sheets, with running balance and cumulative interest columns.

How to build a depreciation schedule

Build a straight-line or declining-balance depreciation schedule using SLN and DDB in Excel and Google Sheets, with annual book value tracking.

How to calculate Net Promoter Score (NPS)

Calculate Net Promoter Score by segmenting survey responses into Promoters, Passives, and Detractors, then applying the NPS formula in Excel and Google Sheets.

How to calculate churn rate

Calculate monthly or annual customer churn rate as lost customers divided by starting customers, expressed as a percentage, in Excel and Google Sheets.

How to calculate a conversion rate

Calculate conversion rate as conversions divided by total visitors or leads, expressed as a percentage, with IFERROR to handle zero-denominator months in Excel and Google Sheets.

How to calculate customer lifetime value

Calculate customer lifetime value (CLV) using average purchase value, purchase frequency, and customer lifespan in Excel and Google Sheets.

How to calculate days sales outstanding (DSO)

Calculate Days Sales Outstanding (DSO) by dividing accounts receivable by total credit sales, then multiplying by the number of days in the period, in Excel and Google Sheets.

How to build a fixed payment schedule

Build a full fixed-payment amortization schedule in Excel or Google Sheets using PMT, IPMT, and PPMT to break each period into interest and principal.

How to calculate an annual run rate

Calculate an annual run rate from partial-year actuals in Excel or Google Sheets — extrapolate any period's revenue or metric to a full-year estimate with a single formula.

How to calculate sell-through rate

Calculate sell-through rate in Excel or Google Sheets — divide units sold by units received to measure inventory performance and flag overstock risk as a percentage.

How to calculate the effective annual interest rate

Calculate the effective annual rate (EAR) in Excel or Google Sheets using the EFFECT function to compare interest rates with different compounding frequencies on a fair basis.

How to calculate monthly recurring revenue (MRR)

Learn how to calculate MRR in Excel and Google Sheets using SUMIF to sum active subscription revenue by billing period, normalized to a monthly figure.

How to calculate annual recurring revenue (ARR)

Calculate ARR in Excel and Google Sheets by summing normalized annual subscription revenue across all active accounts, converting monthly contracts to their annual equivalent.

How to calculate customer acquisition cost (CAC)

Calculate CAC in Excel and Google Sheets by dividing total sales and marketing spend by the number of new customers acquired in the same period.

How to calculate the CAC payback period

Calculate the CAC payback period in Excel and Google Sheets by dividing CAC by the monthly gross profit contribution per customer to find months to recover acquisition cost.

How to calculate monthly burn rate

Calculate monthly burn rate in Excel and Google Sheets by subtracting total monthly cash outflows from cash inflows, then averaging over recent months for a reliable run rate.

How to calculate cash runway in months

Calculate cash runway in months in Excel and Google Sheets by dividing current cash balance by average monthly net burn rate — a critical survival metric for startups.

How to calculate net revenue retention (NRR)

Calculate NRR in Excel and Google Sheets using the canonical formula: (Starting MRR + Expansion − Churn − Contraction) / Starting MRR, expressed as a percentage.

How to calculate gross revenue retention (GRR)

Calculate GRR in Excel and Google Sheets as (Starting MRR − Churn − Contraction) / Starting MRR — the revenue retained from existing customers, capped at 100%.

How to calculate the SaaS magic number

Calculate the SaaS magic number in Excel and Google Sheets: (Current Quarter ARR − Prior Quarter ARR) × 4 ÷ Prior Quarter S&M Spend — a sales efficiency benchmark.

How to calculate the SaaS rule of 40

Calculate the SaaS rule of 40 in Excel and Google Sheets: Revenue Growth Rate (%) + Profit Margin (%). A score ≥ 40 signals a healthy balance of growth and profitability.

How to calculate average revenue per user (ARPU)

Calculate ARPU in Excel and Google Sheets by dividing total recurring revenue for a period by the average number of active users — a key unit-economics benchmark.

How to calculate average revenue per account (ARPA)

Calculate ARPA in Excel and Google Sheets by dividing total MRR by the number of active paying accounts — the right unit-economics metric for B2B SaaS with multi-seat accounts.

How to calculate the SaaS quick ratio

Calculate the SaaS quick ratio (new MRR + expansion MRR) / (churned MRR + contraction MRR) in Excel and Google Sheets to measure revenue growth efficiency.

How to calculate the LTV-to-CAC ratio

Calculate the LTV-to-CAC ratio in Excel and Google Sheets using average revenue per customer, gross margin, churn rate, and average cost to acquire a customer.

Inventory & operations

How to flag low-stock items

Use an IF formula to compare current stock to a reorder threshold and display a low-stock warning automatically.

How to calculate a reorder point

Multiply average daily usage by lead time and add safety stock to calculate the reorder point in Excel or Sheets.

How to calculate total stock value

Multiply units on hand by unit cost per item and sum all rows to find total inventory value using SUMPRODUCT.

How to calculate days of inventory on hand

Divide current stock by average daily sales to calculate how many days of inventory remain in Excel or Google Sheets.

How to track a running inventory balance

Use a running SUM with an expanding reference to add receipts and subtract issues for a live inventory balance.

How to calculate capacity utilization

Calculate capacity utilization in Excel or Google Sheets — divide actual output by maximum available capacity to measure operational efficiency as a percentage.

Unit conversions

How to convert minutes to hours

Turn a number of minutes into decimal hours by dividing by 60. The same simple formula in Excel and Google Sheets.

How to convert inches to centimeters

Multiply the inch value by 2.54 or use the CONVERT function to change inches to centimeters in Excel or Google Sheets.

How to convert kilograms to pounds

Multiply kilograms by 2.20462 or use CONVERT to change kg to pounds in Excel or Google Sheets instantly.

How to convert Celsius to Fahrenheit

Multiply Celsius by 9/5 and add 32, or use the CONVERT function, to change temperatures in Excel or Google Sheets.

How to convert miles to kilometers

Multiply miles by 1.60934 or use CONVERT with 'mi' and 'km' units to change miles to kilometers in Excel or Sheets.

How to convert feet to meters

Multiply feet by 0.3048 or use CONVERT with 'ft' and 'm' to change feet to meters in Excel or Google Sheets.

How to convert bytes to megabytes

Divide byte values by 1048576 (2^20) to convert bytes to megabytes in Excel or Google Sheets.

How to convert hours to minutes

Multiply a time value by 1440 or multiply decimal hours by 60 to convert hours to total minutes in Excel and Google Sheets.

Conditional formatting

How to highlight overdue dates

Use Conditional Formatting with a formula like =A2<TODAY() to automatically highlight past-due dates red in Excel and Google Sheets.

How to highlight duplicate values

Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.

How to highlight cells greater than a value

Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.

How to highlight the highest value in a range

Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.

How to highlight the lowest value in a range

Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.

How to highlight blank cells

Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.

How to highlight cells that contain specific text

Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.

How to shade every other row

Use conditional formatting with MOD and ROW to automatically apply alternating row colors for a banded, readable table layout.

How to highlight weekend dates

Use conditional formatting with WEEKDAY to automatically highlight Saturday and Sunday dates in any calendar or date column.

How to highlight a row based on one cell value

Use conditional formatting with a mixed reference formula to highlight an entire row whenever a specific column's value meets your condition.

How to highlight the top 10 percent

Use conditional formatting with PERCENTILE to automatically highlight the highest-performing values in a dataset — no manual sorting needed.

How to highlight values not in another list

Use conditional formatting with COUNTIF to flag values in one column that do not appear in a reference list, revealing discrepancies instantly.

How to highlight cells that contain errors

Use conditional formatting with ISERROR to automatically flag formula errors like #N/A, #DIV/0!, or #VALUE! across your spreadsheet.

Dates & time

How to calculate age from a date of birth

Turn a birth date into an age in years with DATEDIF and TODAY. Works the same in Excel and Google Sheets.

How to calculate days between two dates

Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.

How to add months to a date

Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.

How to flag overdue dates

Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.

How to calculate age from a birthday

Use DATEDIF with TODAY() to calculate a person's exact age in years from a birth date cell in Excel and Google Sheets.

How to calculate the days between two dates

Subtract one date from another, or use DAYS(), to find the number of calendar days between two dates in Excel and Google Sheets.

How to calculate the months between two dates

Use DATEDIF with "M" or the MONTHS formula to calculate the complete months between two dates in Excel and Google Sheets.

How to calculate the years between two dates

Use DATEDIF with "Y" or YEARFRAC to count the complete years or fractional years between two dates in Excel and Google Sheets.

How to add days to a date

Add a number directly to a date cell to move forward by that many calendar days in Excel and Google Sheets.

How to add months to a date

Use EDATE to add a specific number of months to a date in Excel and Google Sheets, correctly handling month-end dates.

How to add years to a date

Use EDATE with months multiplied by 12, or DATE with YEAR+n, to add whole years to a date in Excel and Google Sheets.

How to subtract one date from another

Subtract two date cells to find the number of days between them, or use DATEDIF for months and years, in Excel and Google Sheets.

How to get the day of the week from a date

Use TEXT with "dddd" to return the weekday name, or WEEKDAY to return a number 1-7 representing the day, from any date cell.

How to get the month name from a date

Use TEXT with "mmmm" format to extract the full month name, or "mmm" for the abbreviated name, from a date in Excel or Sheets.

How to get the quarter from a date

Use ROUNDUP with MONTH divided by 3 to derive the fiscal or calendar quarter number (1-4) from any date in Excel or Sheets.

How to get the week number from a date

Use WEEKNUM to return the ISO or US week number (1-53) for any date in Excel and Google Sheets, with configurable week start.

How to count business days between two dates

Use NETWORKDAYS to count working days (Mon-Fri) between two dates, with optional holidays, in Excel and Google Sheets.

How to add business days to a date

Use WORKDAY to calculate a future date after adding a specific number of business days, skipping weekends and optional holidays.

How to get the first day of the month

Use DATE with YEAR and MONTH to return the first day of any month from a given date in Excel and Google Sheets.

How to get the last day of the month

Use EOMONTH with 0 as the offset to return the last day of the month for any date in Excel and Google Sheets.

How to convert a date to text

Use TEXT() with a format code to convert a date value to a formatted text string in Excel and Google Sheets.

How to convert text to a date

Use DATEVALUE to parse a text string that looks like a date into a real date serial number in Excel and Google Sheets.

How to calculate elapsed time between two times

Subtract start time from end time and format as [h]:mm:ss to calculate elapsed time including durations over 24 hours.

How to check if a date falls on a weekend

Use WEEKDAY to check if a date is a Saturday or Sunday and return TRUE/FALSE or a custom label in Excel and Google Sheets.

How to get the fiscal year from a date

Calculate the fiscal year from a date by adjusting the month offset relative to your fiscal year start using IF or YEAR and MONTH.

How to find the number of days in a month

Use EOMONTH and DAY to find how many days are in any given month, handling leap years automatically.

How to calculate the time difference between two times

Subtract two time values and format the result to display hours, minutes, or seconds elapsed between them.

How to get the day name from a date

Return the full day name (Monday, Tuesday, etc.) from a date using TEXT with the 'dddd' format code.

How to get the start of the week for a date

Use DATE and WEEKDAY to find the Monday (or Sunday) that starts the week containing any given date.

How to get the end of the week for a date

Calculate the Friday or Sunday that ends the week for any date using WEEKDAY arithmetic in Excel or Google Sheets.

How to find the next occurrence of a weekday

Find the next Monday, Wednesday, or any weekday after a given date using WEEKDAY arithmetic in Excel and Google Sheets.

How to count how many of a weekday are in a month

Count the number of Mondays, Fridays, or any specific weekday occurring within a given month using SUMPRODUCT and WEEKDAY.

How to calculate age in years, months, and days

Use DATEDIF to break an age or duration into completed years, remaining months, and remaining days from a birthdate.

How to calculate employee tenure

Calculate how long an employee has worked by finding the difference between their start date and today (or an end date) using DATEDIF.

How to count the days until a deadline

Subtract today's date from a deadline date to get the remaining days, then use MAX to avoid showing negative numbers for past deadlines.

How to round a time to the nearest 15 minutes

Round any time value to the nearest 15-minute interval using MROUND in Excel and Google Sheets.

How to get the start date of a quarter

Calculate the first day of the fiscal or calendar quarter for any date using DATE, MONTH, and integer division.

How to convert a serial number to a date

Turn an Excel or Sheets date serial number into a human-readable date by formatting or using the TEXT or DATEVALUE functions.

How to calculate how many days something is overdue

Find how many days past the due date a task or invoice is using TODAY() minus the due date, clamped to zero for items not yet due.

How to sum hours worked from a timesheet

Sum time values in a timesheet and display totals exceeding 24 hours correctly using the [h]:mm format in Excel or Google Sheets.

Excel ≠ Sheets

How to convert a timestamp to just a date

Strip the time component from a datetime timestamp in Excel or Google Sheets using INT, TRUNC, or DATEVALUE/TEXT combinations.

How to calculate working hours excluding a lunch break

Calculate net working hours by subtracting a fixed lunch break from the time difference between clock-in and clock-out in Excel and Google Sheets.

How to list every Monday in a month

List all Mondays in any month in Excel or Google Sheets using SEQUENCE and WEEKDAY — no helper columns needed, works for any year and month combination.

Excel ≠ Sheets

How to generate a series of dates

Generate a date sequence in Excel or Google Sheets with SEQUENCE — produce daily, weekly, monthly, or custom-step series from a start date in a single formula.

How to sum values by fiscal quarter

Sum values by fiscal quarter in Excel or Google Sheets with SUMPRODUCT and MOD — handle any fiscal year start month without helper columns or pivot tables.

How to count overlapping date ranges

Count overlapping date ranges in Excel or Google Sheets with SUMPRODUCT — detect how many date intervals share at least one day with a reference range, no VBA required.

Text manipulation

How to remove extra spaces

Clean leading, trailing, and double spaces with TRIM — the usual fix for lookups that fail on values that look identical.

How to convert text to a number

Turn numbers stored as text into real numbers with VALUE or by multiplying by 1 — the fix for sums that ignore values.

How to extract the domain from an email address

Pull everything after the @ from an email using FIND and MID. Works the same in Excel and Google Sheets.

How to extract the first name from a full name

Get the first name from a full name with LEFT and FIND, splitting on the first space. Works in Excel and Google Sheets.

How to add leading zeros

Pad numbers to a fixed width with leading zeros using TEXT, or a custom number format. Works in Excel and Google Sheets.

How to remove specific characters

Strip out a character — like dashes or currency symbols — with SUBSTITUTE. Nest it to remove several. Works in both apps.

How to find the position of a character

Locate where a character appears in text using FIND — the basis for splitting codes or extracting parts of a string.

How to check if a cell contains specific text

Test whether a cell contains a substring using ISNUMBER and SEARCH. Returns TRUE or FALSE; works in both apps.

How to pad numbers with leading zeros

Add leading zeros to numbers to a fixed width using RIGHT and concatenation — great for IDs and codes. Same in both apps.

How to split text into columns

Use LEFT, MID, RIGHT with FIND/SEARCH to split delimited text into separate columns with formulas.

How to split first and last name

Extract first and last names from a single cell using LEFT, MID, FIND, and LEN formulas.

How to extract the last name

Pull the last name from a "First Last" or "Last, First" formatted cell using MID, FIND, and LEN.

How to extract numbers from a text string

Pull numeric digits out of mixed text strings using array formulas, VALUE, and MID combinations.

Excel ≠ Sheets

How to extract text from a string

Use LEFT, RIGHT, MID, and FIND to pull specific substrings from any position within a cell.

How to remove the first character from a cell

Drop the first character of a string with RIGHT and LEN — a simple two-function formula.

How to remove the last N characters from a cell

Strip trailing characters from a string using LEFT and LEN — one clean formula handles any count.

How to remove non-numeric characters

Strip letters and symbols from a cell to leave only digits using array formulas or REGEXREPLACE.

Excel ≠ Sheets

How to remove line breaks from a cell

Replace CHAR(10) line breaks in a cell with a space or nothing using SUBSTITUTE or CLEAN.

How to remove leading zeros

Convert text-stored numbers with leading zeros to true numeric values using VALUE or multiplication.

How to capitalize the first letter

Uppercase the first character of a string while leaving the rest unchanged using UPPER, LEFT, and MID.

How to convert text to uppercase

Convert any text string to ALL CAPS instantly using the UPPER function in Excel and Google Sheets.

How to convert text to lowercase

Convert any text string to all-lowercase using the LOWER function in Excel and Google Sheets.

How to convert text to proper case

Title-case a text string so each word starts with a capital using the PROPER function.

How to reverse a text string

Reverse the order of characters in a string using a TEXTJOIN array formula or MID with SEQUENCE.

Excel ≠ Sheets

How to find and replace text with a formula

Use SUBSTITUTE to programmatically find and replace text within a cell without using the Find & Replace dialog.

How to replace multiple values at once

Chain SUBSTITUTE calls or use REDUCE/LAMBDA to replace many different strings in one formula.

How to combine text from two cells

Join two cell values into one using the & operator or CONCAT/CONCATENATE functions.

How to combine text with a delimiter

Join multiple cells with a comma, dash, or any separator using TEXTJOIN or the & operator.

How to get the text before a character

Extract everything to the left of a delimiter using LEFT and FIND, or the new TEXTBEFORE function.

How to get the text after a character

Extract everything to the right of a delimiter using MID and FIND, or the modern TEXTAFTER function.

How to get the text between two characters

Extract a substring between two delimiter characters using MID and FIND, or REGEXEXTRACT in Sheets.

Excel ≠ Sheets

How to check if text starts with a value

Test whether a cell begins with a specific prefix using LEFT or STARTSWITH-style logic with IF.

How to check if text ends with a value

Test whether a cell ends with a specific suffix using RIGHT or a COUNTIF wildcard pattern.

How to convert a number to text

Use TEXT() to convert a number to a formatted string in Excel and Google Sheets, controlling decimal places, commas, and currency symbols.

How to generate initials from a name

Extract the first letter of each word in a full name to build initials using LEFT, MID, FIND, and related text functions.

How to count occurrences of a word in a cell

Use LEN and SUBSTITUTE to count how many times a specific word appears inside a single cell in Excel and Google Sheets.

How to extract the state from an address

Pull a US state abbreviation from a full address string using MID and FIND to locate commas and spaces.

How to extract a zip code from an address

Extract a 5-digit US ZIP code from the end of an address string using RIGHT and TRIM, or a regex in Sheets.

Excel ≠ Sheets

How to format a phone number

Format a 10-digit number string as (###) ###-#### using TEXT, or combine LEFT, MID, and RIGHT for more control.

How to mask part of a string

Hide sensitive characters in a string by replacing middle characters with asterisks using LEFT, RIGHT, and REPT.

How to get the nth word from a sentence

Extract the Nth word from a text string using TRIM, MID, and FIND with SUBSTITUTE to locate word boundaries.

How to get the last word in a cell

Extract the last word from a text string using TRIM, RIGHT, SUBSTITUTE, and LEN to locate the final space.

How to get the first word in a cell

Extract the first word from a text string using LEFT and FIND to locate the first space.

How to swap first and last name

Rearrange 'First Last' to 'Last, First' using FIND, LEFT, MID, and LEN to split on the space.

How to remove everything after a character

Strip all text after a delimiter character using LEFT and FIND to keep only the portion before the character.

How to remove everything before a character

Extract only the text after a delimiter using MID and FIND to skip everything up to and including the character.

How to extract a file extension from a filename

Pull the file extension from a filename string using RIGHT and FIND on the last period in the name.

How to extract the username from an email

Get the username (part before @) from an email address using LEFT and FIND.

How to count the lines in a cell

Count the number of lines in a multi-line cell by counting line break characters with LEN and SUBSTITUTE.

How to repeat a character N times

Use the REPT function to repeat any character or string a specified number of times in a cell.

How to create a URL slug from text

Convert a title into a lowercase, hyphenated URL slug using LOWER and SUBSTITUTE to replace spaces.

How to remove a currency symbol from a number

Strip currency symbols like $ or £ from text-formatted numbers using SUBSTITUTE or VALUE with TRIM.

How to add a prefix to every cell in a column

Prepend a fixed string to every cell in a column by concatenating the prefix with the cell reference using & or CONCAT.

How to add a suffix to every cell in a column

Append a fixed string to every cell in a column by concatenating the cell reference with the suffix using &.

How to extract every number from a text string

Extract all numbers embedded in a text string in Google Sheets with REGEXEXTRACT, or use a TEXTSPLIT/TRIM approach in Excel 365, to pull every numeric value from mixed text.

Excel ≠ Sheets

How to parse a CSV string inside a single cell

Split a comma-separated string stored in a single cell into individual values using TEXTSPLIT in Excel 365 or SPLIT in Google Sheets, spilling each value into its own cell.

Excel ≠ Sheets

How to extract hashtags from a text string

Pull every #hashtag from a social media post or text cell in Google Sheets with REGEXEXTRACT, or use TEXTSPLIT and FILTER in Excel 365 to isolate tokens that start with #.

Excel ≠ Sheets

How to normalize phone numbers to a single format

Strip formatting characters from phone numbers and reformat them to a consistent pattern in Excel and Google Sheets using SUBSTITUTE and TEXT, or REGEXREPLACE in Sheets.

Excel ≠ Sheets

How to extract a currency amount from a text string

Extract a numeric currency amount from a text string like "Total: $1,234.56 due" in Google Sheets with REGEXEXTRACT, or use SUBSTITUTE and VALUE in Excel for a numeric result.

Excel ≠ Sheets

Lookups

How to VLOOKUP from another sheet

Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.

How to VLOOKUP with multiple criteria

Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.

How to fix VLOOKUP returning #N/A

VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.

How to return multiple values with VLOOKUP

Use multiple VLOOKUP formulas with different column index numbers, or INDEX MATCH, to retrieve several fields for a single lookup value.

How to do a two-way lookup

Use INDEX with two MATCH functions to look up a value at the intersection of a matching row and a matching column.

How to look up the last matching value

Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.

How to do a lookup with wildcards

Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.

How to do a case-sensitive lookup

Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.

How to do a reverse (left) lookup

Use INDEX and MATCH to look up a value and return data from a column to its left — something VLOOKUP cannot do.

How to look up a value and sum the matches

Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.

How to use INDEX MATCH with two criteria

Combine INDEX and MATCH with multiplication of two criteria arrays to perform a lookup that matches on two conditions simultaneously.

How to find the closest matching value

Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.

How to look up a value across multiple columns

Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.

How to return the column header of a matching value

Use INDEX and MATCH together to return the column header (from row 1) of the column that contains a specific value in a table.

How to get the value of the last non-empty cell

Return the last non-blank value in a column or row using LOOKUP, INDEX/MATCH, or COUNTA tricks.

How to look up and return multiple values

Use FILTER (Sheets/Excel 365) or an INDEX/SMALL/IF array formula to return all matches for a lookup value.

Excel ≠ Sheets

How to combine VLOOKUP with IF

Wrap VLOOKUP inside IF to return a custom value when the lookup fails or to apply logic to the returned result before displaying it.

How to write a nested VLOOKUP

Use a VLOOKUP inside another VLOOKUP to chain two lookups when the result of the first determines where the second lookup should search.

How to use VLOOKUP across multiple sheets

Reference another sheet in the VLOOKUP table_array argument using SheetName! notation to look up data stored on a different tab.

How to look up and return an entire row

Use INDEX with MATCH to return multiple columns from a matched row, or use XLOOKUP in Excel 365 and Sheets to spill an entire row of results.

How to find the row number of a value

Use MATCH to return the relative position of a value within a range, or add ROW of the first cell to convert it to the actual worksheet row number.

How to find the column number of a value

Use MATCH on a single row to return the relative column position of a header or value, optionally combined with COLUMN to get the absolute column number.

How to do a two-dimensional lookup

Combine INDEX with two MATCH calls — one for the row and one for the column — to look up a value at the intersection of a row and column header.

How to look up the most recent date for an item

Use MAXIFS (Excel) or MAX with IF as an array formula to find the latest date associated with a specific item in a list.

How to do an approximate-match lookup

Use VLOOKUP or MATCH with match_type 1 (TRUE) on a sorted table to find the closest value less than or equal to your lookup value, ideal for tax or grade brackets.

How to look up and concatenate all matches

Use TEXTJOIN with IF as an array formula in Excel, or TEXTJOIN with FILTER in Google Sheets, to find all matching values and join them into one cell.

Excel ≠ Sheets

How to use XLOOKUP with multiple criteria

Concatenate criteria columns in XLOOKUP's lookup array to match on multiple conditions at once, returning results only when all criteria align.

How to use VLOOKUP with a wildcard

Use asterisk (*) or question mark (?) wildcards in VLOOKUP's lookup value to match partial text, with exact match mode (FALSE) required.

How to find the first matching value in a row

Use MATCH with a horizontal range to locate the column position of the first value meeting your criteria, then pair with INDEX to retrieve it.

How to look up the nth matching value

Use SMALL with IF as an array formula to retrieve the position of the nth match, then INDEX to return the corresponding value from any column.

Logic & conditional

How to hide #N/A and other formula errors

Replace #N/A, #DIV/0!, and other errors with a blank or a friendly message using IFERROR or IFNA. Works in Excel and Google Sheets.

How to sum if a cell contains specific text

Use SUMIF with wildcard criteria (*text*) to sum values in a column whenever a related column contains a specific substring.

How to write a nested IF formula

Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.

How to write an IF with multiple conditions

Combine IF with AND or OR to test multiple conditions simultaneously and return a result based on whether all or any are true.

How to combine IF and AND

Use IF with AND to return a value only when all specified conditions are simultaneously true in Excel or Google Sheets.

How to combine IF and OR

Use IF with OR to return a value when at least one of several conditions is true in Excel or Google Sheets.

How to check if a cell is blank with IF

Use IF with ISBLANK or an empty string comparison to detect blank cells and return a custom value or skip calculations.

How to return a value if a cell contains text

Use IF with ISNUMBER and SEARCH to check if a cell contains specific text and return a custom result.

How to return blank instead of zero

Use IF to test for zero and return an empty string, keeping your spreadsheet clean and charts free of unwanted zero markers.

How to flag duplicate values

Use COUNTIF to flag duplicate values in a list with a simple formula that marks any entry appearing more than once.

How to flag values over a threshold

Use IF with a comparison to label each cell as 'Above' or 'Below' a numeric threshold without manual inspection.

How to assign a letter grade from a score

Convert numeric scores to letter grades automatically using nested IF or IFS — no manual lookup tables needed.

Excel ≠ Sheets

How to categorize values into buckets

Group numeric values into named categories (buckets) using nested IF or a lookup table for clean, readable segmentation.

How to create a pass or fail formula

Return Pass or Fail based on a score or condition using a simple IF formula — works identically in Excel and Google Sheets.

How to return the first non-blank value

Retrieve the first non-empty cell in a row or column using COALESCE in Sheets or an INDEX/MATCH combination in Excel.

Excel ≠ Sheets

How to return the last non-blank value

Pull the last filled cell in a column or row using LOOKUP or INDEX/MATCH to get the most recent entry automatically.

Data cleaning & shaping

How to remove duplicates

Remove duplicate values with one formula using UNIQUE, or strip them in place with the built-in tool. Works in Excel 365 and Google Sheets.

How to compare two columns

Check whether two columns match row by row, or find values in one column missing from another, using IF and COUNTIF.

How to merge two columns

Combine two columns into one with the & operator — perfect for joining first and last names into a full name.

How to create a sequential invoice number

Automatically generate sequential invoice numbers with a prefix and zero-padded counter using TEXT and ROW in Excel and Google Sheets.

How to number rows within each group

Assign sequential row numbers that restart for each group using COUNTIF with a mixed reference in Excel and Google Sheets.

How to get a sorted list of unique values

Extract a sorted, deduplicated list from a column using SORT+UNIQUE in modern Excel and Google Sheets, or array formulas in older versions.

How to find duplicate values

Identify which values appear more than once in a list using COUNTIF to add a Duplicate/Unique label to each row.

How to remove blank rows

Delete empty rows quickly by filtering for blanks and deleting visible rows, or use Go To Special in Excel for a one-click approach.

How to transpose rows to columns

Flip rows and columns using the TRANSPOSE function or Paste Special to convert horizontal data to vertical and vice versa.

How to combine multiple columns into one

Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.

Excel ≠ Sheets

How to fill blank cells with a value

Replace empty cells with a default value using IF+ISBLANK, Go To Special fill-down, or a Power Query step for bulk data.

How to number rows automatically

Add sequential row numbers automatically using ROW() so numbers update if rows are inserted or deleted.

How to create a sequential list of numbers

Generate a list of sequential numbers using ROW, SEQUENCE (Sheets/365), or a simple fill handle drag for quick setup.

Excel ≠ Sheets

How to generate random numbers

Generate random integers or decimals using RAND and RANDBETWEEN — both recalculate on every sheet change unless pasted as values.

How to create a drop-down list

Add a validated drop-down list to any cell using Data Validation to restrict input to a predefined set of options.

How to reverse the order of a list

Flip a list upside-down using INDEX with ROW arithmetic in Excel, or SORT with descending order in Google Sheets.

Excel ≠ Sheets

How to compare two lists for differences

Find values that exist in one list but not the other using COUNTIF to label each item as Matched or Unmatched.

How to find common values between two lists

Identify values present in both lists using COUNTIF — any item with a count greater than zero exists in both lists.

How to find missing values between two lists

Identify items in one list that are absent from another using COUNTIF — a count of zero means the value is missing.

How to split a cell into multiple rows

Break a delimited cell value into separate rows using Power Query in Excel or SPLIT combined with TRANSPOSE in Google Sheets.

Excel ≠ Sheets

How to auto-number rows that have data

Assign sequential numbers only to rows containing data using IF with COUNTA, skipping blank rows automatically.

How to create a dynamic range with OFFSET

Build a range that grows automatically as you add rows using OFFSET and COUNTA — the basis of a dynamic named range for charts and formulas.

How to distribute a total evenly across rows

Distribute a total evenly across a fixed number of rows, handling remainder cents using MOD and ROW to ensure the distributed values always sum exactly to the original total.

How to build a frequency distribution

Build a frequency distribution in Excel or Google Sheets using FREQUENCY — count how many values fall in each bin with one array formula, no COUNTIF loop required.

How to sum by both row and column criteria

Sum values where both a row and a column label match in Excel or Google Sheets using SUMIFS for flat data or SUMPRODUCT for flexible two-dimensional aggregation.

How to rank values with a tiebreaker

Assign unique ranks in Excel or Google Sheets when values tie — combine RANK with COUNTIFS on a secondary criterion to eliminate duplicate rank positions.

Percentages & business math

How to calculate a percentage

Work out what percentage one number is of another with a simple division, then format the result as a percent. Same in Excel and Google Sheets.

How to calculate percentage change

Find the percentage increase or decrease between two numbers with (new − old) / old, then format as a percent. Works in Excel and Sheets.

How to add a percentage to a number

Increase a number by a percentage with number × (1 + rate) — handy for tax, markup, or raises. Same in both apps.

How to calculate a cumulative percentage

Use a running SUM divided by the total SUM to build a cumulative percentage column in Excel or Google Sheets.

How to calculate attendance percentage

Divide attended sessions by total sessions and multiply by 100 to get attendance percentage in Excel or Google Sheets.

How to calculate the percentage of target achieved

Divide actual results by the target goal to calculate percentage of target achieved in Excel or Google Sheets.

How to calculate a percentage of a percentage

Multiply two percentages together correctly in Excel and Google Sheets to find a percentage of a percentage.

How to calculate the percentage of a total

Divide each value by the total using SUM with an absolute reference to find every item's share of the whole as a percentage.

How to calculate the percentage difference between two numbers

Calculate the relative percentage difference between two values using (new-old)/old, expressed as a percentage.

How to subtract a percentage from a number

Multiply a value by (1 minus the percentage) to subtract a percentage discount or reduction in one step.

How to increase a number by a percentage

Multiply a value by (1 plus the percentage) to apply a percentage increase such as a price rise or markup.

How to work out what percentage one number is of another

Divide the part by the whole to find what percentage one number represents of another, then format as a percentage.

How to calculate percentage complete

Divide completed units by total units to track progress as a percentage, useful for project management and task tracking.

How to calculate a growth rate

Calculate a simple or compound growth rate between a start and end value using straightforward arithmetic or the RATE function.

How to calculate year-over-year growth

Calculate YoY growth by dividing the current year's value minus the prior year's value by the prior year's value.

How to calculate month-over-month growth

Compute MoM growth by comparing each month's value to the prior month using (current - prior) / prior, formatted as a percentage.

How to calculate market share

Divide a company's sales by total market sales to compute its percentage market share using simple division and percentage formatting.

Average & statistics

How to rank values

Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.

How to calculate standard deviation

Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.

How to calculate a weighted average

Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.

How to calculate an average

Use the AVERAGE function to calculate the arithmetic mean of a range of numbers in Excel or Google Sheets.

How to average ignoring zeros

Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.

How to average ignoring blank cells

AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.

How to average with a condition

Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.

How to calculate a moving average

Compute a rolling average for each row using AVERAGE with a fixed-window OFFSET or INDEX formula anchored to each row.

How to find the median of a range

Use the MEDIAN function to find the middle value in a dataset — the value that half the data falls above and half below.

How to find the most common value

Use MODE for numbers or COUNTIF-based logic for text to find the value that appears most frequently in a dataset.

How to calculate standard deviation

Use STDEV.S for a sample or STDEV.P for a full population to measure the spread of values around the mean.

Excel ≠ Sheets

How to calculate variance

Use VAR.S for sample variance or VAR.P for population variance to quantify data dispersion in Excel or Google Sheets.

Excel ≠ Sheets

How to calculate a percentile

Use PERCENTILE.INC or PERCENTILE.EXC to find the value at a given percentile rank within a dataset.

Excel ≠ Sheets

How to rank values without ties

Use RANK combined with COUNTIF to break ties and assign a unique rank to every value in a list.

How to find the nth largest value

Use the LARGE function to return the nth highest value from a range — 1 for the maximum, 2 for the second largest, and so on.

How to calculate the range of a set of values

Subtract the MIN from the MAX of a dataset to calculate the statistical range in Excel or Google Sheets.

How to find outliers in a dataset

Use IQR-based bounds with QUARTILE to flag outliers in your data using an IF formula in Excel or Google Sheets.

How to find the most frequent text value

Use INDEX/MATCH with COUNTIF to find the most frequently occurring text value in Excel and Google Sheets.

How to calculate a weighted score

Use SUMPRODUCT to multiply scores by weights and sum the result to compute a weighted average or total score.

How to build a ranked leaderboard

Use RANK to assign positions and LARGE/INDEX/MATCH to sort a leaderboard by score in Excel or Google Sheets.

How to calculate a percentile rank

Use PERCENTRANK to find where a value falls within a dataset as a percentage in Excel or Google Sheets.

How to rank values within each group

Use COUNTIFS to build a within-group rank formula without needing helper columns in Excel or Google Sheets.

How to grade scores on a curve

Curve exam scores by adding a flat boost, scaling to a target average, or using a square-root curve in Excel and Google Sheets.

How to calculate a weighted GPA

Calculate a weighted GPA using SUMPRODUCT to multiply grade points by credit hours then divide by total credits.

How to find the second largest value

Use the LARGE function to retrieve the second (or Nth) largest value from a dataset in Excel and Google Sheets.

How to find the nth smallest value

Use the SMALL function to retrieve the 1st, 2nd, 3rd, or any nth smallest number from a range of values.

How to find the maximum with a condition

Use MAXIFS (Excel 2019+/Google Sheets) or an array formula to find the largest value that meets one or more criteria.

How to find the minimum ignoring zero

Find the smallest non-zero value in a range using MINIFS or a MIN(IF(…)) array formula to skip zeros.

How to calculate a z-score

Compute a z-score (standard score) using AVERAGE and STDEV functions to measure how many standard deviations a value is from the mean.

How to normalize values to a 0-1 range

Apply min-max normalization with a formula using MIN and MAX to scale any dataset to a 0–1 range.

How to average the last N values

Average only the most recent N entries in a column with AVERAGE, OFFSET and COUNTA — ideal for a trailing average.

How to calculate a weighted moving average

Calculate a weighted moving average in Excel or Google Sheets with SUMPRODUCT — apply custom weights to a rolling window of values without helper columns or VBA.

Sum, count & aggregation

How to sum a column

Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.

How to count unique values

Count how many distinct values a column has by combining COUNTA with UNIQUE. Works in Excel 365 and Google Sheets.

How to count cells with text

Count cells that contain any text using COUNTIF with a wildcard. Works the same in Excel and Google Sheets.

How to count non-blank cells

Count every filled cell — text, numbers, or formulas — with COUNTA. Works the same in Excel and Google Sheets.

How to sum values by month

Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.

How to count entries by month

Count rows that fall in a specific month using COUNTIFS with two date bounds. Works in Excel and Google Sheets.

How to count how many times a word appears in a cell

Count occurrences of a word in text by comparing its length before and after removing the word. Works in both apps.

How to count words in a cell

Count words by measuring spaces with LEN and SUBSTITUTE. One formula, identical in Excel and Google Sheets.

How to sum the top N values

Add up the largest few numbers in a range using LARGE. Here we total the top 3 — the same in both apps.

How to count cells with specific text

Count how many cells exactly match a word using COUNTIF — for example, how many orders are marked Paid. Same in both apps.

How to sum a row

Use SUM with a horizontal range to total values across an entire row or a specific span of columns.

How to sum by category

Use SUMIF to add up values that belong to a specific category, such as a product name or region.

How to sum values by week

Use SUMPRODUCT with WEEKNUM to total numeric values that fall within a specific week number.

How to sum values by year

Use SUMPRODUCT with YEAR to total values whose dates fall within a specific calendar year.

How to sum cells by color

Neither Excel nor Google Sheets has a built-in function to sum by fill color; use a helper column or a VBA/Apps Script macro instead.

How to sum with multiple criteria

Use SUMIFS to sum a range when multiple conditions across different columns must all be true simultaneously.

How to sum the bottom N values

Use SUMPRODUCT with SMALL to add up the N smallest values in a range without sorting your data.

How to sum every other row

Use SUMPRODUCT with MOD and ROW to add only the even-numbered or odd-numbered rows in a range.

How to sum every nth row

Use SUMPRODUCT with MOD and ROW to add values from every nth row in a dataset, such as every 3rd or 4th row.

How to sum visible cells only

Use SUBTOTAL with function code 9 to sum only the visible rows after applying a filter, ignoring hidden rows.

How to sum a range ignoring blanks

Use SUMIF with criteria "<>" to sum only non-blank cells, or simply use SUM since it already ignores blanks.

How to sum values between two dates

Use SUMIFS with two date criteria — greater than or equal to the start date and less than or equal to the end date.

How to sum only the positive numbers

Use SUMIF with criteria ">0" to add only the positive values in a range, skipping zeros and negatives.

How to sum only the negative numbers

Use SUMIF with criteria "<0" to add only the negative values in a range, ignoring zeros and positives.

How to create a running total

Use a mixed-reference SUM formula to create a running total column that grows with each row.

How to create a cumulative sum

Use SUM with a mixed reference ($B$2:B2) to build a cumulative total that grows row by row when copied down.

How to sum unique values

Use SUMPRODUCT with COUNTIF to sum only the first occurrence of each distinct value, effectively summing unique amounts.

How to sum a filtered range

Use SUBTOTAL(9,range) to sum only the visible rows in a filtered list; the result updates automatically as filters change.

How to count cells that contain numbers

Use COUNT to count all cells in a range that contain numeric values, automatically ignoring text and blanks.

How to count blank cells

Use COUNTBLANK to count empty cells in a range, or COUNTIF with "" as the criteria for more flexibility.

How to count duplicate values

Use COUNTIF to find how many times each value appears, then identify which values appear more than once.

How to count cells greater than a value

Use COUNTIF with a ">" comparison operator to count cells whose numeric value exceeds a specified threshold.

How to count cells less than a value

Use COUNTIF with a "<" comparison operator to count cells whose numeric value falls below a specified threshold.

How to count cells between two values

Use COUNTIFS to count cells that fall between two numeric bounds — works in both Excel and Google Sheets.

How to count cells by color

Learn how to count colored cells using a VBA helper function in Excel or Apps Script in Google Sheets — no built-in formula exists.

How to count how many times a value appears

Use COUNTIF to count how many times a specific value appears in a range — exact match or wildcard pattern.

How to count rows that meet two criteria

Use COUNTIFS to count rows where multiple conditions are all true simultaneously — like region AND product.

How to count characters in a cell

Use the LEN function to count the total number of characters in any cell, including spaces and punctuation.

How to count how many times a character appears in a cell

Combine LEN and SUBSTITUTE to count occurrences of a specific character or substring inside a cell.

How to count cells that contain a substring

Use COUNTIF with wildcard asterisks to count cells that contain a specific word or substring anywhere in the cell.

How to count cells that start with a letter

Use COUNTIF with a trailing wildcard (e.g. "A*") to count cells whose content starts with a specific letter or prefix.

How to count cells that end with a value

Use COUNTIF with a leading wildcard ("*value") to count cells whose content ends with a specific suffix.

How to count weekdays between two dates

Use NETWORKDAYS to count business days (Monday–Friday) between a start and end date, with optional holiday exclusions.

How to count weekend days between two dates

Subtract NETWORKDAYS from total days to get weekend-day count, or use SUMPRODUCT with WEEKDAY for precise control.

How to count distinct values

Use SUMPRODUCT with COUNTIF to count unique values in a range — no helper columns needed in either application.

How to count rows that match a condition

Use COUNTIF for a single condition or COUNTIFS for multiple conditions to count matching rows in any dataset.

How to count checked checkboxes

Count how many checkboxes are checked in a range using COUNTIF with TRUE in Google Sheets or form controls in Excel.

How to use SUMIF with a date range

Use SUMIF with a date comparison criterion to sum values only for dates on or after (or before) a specific date in your data range.

How to use SUMIFS between two dates

Use SUMIFS with two date criteria on the same date column to sum values that fall within a specific start and end date range.

How to use SUMIF with an OR condition

Add multiple SUMIF calls together or use SUMPRODUCT with an OR array to sum values matching any one of several criteria.

How to sum values by day of the week

Use SUMPRODUCT with WEEKDAY to conditionally sum values based on which day of the week the corresponding date falls on.

How to sum values by quarter

Sum sales or any numeric data by quarter using SUMPRODUCT and MONTH, or SUMIF with a helper column.

How to count cells not equal to a value

Use COUNTIF with the <> operator to count all cells that do not match a specific value.

How to use COUNTIF with a wildcard

Count cells that contain, start with, or end with specific text using COUNTIF with * and ? wildcard characters.

How to use COUNTIFS with OR logic

COUNTIFS applies AND logic by default; use summed COUNTIFs or SUMPRODUCT to achieve OR logic across multiple criteria.

How to sum cells whose label contains a word

Use SUMIF with wildcard criteria to sum numeric values in rows where a text column contains a specific word or substring.

How to count rows where any column has a value

Count rows that have at least one non-blank cell using SUMPRODUCT and COUNTA across multiple columns per row.

Excel ≠ Sheets

How to sum the same cell across multiple sheets

Total the same cell from several sheets at once using a 3D reference in Excel, or a stacked range in Google Sheets.

Excel ≠ Sheets

How to sum the last N rows

Add up only the last N values in a column using OFFSET and COUNTA, so the total follows your data as new rows are added.

How to sum a column found by its header

Total whichever column carries a given header using MATCH to locate it and OFFSET (or INDEX) to sum it — no manual column letter needed.