The spreadsheet field guide
A useful answer, one task away.
Search 552 step-by-step guides by task or function. Find the formula, understand the steps, and adapt it to your sheet.
552 of 552 guides · Excel & Google Sheets
Sum, count & aggregation 62
How to calculate a 30-day rolling sum
Sum the 30 calendar days ending on a chosen date with SUMIFS, including timestamps on the final day and excluding future rows.
Worked sample includedHow 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 sum by category
Total values per category with SUMIF, build a full summary table with UNIQUE + SUMIF, handle multiple criteria with SUMIFS, and match several categories at once.
Worked sample includedHow to count words in a cell
Count words by measuring spaces with LEN and SUBSTITUTE. One formula, identical in Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to sum a row
Use SUM with a horizontal range to total values across an entire row or a specific span of columns.
Worked sample includedHow 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 native SUMBYCOLOR function. Three reliable approaches: helper-column label (works everywhere), Excel VBA macro reading Interior.Color, or Google Apps Script using getBackground(). Pick the helper column unless you can't add one.
Worked sample includedHow to sum with multiple criteria
Use SUMIFS to sum a range when multiple conditions across different columns must all be true simultaneously.
Worked sample includedHow to sum the bottom N values
Use SUMPRODUCT with SMALL to add up the N smallest values in a range without sorting your data.
Worked sample includedHow 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.
Worked sample includedHow to sum a range ignoring blanks
Use =SUMIF(criteria_range, "<>", sum_range) to sum only rows where the criteria column is non-blank. Plain SUM already skips blanks in its own range — SUMIF gates by a DIFFERENT column. Covers the empty-string formula gotcha, multi-condition SUMIFS, and the inverse (sum only blanks).
Worked sample includedHow 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.
Worked sample includedHow to sum only the positive numbers
Use SUMIF with criteria ">0" to add only the positive values in a range, skipping zeros and negatives.
Worked sample includedHow to sum only the negative numbers
Use SUMIF with criteria "<0" to add only the negative values in a range, ignoring zeros and positives.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to count cells that contain numbers
Use COUNT to count all cells in a range that contain numeric values, automatically ignoring text and blanks.
Worked sample includedHow to count blank cells
Use COUNTBLANK to count empty cells in a range, or COUNTIF with "" as the criteria for more flexibility.
Worked sample includedHow to count duplicate values
Use COUNTIF to find how many times each value appears, then identify which values appear more than once.
Worked sample includedHow to count cells greater than a value
Use COUNTIF with a ">" comparison operator to count cells whose numeric value exceeds a specified threshold.
Worked sample includedHow to count cells less than a value
Use COUNTIF with a "<" comparison operator to count cells whose numeric value falls below a specified threshold.
Worked sample includedHow to count cells between two values
Use COUNTIFS to count cells that fall between two numeric bounds — works in both Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to count rows that meet two criteria
Use COUNTIFS to count rows where multiple conditions are all true simultaneously — like region AND product.
Worked sample includedHow to count characters in a cell
Use =LEN(A2) to count total characters in a cell, including spaces and punctuation. Covers counting without spaces, counting a specific character, range totals, and words-vs-characters.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to use SUMIF with an OR condition
Use =SUMIF($A$2:$A$5,"Apple",$B$2:$B$5)+SUMIF($A$2:$A$5,"Banana",$B$2:$B$5) to sum values matching any of multiple criteria (also called multi-criteria OR sum or conditional OR sum). For 3+ criteria, switch to SUMPRODUCT for a cleaner formula. Works in Excel and Google Sheets.
Worked sample includedHow to sum values by day of the week
Use =SUMPRODUCT(($B$2:$B$5)*(WEEKDAY($A$2:$A$5,2)=1)) to sum values where the date in column A falls on a specific weekday (also called day-of-week totals or weekday aggregation). Works in Excel 2007+ and Google Sheets without array entry.
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.
Worked sample includedHow to use COUNTIF with a wildcard
Count cells that contain, start with, or end with specific text using COUNTIF with * and ? wildcard characters.
Worked sample includedHow to use COUNTIFS with OR logic
COUNTIFS applies AND logic by default; use summed COUNTIFs or SUMPRODUCT to achieve OR logic across multiple criteria.
Worked sample includedHow 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.
Worked sample includedHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
How to count a specific character in a cell
Learn how to count occurrences of a specific character within a cell using formulas in both Excel and Google Sheets.
Worked sample includedHow to count unique values matching criteria
Learn how to count unique values based on specific criteria using formulas in both Excel and Google Sheets.
Separate Excel & Sheets formulas
Lookups 32
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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to look up the most recent date for an item
Use =MAXIFS($B$2:$B$5,$A$2:$A$5,"Apple") to return the latest date for a specific item (also called the most recent transaction date or last activity date). MAXIFS works in Excel 2019+ and Google Sheets; older Excel needs an array MAX/IF.
Worked sample includedHow 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.
Separate Excel & Sheets formulasHow 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.
Worked sample includedHow to return multiple columns with XLOOKUP
Learn how to use XLOOKUP to fetch data from multiple columns efficiently in both Excel and Google Sheets.
How to use XLOOKUP with wildcards
Learn how to use XLOOKUP with wildcards to perform flexible lookups in Excel and Google Sheets.
Dates & time 46
How to calculate age from a date of birth
Calculate completed years at an explicit as-of date with DATEDIF and numeric DATE inputs, avoiding ambiguous date text and changing examples.
Worked sample includedHow to find the first day of a month
Construct the first day of a month from numeric year and month inputs, with a reproducible worked example.
Worked sample includedHow to find the last day of a month
Use EOMONTH to find a month end, including February in a leap year, and keep date display separate from date arithmetic.
Worked sample includedHow 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 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 days to a date with =A2+N for calendar days, =A2+(N*7) for weeks, or =WORKDAY(A2,N) for business days only. Covers subtracting, holidays, and the most common formatting pitfall.
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(A2,"dddd") for the full weekday name (Monday) or =WEEKDAY(A2,2) for a number (Mon=1, Sun=7). Covers short names, weekend detection, locale, and "is this a Friday" checks.
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 convert a date to text
Use =TEXT(A2, "YYYY-MM-DD") to convert a date to a formatted text string. Covers ISO/US/UK/long format codes, concatenation patterns, locale gotchas, and the difference between TEXT (formats display) and DATEVALUE (the reverse direction).
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.
Worked sample includedHow 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.
Worked sample includedHow 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
Use =DATEDIF(start_date, end_date, "Y") for completed years of service, or combine with "YM" for years + months display. Handles current and former employees, decimal years via YEARFRAC, and the common DATEDIF edge case where the start date is later than the end date.
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
How to calculate business days between two dates
Learn how to calculate the number of business days between two dates using Excel's NETWORKDAYS function and Google Sheets' equivalent.
Worked sample included
Data cleaning & shaping 26
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to combine multiple columns into one
Merge values from several columns into one cell using the ampersand operator or CONCAT/TEXTJOIN for delimited output.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedHow to number rows automatically
Add sequential row numbers automatically using ROW() so numbers update if rows are inserted or deleted.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow to generate random numbers
Generate random integers or decimals using RAND and RANDBETWEEN — both recalculate on every sheet change unless pasted as values.
Worked sample includedHow 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.
Worked sample includedHow 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.
Separate Excel & Sheets formulasHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow to auto-number rows that have data
Assign sequential numbers only to rows containing data using IF with COUNTA, skipping blank rows automatically.
Worked sample includedHow 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.
Worked sample includedHow 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.
Finance & SaaS metrics 61
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
Worked sample includedHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
Worked sample includedHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
Worked sample includedHow to calculate days sales outstanding (DSO)
Calculate DSO from average accounts receivable, net credit sales and days in the same period, with an explicit zero-sales result.
Worked sample includedHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate simple interest
Multiply principal, rate, and time to compute simple interest using a straightforward formula in Excel or Sheets.
Worked sample includedHow to calculate a loan payment
Use the PMT function to calculate periodic loan payments given a principal, interest rate, and number of periods.
Worked sample includedHow to calculate a monthly loan payment
Use =PMT(B2/12, C2*12, -A2) to get the exact monthly payment for a loan. Covers mortgages, auto loans, balloon payments, interest-vs-principal split, and the rate-period pitfall that trips most people up.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate markup
Divide the difference between selling price and cost by cost to calculate markup percentage in Excel or Google Sheets.
Worked sample includedHow to calculate profit margin
Divide net profit by revenue to calculate gross or net profit margin percentage in Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate a discount
Multiply the original price by the discount rate to find the discount amount, then subtract to get the discounted price.
Worked sample includedHow to calculate commission
Multiply sales revenue by the commission rate to compute the commission earned in Excel and Google Sheets.
Worked sample includedHow to calculate ROI
Calculate Return on Investment by dividing net gain by the initial cost, expressed as a percentage in Excel or Sheets.
Worked sample includedHow 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.
Worked sample includedHow to convert currency
Multiply an amount by an exchange rate to convert between currencies; use a rate table and VLOOKUP for multi-currency conversions.
Worked sample includedHow 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.
Worked sample includedHow to calculate depreciation
Use the SLN function for straight-line depreciation or DB for declining balance; both work in Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate progressive income tax
Calculate income tax across multiple brackets in one formula using nested IF or a SUMPRODUCT lookup against a bracket table. Same logic works for any country — swap the rate/threshold numbers.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate customer lifetime value
Calculate customer lifetime value (CLV) using average purchase value, purchase frequency, and customer lifespan in Excel and Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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%.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
How to build a loan amortization schedule
Learn how to create a detailed loan amortization schedule using Excel or Google Sheets with step-by-step instructions and formulas.
Worked sample included
Comparisons (X vs Y) 36
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.
Worked sample includedCOUNTIF 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.
Worked sample includedINDEX 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.
Worked sample includedIF 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.
Worked sample includedSeparate Excel & Sheets formulasCONCAT vs CONCATENATE vs TEXTJOIN in Excel and Sheets
Use TEXTJOIN when you need a delimiter or skip blanks. Use CONCAT (or the & operator) for simple joins. CONCATENATE still works but is legacy — avoid in new sheets.
Worked sample includedSeparate Excel & Sheets formulasFIND 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.
Worked sample includedROUND 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.
Worked sample includedFILTER 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.
Worked sample includedSeparate Excel & Sheets formulasUNIQUE 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.
Worked sample includedSeparate Excel & Sheets formulasIFERROR 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.
Worked sample includedTEXT 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.
Worked sample includedSUBSTITUTE 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.
Worked sample includedSUM 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: which to use, and why INDEX is almost always right
Use INDEX by default — it's non-volatile, faster, and returns the same value. Reserve OFFSET for the one case where you genuinely need a resizable block (its height and width arguments).
Worked sample includedCOUNTA 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.
Worked sample includedMAX 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.
Worked sample includedMIN 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.
Worked sample includedSUMPRODUCT 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.
Worked sample includedTEXTJOIN 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.
Separate Excel & Sheets formulasDATEDIF 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.
Worked sample includedMOD 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.
Worked sample includedCHOOSE 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.
Worked sample includedNETWORKDAYS 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.
Separate Excel & Sheets formulasAVERAGEIF 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.
Worked sample includedIFERROR 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.
Worked sample includedSORT 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.
Worked sample includedHow to calculate actual vs target percentage
Learn how to easily calculate the percentage difference between actual and target values using Excel or Google Sheets.
Worked sample includedHow to calculate markup vs margin
Learn how to calculate markup and margin in Excel and Google Sheets using simple formulas. Understand the difference between markup and margin and apply these calculations to your financial data.
Worked sample included
Modern Excel (LET, LAMBDA) 9
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) 10
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Separate Excel & Sheets formulasHow 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.
Worked sample includedHow 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 16
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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulas
Inventory & operations 6
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample included
Unit conversions 8
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample included
Conditional formatting 21
How to highlight the lowest value
Highlight the smallest number in a range with Conditional Formatting and a MIN-based custom formula in Excel and Google Sheets — including how to ignore blanks and highlight the lowest N.
How to highlight cells greater than a value
Highlight cells above a threshold with Conditional Formatting in Excel and Google Sheets — built-in greater-than rules, custom formulas, whole-row highlighting, and dynamic thresholds.
Worked sample includedHow 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.
Worked sample includedHow to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
Worked sample includedHow 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.
Worked sample includedHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
Worked sample includedHow 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.
Worked sample includedHow to shade every other row
Use conditional formatting with MOD and ROW to automatically apply alternating row colors for a banded, readable table layout.
Worked sample includedHow to highlight weekend dates
Use conditional formatting with =WEEKDAY(A2,2)>5 to automatically highlight Saturday and Sunday dates in any calendar or date column (also called weekend highlighting or Sat/Sun shading). Works in Excel 2010+ and Google Sheets.
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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to highlight duplicates in two columns
Learn how to easily highlight duplicate entries across two columns in both Excel and Google Sheets using conditional formatting.
Worked sample includedHow to highlight cells greater than average
Learn how to use conditional formatting to highlight cells with values greater than the average in both Excel and Google Sheets.
Worked sample includedHow to highlight cells less than a value
Learn how to use conditional formatting to highlight cells with values less than a specified number in both Excel and Google Sheets.
Worked sample includedHow to highlight upcoming due dates
Learn how to automatically highlight upcoming due dates using conditional formatting in both Excel and Google Sheets.
Worked sample includedHow to highlight a row if a cell equals a value
Learn how to use conditional formatting to highlight an entire row based on a specific cell value in both Excel and Google Sheets.
Worked sample includedHow to highlight a row based on a drop-down value
Learn how to automatically highlight a row in Excel and Google Sheets when a specific value is selected from a drop-down list.
Worked sample includedHow to highlight the minimum value in a column
Learn how to easily highlight the minimum value in a column using conditional formatting in both Excel and Google Sheets.
Worked sample includedHow to highlight the maximum value in a column
Learn how to easily highlight the maximum value in a column using conditional formatting in both Excel and Google Sheets.
Worked sample included
Text manipulation 66
How to get text after a character or delimiter
Extract everything after the first delimiter with MID and FIND, including multi-character delimiters and explicit missing-delimiter handling.
Worked sample includedHow 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 remove everything after a character
Keep only the text before a delimiter: TEXTBEFORE in Excel 365, LEFT + FIND everywhere, SPLIT in Google Sheets — plus last-occurrence handling and a no-formula Find & Replace trick.
Worked sample includedHow 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.
Worked sample includedHow to check if a cell contains specific text
Use =ISNUMBER(SEARCH("text", A2)) to test whether a cell contains a substring. Returns TRUE or FALSE. Covers case-insensitive, case-sensitive, multi-criteria, and "if-contains-then" variants.
Worked sample includedHow 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.
Worked sample includedHow to split first and last name
Extract first and last names from a single cell using LEFT, MID, FIND, and LEN formulas.
Worked sample includedHow to extract the last name
Pull the last name from a "First Last" or "Last, First" formatted cell using MID, FIND, and LEN.
Worked sample includedHow to extract numbers from a text string
Pull numeric digits out of mixed text strings using array formulas, VALUE, and MID combinations.
Separate Excel & Sheets formulasHow to extract text from a string
Use LEFT, RIGHT, MID, and FIND to pull specific substrings from any position within a cell.
Worked sample includedHow to remove the first character from a cell
Drop the first character of a string with RIGHT and LEN — a simple two-function formula.
Worked sample includedHow 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.
Worked sample includedHow to remove non-numeric characters
Strip letters and symbols from a cell to leave only digits using array formulas or REGEXREPLACE.
Separate Excel & Sheets formulasHow to remove line breaks from a cell
Replace CHAR(10) line breaks in a cell with a space or nothing using SUBSTITUTE or CLEAN.
Worked sample includedHow 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.
Worked sample includedHow to convert text to uppercase
Convert any text string to ALL CAPS instantly using the UPPER function in Excel and Google Sheets.
Worked sample includedHow to convert text to lowercase
Convert any text string to all-lowercase using the LOWER function in Excel and Google Sheets.
Worked sample includedHow to convert text to proper case
Title-case a text string so each word starts with a capital using the PROPER function.
Worked sample includedHow to reverse a text string
Reverse the order of characters in a string using a TEXTJOIN array formula or MID with SEQUENCE.
Separate Excel & Sheets formulasHow 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.
Worked sample includedHow to replace multiple values at once
Chain SUBSTITUTE calls or use REDUCE/LAMBDA to replace many different strings in one formula.
Worked sample includedHow to combine text from two cells
Join two cell values into one using the & operator or CONCAT/CONCATENATE functions.
Worked sample includedHow 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.
Worked sample includedHow to get the text between two characters
Extract a substring between two delimiter characters using MID and FIND, or REGEXEXTRACT in Sheets.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedHow to check if text ends with a value
Test whether a cell ends with a specific suffix using RIGHT or a COUNTIF wildcard pattern.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to swap first and last name
Rearrange 'First Last' to 'Last, First' using FIND, LEFT, MID, and LEN to split on the space.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to extract the username from an email
Get the username (part before @) from an email address using LEFT and FIND.
Worked sample includedHow 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.
Worked sample includedHow to repeat a character N times
Use the REPT function to repeat any character or string a specified number of times in a cell.
Worked sample includedHow to create a URL slug from text
Convert a title into a lowercase, hyphenated URL slug using LOWER and SUBSTITUTE to replace spaces.
Worked sample includedHow 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.
Worked sample includedHow 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 &.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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 #.
Worked sample includedSeparate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow 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.
Separate Excel & Sheets formulasHow to extract text before a specific character
Learn how to use LEFT and FIND functions to extract text before a specific character in both Excel and Google Sheets.
Worked sample includedHow to extract text between two characters
Learn how to use formulas to extract text located between two specific characters in Excel and Google Sheets.
Worked sample includedHow to extract the domain name from a URL
Learn how to use formulas to extract domain names from URLs in both Excel and Google Sheets, ensuring accurate data manipulation.
Worked sample includedSeparate Excel & Sheets formulasHow to extract the first word from a cell
Learn how to use formulas to extract the first word from a text string in both Excel and Google Sheets.
Worked sample includedHow to extract the last word from a cell
Learn how to use formulas to extract the last word from a text string in both Excel and Google Sheets.
Worked sample includedHow to add leading zeros to a number
Learn how to format numbers with leading zeros in Excel and Google Sheets using TEXT function for consistent display.
Worked sample includedHow to extract the middle name from a full name
Learn how to use Excel and Google Sheets formulas to accurately extract the middle name from a full name, handling various naming conventions.
Worked sample includedHow to extract an email address from text
Learn how to use formulas to extract email addresses from text in both Excel and Google Sheets.
Worked sample includedSeparate Excel & Sheets formulas
Logic & conditional 16
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 return a value if a cell contains text
Check if a cell contains specific text with IF + ISNUMBER + SEARCH and return any value you want — plus case-sensitive checks, multiple keywords, and wildcard alternatives.
Worked sample includedHow to return blank instead of zero
Stop formulas showing 0: wrap them in IF to return an empty string, suppress zeros with a custom number format, or hide them sheet-wide — plus the VLOOKUP-returns-0 fix.
Worked sample includedHow 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.
Worked sample includedHow to write a nested IF formula
Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedSeparate Excel & Sheets formulasHow to categorize values into buckets
Group numeric values into named categories (buckets) using nested IF or a lookup table for clean, readable segmentation.
Worked sample includedHow 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.
Worked sample includedHow 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.
Separate Excel & Sheets formulasHow 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.
Percentages & business math 17
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.
Worked sample includedHow 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.
Worked sample includedHow to calculate attendance percentage
Divide attended sessions by total sessions and multiply by 100 to get attendance percentage in Excel or Google Sheets.
Worked sample includedHow 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.
Worked sample includedHow to calculate a percentage of a percentage
Multiply two percentages together correctly in Excel and Google Sheets to find a percentage of a percentage.
Worked sample includedHow 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.
Worked sample includedHow to calculate the percentage difference between two numbers
Calculate the relative percentage difference between two values using (new-old)/old, expressed as a percentage.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate percentage complete
Divide completed units by total units to track progress as a percentage, useful for project management and task tracking.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample included
Average & statistics 29
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 average ignoring zeros
Use AVERAGEIF with a "<>0" criterion to calculate an average that excludes zero values from the calculation.
Worked sample includedHow to average ignoring blank cells
AVERAGE naturally ignores blank cells; use AVERAGEIF with "<>" to also ignore text placeholders like dashes.
Worked sample includedHow to average with a condition
Use AVERAGEIF to compute the mean of values in one column where a corresponding column meets a specified criterion.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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 variance
Use VAR.S for sample variance or VAR.P for population variance to quantify data dispersion in Excel or Google Sheets.
Worked sample includedSeparate Excel & Sheets formulasHow to calculate a percentile
Use PERCENTILE.INC or PERCENTILE.EXC to find the value at a given percentile rank within a dataset.
Separate Excel & Sheets formulasHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow to calculate a weighted GPA
Calculate a weighted GPA using SUMPRODUCT to multiply grade points by credit hours then divide by total credits.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample includedHow 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.
Worked sample included
Other & practical 91
How to find the next Monday after a date
Find the Monday strictly after an input date using WEEKDAY, with predictable behavior when the input is already a Monday.
Worked sample includedHow to find the last day of a calendar quarter
Calculate a calendar quarter end from the month number, including the December-to-January boundary.
Worked sample includedHow to use FILTER with multiple conditions
Return campaign names that meet both a region and amount condition, with the correct Excel and Google Sheets FILTER argument conventions.
Worked sample includedSeparate Excel & Sheets formulasHow to filter rows by an inclusive date range
Return names from rows dated within a reporting interval, including timestamps on the final date without including the following day.
Worked sample includedSeparate Excel & Sheets formulasHow to combine two columns
Merge two columns — like first and last name — into one with & or TEXTJOIN. Works the same in Excel and Google Sheets.
How to split text into columns
Split a full name or any delimited text into separate columns. Google Sheets uses SPLIT; Excel uses TEXTSPLIT — here is each.
Separate Excel & Sheets formulasHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How to calculate an average
Find the mean of a range with AVERAGE, or average only matching rows with AVERAGEIF. Same in Excel and Google Sheets.
How to write an IF-THEN formula
Return one value when a condition is true and another when it is false using IF. Works the same in Excel and Google Sheets.
How to subtract
Subtract one cell from another with the minus operator, or subtract a total with SUM. Same in Excel and Google Sheets.
How to multiply two columns
Multiply two columns row by row with *, then total the products with SUMPRODUCT. Works in Excel and Google Sheets.
How to get the day of the week from a date
Turn a date into a weekday name with TEXT, or a weekday number with WEEKDAY. Works the same in Excel and Google Sheets.
How to round to the nearest 5, 10, or 100
Round a number to the nearest multiple with MROUND, or force up/down with CEILING and FLOOR. Works in both apps.
How to generate a unique ID
Create unique IDs in Excel and Google Sheets: sequential codes with a prefix, composite keys from existing columns, or random IDs — plus how to guarantee no duplicates.
How to calculate BMI
Calculate Body Mass Index in Excel and Google Sheets: metric and imperial formulas, plus how to label each result Underweight, Normal, Overweight or Obese automatically.
How to calculate a GPA
Calculate a simple unweighted GPA from letter or numeric grades using AVERAGE or SUMPRODUCT in Excel and Google Sheets.
Worked sample includedHow to convert a column number to a letter
Convert a numeric column index (like 1, 2, 26) to its corresponding letter (A, B, Z) using a SUBSTITUTE and ADDRESS formula.
Worked sample includedHow to convert a column letter to a number
Convert a column letter like A, B, or AA to its numeric index using COLUMN and INDIRECT in Excel and Google Sheets.
How to make an in-cell bar chart with REPT
Create simple visual bar charts inside cells using the REPT function to repeat a character proportional to a value.
Worked sample includedHow to pick a random name from a list
Randomly select one name from a list using INDEX and RANDBETWEEN in Excel and Google Sheets.
Worked sample includedHow to take a random sample from a list
Extract a random sample of N items from a list without repeats using RAND, RANK, and INDEX in Excel and Google Sheets.
Worked sample includedHow to convert a number to words
Spell out a number as words in Excel using a custom VBA function or SpellNumber, and in Google Sheets with Apps Script.
Separate Excel & Sheets formulasHow to check if a value exists in a list
Check whether a value is present in a list using COUNTIF or MATCH wrapped in an ISNUMBER in Excel and Google Sheets.
Worked sample includedHow to return multiple matches in one cell
Concatenate all matching values into a single cell using TEXTJOIN with IF in Excel, or ARRAYFORMULA JOIN in Google Sheets.
Separate Excel & Sheets formulasHow to create a sparkline
Learn how to add a sparkline to your Excel or Google Sheets worksheet to visualize data trends quickly and effectively.
How to build a KPI dashboard formula
Learn how to create a dynamic KPI dashboard using formulas in Excel and Google Sheets, ensuring accurate and up-to-date performance metrics.
Worked sample includedHow to calculate variance between two numbers
Learn how to calculate the variance between two numbers using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to calculate quarter-over-quarter growth
Learn how to calculate QoQ growth using simple formulas in Excel and Google Sheets with step-by-step instructions.
Worked sample includedSeparate Excel & Sheets formulasHow to calculate month-over-month change
Learn how to calculate month-over-month changes using simple formulas in Excel and Google Sheets with step-by-step instructions.
Worked sample includedHow to calculate a 7-day rolling average
Learn how to compute a 7-day rolling average using Excel and Google Sheets formulas with step-by-step instructions and examples.
Worked sample includedHow to calculate cohort retention
Learn how to calculate cohort retention using Excel and Google Sheets with step-by-step instructions and formulas.
Worked sample includedHow to calculate NPV (Net Present Value)
Learn how to calculate Net Present Value using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to calculate IRR (Internal Rate of Return)
Learn how to use the IRR function in both Excel and Google Sheets to calculate the internal rate of return for a series of cash flows.
How to calculate profit margin percentage
Learn how to calculate the profit margin percentage using simple formulas in both Excel and Google Sheets with step-by-step instructions.
Worked sample includedSeparate Excel & Sheets formulasHow to calculate customer lifetime value (LTV)
Learn how to calculate Customer Lifetime Value (CLV) using Excel and Google Sheets with step-by-step instructions and formulas.
Worked sample includedHow to calculate conversion rate
Learn how to calculate conversion rates using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to calculate CAGR (compound annual growth rate)
Learn how to calculate the Compound Annual Growth Rate (CAGR) using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to create a dependent drop-down list
Learn how to set up dependent drop-down lists in both Excel and Google Sheets for dynamic data selection.
How to use a formula in data validation
Learn how to apply a custom formula in data validation rules to ensure data integrity in Excel and Google Sheets.
Worked sample includedHow to restrict cell input to numbers only
Learn how to ensure that only numeric values can be entered into specific cells in Excel and Google Sheets, enhancing data integrity.
Worked sample includedHow to restrict cell input to a date range
Learn how to ensure that users can only enter dates within a specific range in both Excel and Google Sheets.
Worked sample includedHow to prevent duplicate entries in a column
Learn how to use formulas to prevent duplicate entries in a column, ensuring data integrity in both Excel and Google Sheets.
Worked sample includedHow to validate email address format
Learn how to use formulas to validate email addresses in Excel and Google Sheets, ensuring they follow the standard format.
Separate Excel & Sheets formulasHow to apply a color scale by value
Learn how to visually represent data values using color scales in both Excel and Google Sheets for better data analysis.
Worked sample includedHow to add data bars with conditional formatting
Learn how to visually represent data with data bars using conditional formatting in both Excel and Google Sheets.
Worked sample includedHow to add icon sets with conditional formatting
Learn how to visually represent data trends using icon sets in Excel and Google Sheets through conditional formatting.
Worked sample includedHow to split a full name into first and last name
Learn how to easily split a full name into separate first and last names using Excel and Google Sheets functions.
Worked sample includedHow to split a cell by a delimiter
Learn how to split text in a cell using a delimiter in both Excel and Google Sheets with step-by-step instructions and examples.
Separate Excel & Sheets formulasHow to combine first and last name into one cell
Learn how to concatenate first and last names into a single cell using Excel and Google Sheets formulas.
Worked sample includedHow to concatenate text with a line break
Learn how to combine text with a line break in both Excel and Google Sheets using CHAR(10) or CHAR(13)&CHAR(10). Perfect for creating multi-line text in cells.
Worked sample includedHow to reverse text in a cell
Learn how to reverse the text in a cell using Excel and Google Sheets with simple formulas.
Separate Excel & Sheets formulasHow to convert text to proper case (title case)
Learn how to easily convert text to proper case in both Excel and Google Sheets using built-in functions.
Worked sample includedHow to remove special characters from text
Learn how to clean up text by removing unwanted special characters using Excel and Google Sheets formulas.
Worked sample includedSeparate Excel & Sheets formulasHow to calculate hours between two times
Learn how to easily calculate the number of hours between two times using simple formulas in both Excel and Google Sheets.
Worked sample includedHow to find the first day of the quarter
Learn how to calculate the first day of any given date's quarter using Excel and Google Sheets formulas.
Worked sample includedHow to count days until a due date
Learn how to calculate the number of days remaining until a specific due date using simple formulas in both Excel and Google Sheets.
Worked sample includedHow to calculate minutes between two timestamps
Learn how to easily calculate the difference in minutes between two timestamps using simple formulas in both Excel and Google Sheets.
Worked sample includedHow to get the calendar quarter from a date
Learn how to extract the calendar quarter from a given date using simple formulas in both Excel and Google Sheets.
How to get the ISO week number from a date
Learn how to extract the ISO week number from a given date using Excel and Google Sheets formulas.
Worked sample includedHow to calculate the fiscal year from a date
Learn how to determine the fiscal year from a given date using Excel and Google Sheets formulas. Perfect for financial reporting.
Worked sample includedHow to filter to unique values only
Learn how to extract unique values from a list in both Excel and Google Sheets using simple formulas.
How to sort a range by multiple columns
Learn how to sort data by multiple criteria in both Excel and Google Sheets using built-in functions and features.
How to sort by a custom list order
Learn how to sort data based on a custom list order in both Excel and Google Sheets, ensuring your data aligns with specific criteria.
How to rank values with ties broken
Learn how to rank values while breaking ties using RANK.EQ and RANK.AVG functions in Excel and Google Sheets.
How to rank by multiple criteria
Learn how to rank data based on multiple criteria using RANK.EQ with an array formula in Excel and ARRAYFORMULA in Google Sheets.
Separate Excel & Sheets formulasHow to find the most common value (mode) in a column
Learn how to identify the most frequently occurring value in a dataset using Excel's MODE.SNGL or Google Sheets' MODE function.
How to find the second largest value
Learn how to use the LARGE function to find the second largest value in a dataset in both Excel and Google Sheets.
Worked sample includedHow to find the second smallest value
Learn how to use the SMALL function to find the second smallest value in a dataset in both Excel and Google Sheets.
Worked sample includedHow to unpivot data from wide to long format
Learn how to transform wide-format data into long-format using Excel's Power Query and Google Sheets' UNPIVOT feature.
Separate Excel & Sheets formulasHow to transpose rows to columns with a formula
Learn how to easily convert rows into columns using a simple formula in both Excel and Google Sheets.
Worked sample includedHow to split one column into multiple columns
Learn how to efficiently split a single column of data into multiple columns using Excel and Google Sheets functions.
How to maintain a running balance column
Learn how to create a running balance column in Excel and Google Sheets using simple formulas. Ideal for tracking financial transactions over time.
Worked sample includedHow to calculate stock on hand
Learn how to calculate stock on hand using simple formulas in Excel and Google Sheets with this step-by-step guide.
Worked sample includedHow to calculate the reorder point
Learn how to calculate the reorder point using Excel or Google Sheets with simple formulas.
Worked sample includedHow to calculate safety stock
Learn how to calculate safety stock using Excel and Google Sheets formulas with this step-by-step guide.
Worked sample includedHow to calculate inventory turnover
Learn how to calculate inventory turnover using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to calculate return on investment (ROI)
Learn how to calculate ROI using Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to calculate the payback period
Learn how to calculate the payback period using Excel and Google Sheets with step-by-step instructions and examples.
Separate Excel & Sheets formulasHow to calculate employee attendance percentage
Learn how to calculate employee attendance percentages using Excel and Google Sheets with step-by-step instructions and formulas.
Worked sample includedHow to calculate utilization rate
Learn how to calculate the utilization rate using simple formulas in Excel and Google Sheets with step-by-step instructions.
Worked sample includedHow to format a number as a percentage
Learn how to easily convert decimal numbers into percentages in both Excel and Google Sheets with step-by-step instructions.
Worked sample includedHow to format a number with thousands separator
Learn how to easily add thousands separators to numbers in Excel and Google Sheets for better readability.
Worked sample includedHow to format negative numbers in red
Learn how to automatically format negative numbers in red using conditional formatting in both Excel and Google Sheets.
Worked sample includedHow to format a number as currency
Learn how to easily convert numbers into currency format in both Excel and Google Sheets with step-by-step instructions and examples.
Worked sample includedHow to build an in-cell bar chart with REPT
Learn how to create simple in-cell bar charts using the REPT function in both Excel and Google Sheets for quick visual data representation.
Worked sample includedHow to build a progress bar in a cell
Learn how to create a visual progress bar directly within a cell using Excel and Google Sheets formulas.
Worked sample includedHow to show rating stars in a cell
Learn how to visually represent ratings using star symbols directly within Excel and Google Sheets cells.
Worked sample includedHow to flatten a multi-column range into one column
Learn how to transform a multi-column dataset into a single column using Excel and Google Sheets formulas.
Separate Excel & Sheets formulasHow to display a number in millions or thousands
Learn how to format numbers to display in millions or thousands in both Excel and Google Sheets with simple formulas.
Worked sample includedHow to use custom number format codes
Learn how to apply custom number formats in Excel and Google Sheets to display numbers in specific ways, enhancing readability and presentation.
Worked sample includedHow to convert military time to 12-hour format
Learn how to easily convert military time to standard 12-hour format using Excel and Google Sheets with simple formulas.
Worked sample included