Excel how-to guides — 50 step-by-step tutorials.
Each guide covers one specific spreadsheet task: the right formula, step-by-step instructions, a worked example, and the most common mistakes to avoid. Works in both Excel and Google Sheets.
Date & Time
Calculate durations, working days, deadlines, and date differences with precision.
EDATE · beginner
How to edate add months
Return a date that is a specified number of months before or after a start date, landing on the same day of the month.
WORKDAY · intermediate
How to workday due date
Return a date that is a set number of working days from a start date, automatically skipping Saturdays and Sundays.
DATEDIF · intermediate
How to datedif age years
Calculate the number of complete years between a birth date and today — or between any two dates.
TODAY · beginner
How to today days remaining
Calculate how many calendar days remain between today and a future deadline date — updates automatically every day.
MONTH · beginner
How to month year extract
Pull the month number or year from a date cell to use in grouping, labelling, or pivot-style summaries.
EOMONTH · beginner
How to eomonth last day
Return the last calendar day of the month that is N months from a given start date — handles variable month lengths automatically.
NETWORKDAYS · beginner
How to networkdays project timeline
Count the number of working days between two project dates while excluding weekends automatically.
TEXT · beginner
How to weekday name
Display the day of the week as a name (Monday, Tuesday…) from any date cell using the TEXT function.
Math & Statistics
Add, count, average, and summarize spreadsheet data with criteria-aware formulas.
AVERAGEIFS · intermediate
How to averageifs conditional average
Calculate the average of values that meet two or more criteria — the multi-condition version of AVERAGEIF.
COUNTIFS · intermediate
How to countifs active customers
Count customers only when more than one condition is true, such as active status and target segment.
MAXIFS · intermediate
How to maxifs conditional max
Return the largest value in a range that meets one or more conditions — the criteria-aware version of MAX.
LARGE · beginner
How to large rank top n
Return the Nth largest value from a range — get the top score, second-highest sale, or any specific rank position without sorting the data.
STDEV · intermediate
How to stdev standard deviation
Calculate the standard deviation of a sample dataset — a measure of how far individual values typically deviate from the average.
SUMIFS · intermediate
How to sumifs monthly revenue
Add revenue only when both the category and the month boundaries match your criteria.
Financial
Model loan payments, net present value, internal rate of return, and depreciation.
PMT · intermediate
How to pmt loan payment
Calculate the fixed periodic payment required to pay off a loan at a constant interest rate over a set number of periods.
FV · intermediate
How to fv future value
Project the future value of a series of equal periodic payments or a lump sum growing at a constant interest rate.
IRR · advanced
How to irr return rate
Find the discount rate at which the net present value of a series of cash flows equals zero — the break-even return rate for an investment.
NPV · advanced
How to npv investment value
Discount a series of future cash flows to their present value and sum them — then subtract the initial investment to get net present value.
SLN · beginner
How to sln straight line depreciation
Return the straight-line depreciation expense for one period — equal annual write-down from cost to salvage value over the asset life.
RATE · advanced
How to rate interest rate
Find the periodic interest rate of a loan or investment given its payment amount, number of periods, and present value.
Array Formulas
Use dynamic arrays to filter, sort, deduplicate, and reshape data without helper columns.
SUMPRODUCT · intermediate
How to sumproduct weighted average
Multiply two arrays element-by-element and sum the products — the standard way to calculate weighted averages, conditional sums, and multi-criteria counts without helper columns.
UNIQUE · beginner
How to unique deduplicate list
Return a list of distinct values from a column or range, automatically spilling the results into adjacent cells without a helper column.
FILTER · intermediate
How to filter rows by condition
Return only the rows from a table that match one or more conditions — a dynamic, formula-driven alternative to the AutoFilter feature.
SEQUENCE · beginner
How to sequence number rows
Spill a consecutive number sequence into a range of cells — useful for numbering rows, generating date series, or building lookup indexes.
LET · advanced
How to let reuse calculation
Assign names to intermediate values inside a formula so they can be reused without repeating the calculation — makes complex formulas readable and faster.
SORT · beginner
How to sort dynamic array
Return a sorted copy of a range or array, spilling the results automatically — without modifying the source data.
Data Cleaning
Remove whitespace, fix casing, strip unwanted characters, and standardize messy data.
ISBLANK · beginner
How to isblank check empty
Return TRUE if a cell is completely empty and FALSE if it contains any value, space, or formula result — the reliable way to detect genuinely empty cells.
VALUE · beginner
How to value convert text number
Convert a number stored as text into a real numeric value that SUM, AVERAGE, and other math functions can use.
COUNTIF · beginner
How to remove duplicates countif
Mark every row that contains a value appearing more than once in a column — without removing data or using a table feature.
TRIM · beginner
How to trim remove spaces
Strip leading, trailing, and excess internal spaces from a text string, leaving only single spaces between words.
CLEAN · beginner
How to clean remove nonprintable
Strip invisible control characters and non-printable characters from imported text — the first fix for data that looks clean but breaks formulas.
IFERROR · beginner
How to iferror suppress errors
Return a custom value or blank instead of an error code when a formula fails — keeps dashboards and reports readable.
ISNUMBER · beginner
How to isnumber validate numeric
Return TRUE if a cell contains a numeric value and FALSE if it contains text, a blank, or an error — the fastest way to find numbers stored as text.
Text Manipulation
Extract, combine, clean, and reformat text values from any cell or column.
CONCAT · beginner
How to concat full address
Merge street, city, state, and zip columns into a single formatted address string.
PROPER · beginner
How to proper case names
Capitalise the first letter of every word in a cell and lowercase the rest — useful for cleaning name or title columns.
LEN · beginner
How to len count characters
Return the total number of characters in a cell, including spaces and punctuation — useful for validation and dynamic formulas.
MID · intermediate
How to mid extract substring
Pull a segment of text from any position inside a string by specifying a start position and character count.
LEFT · beginner
How to left extract prefix
Pull a fixed number of characters from the beginning of a text string — useful for extracting codes, prefixes, or initials.
RIGHT · beginner
How to right extract suffix
Pull a fixed number of characters from the end of a text string — useful for zip codes, file extensions, or trailing identifiers.
SUBSTITUTE · beginner
How to substitute replace text
Replace every occurrence of a specific word, character, or phrase in a cell without affecting the rest of the text.
TEXTJOIN · beginner
How to textjoin combine list
Combine all values from a column range into a single comma-separated string, ignoring blanks.
TEXTJOIN · beginner
How to textjoin name builder
Combine first name, middle name, and last name values into one clean full-name field.
Conditional Logic
Build formulas that branch cleanly when different business rules or thresholds apply.
AND · beginner
How to and or combined logic
Return TRUE only when all specified conditions are met (AND), or when at least one condition is met (OR) — the building blocks of compound conditional logic.
IFS · intermediate
How to ifs multiple conditions
Test a series of conditions in order and return the value for the first one that is true — cleaner than deeply nested IF statements.
SWITCH · intermediate
How to switch exact match
Compare an expression to a list of exact values and return the result for the first match — cleaner than IFS for exact-match branching.
IF · beginner
How to nested if grade scale
Convert numeric scores into letter grades with threshold-based nested IF logic.
Lookup & Reference
Reference formulas for pulling values across tables, finding matches, and building reliable lookup patterns.
HLOOKUP · intermediate
How to hlookup row lookup
Search for a value in the first row of a table and return the corresponding value from a specified row below it.
INDEX · advanced
How to index match two way
Look up a value by both row and column simultaneously — the most flexible way to retrieve a value from a table using two criteria.
VLOOKUP · intermediate
How to vlookup match two columns
Match an ID in one table to another table and return the related value with an exact-match VLOOKUP.
XLOOKUP · intermediate
How to xlookup last match
Return the last matching record in a list by searching from the bottom instead of the top.