Excel Functions

Excel AND Function

AND is the logical glue behind most multi-condition IF formulas. Learn how it evaluates conditions, where it silently breaks, and when to pair it with OR instead.

Excel AVERAGEIF Function

AVERAGEIF calculates an average based on a single condition, no helper columns needed. Here's how the syntax works, where it silently gives wrong answers, and when to switch to AVERAGEIFS.

Excel AVERAGEIFS Function

AVERAGEIFS averages a range of numbers based on multiple conditions at once. It's the tool for questions like average sales in the West region during March.

Excel CHAR Function

CHAR turns a number into a text character, useful for inserting line breaks, quotation marks, and other symbols you can't easily type into a formula. Here's the syntax, the platform quirks, and when to switch to UNICHAR.

Excel CLEAN Function

CLEAN removes line breaks and control characters that sneak in when you copy data from databases, websites, or PDFs. Learn what it actually deletes, what it leaves behind, and why TRIM has to finish the job.

Excel CODE Function

CODE turns the first character of any text into its underlying number. Useful for sorting, validation, and bridging worksheet formulas to VBA.

Excel CONCATENATE Function

CONCATENATE combines text from multiple cells into a single cell. It still works, but Microsoft recommends TEXTJOIN or CONCAT for anything new.

Excel COUNT Function

COUNT answers one question: how many of these cells actually hold a number? It's the fastest way to check for missing data before you build a chart, average, or dashboard on top of it.

Excel COUNTA Function

COUNTA tells you how many cells in a range actually contain something. It's the function to reach for when you need a count that includes text and dates, not just numbers.

Excel COUNTBLANK Function

COUNTBLANK tells you how many cells in a range are empty, which makes it the fastest way to spot missing entries in a form, order list, or survey. But it counts more than truly empty cells, and that trips people up.

Excel COUNTIF Function

COUNTIF counts cells that match a single condition — no formulas, no helper columns. Here's how the syntax works and where people get it wrong.

Excel COUNTIFS Function

COUNTIFS extends COUNTIF to handle multiple conditions at once, no helper columns required. Here's the syntax, real examples, and where people get tripped up.

Excel DATE Function

DATE turns three separate numbers into one date Excel can calculate with. It's the function that makes dynamic date-building possible.

Excel DATEDIF Function

DATEDIF measures the gap between two dates in whatever unit you need, from complete years to total days. It's also one of the only Excel functions Microsoft never bothered to document.

Excel DATEVALUE Function

If your dates are stuck as left-aligned text after an import from a CSV, PDF, or database export, DATEVALUE turns them back into real, calculable Excel dates. Here's the syntax, the regional-settings trap nobody warns you about, and a formula pattern for columns that mix real dates with text dates.

Excel DAY Function

DAY pulls the day-of-month number out of any date, so you can flag month-end transactions, build payroll trackers, or rebuild dates with DATE. It takes one argument, but text dates and regional formatting can quietly return the wrong number.

Excel DAYS Function

DAYS subtracts one date from another and hands you a clean number, no formatting cleanup required. Get the argument order backward and you'll get a negative result with no warning.

Excel DOLLAR Function

DOLLAR turns a number into currency-formatted text, useful for building report sentences and invoice summaries. It rounds automatically, but the output can't be used in further math without converting it back to a number first.

Excel EDATE Function

EDATE calculates renewal dates, maturity dates, and expirations by adding or subtracting whole months from a date. No manual month counting, no calendar math.

Excel EOMONTH Function

EOMONTH finds the last day of any month, past, present, or future, from a single starting date. It's the fastest way to build invoice due dates, subscription renewals, and fiscal quarter-end formulas without a calendar.

Excel EXACT Function

EXACT is the function you reach for when Excel's default comparison isn't strict enough. It catches case differences that the equal sign ignores completely.

Excel FILTER Function

FILTER pulls matching records out of a data set without formulas, helper columns, or the Data tab. Change the source data and the results update on their own.

Excel FIND Function

FIND locates the position of one text string inside another, character by character. It's case-sensitive and doesn't support wildcards, which trips up a lot of people who reach for it expecting SEARCH-like flexibility.

Excel FIXED Function

FIXED turns a number into formatted text, decimals and all, so you can merge it into a label or report sentence without a separate number format. Here's the syntax, the gotchas, and when to use TEXT instead.

Excel HLOOKUP Function

HLOOKUP finds a value in the first row of a table and returns data from any row below it. In Excel 365 and 2021, XLOOKUP replaces it entirely.

Excel HOUR Function

HOUR pulls just the hour out of any time or date-time value, ignoring minutes, seconds, and the date itself. It's the building block for shift reports, time-bucketed dashboards, and decimal-hours payroll formulas.

Excel HYPERLINK Function

Turn any cell into a clickable link, whether it points to a website, a file on your network, or a cell three sheets away. Here's the syntax, the gotchas, and how to build links dynamically.

Excel IF Function

IF is the function behind almost every conditional formula in Excel — pass or fail, over or under budget, yes or no. Here's how to write one that works on the first try.

Excel IFERROR Function

IFERROR replaces ugly error codes like #N/A and #DIV/0! with a value you choose. It's simple to use, but it catches every error type — including the ones you didn't mean to hide.

Excel IFS Function

IFS lets you test up to 127 conditions in a single formula instead of nesting IF inside IF inside IF. Here's how the syntax works, where people get tripped up, and when SWITCH or nested IF is actually the better call.

Excel INDEX Function

INDEX is the backbone of Excel's most reliable lookup pattern, INDEX/MATCH. Here's how the array form, reference form, and area_num argument actually work — and where INDEX breaks in ways VLOOKUP never will.

Excel INDIRECT Function

INDIRECT turns plain text into a working cell reference, which means you can build formulas that point at different sheets, ranges, or workbooks based on what's typed into a cell. It's also volatile and easy to misuse — here's how to use it without wrecking your workbook's performance.

Excel ISBLANK Function

ISBLANK tells you whether a cell has absolutely nothing in it, not just whether it looks empty. Here's how it works, where it gets tricked, and what to use instead when it doesn't do what you expect.

Excel ISNUMBER Function

ISNUMBER tells you whether a cell holds a real number or something Excel only treats like one. It's the quiet workhorse behind data validation, error trapping, and text-search formulas.

Excel LAMBDA Function

LAMBDA turns a formula into a named, reusable function you can call from any cell in the workbook. This article covers the mechanics competitors skip: optional parameters, live scope, error propagation, and sharing a LAMBDA library across files.

Excel LEFT Function

LEFT pulls a specific number of characters from the beginning of a cell's text. It's the go-to function for splitting codes, IDs, and names apart without a formula that breaks the moment your data shifts.

Excel LEN Function

LEN measures the length of whatever is in a cell, character by character. It's the fastest way to catch stray spaces, mismatched codes, and bad data imports before they break something downstream.

Excel LET Function

LET turns a formula full of repeated lookups and calculations into something you can actually read. It also stops Excel from calculating the same thing twice.

Excel LOWER Function

LOWER converts any text string to lowercase, letter by letter. Use it to standardize names, build email addresses, or clean up inconsistent capitalization in imported data.

Excel MATCH Function

MATCH doesn't return data — it returns a row or column number, which is exactly why INDEX needs it. Here's how the syntax works and where the default match type quietly breaks formulas.

Excel MAX Function

MAX finds the highest number in a data set with one simple formula. Here's how it handles errors, dates, and conditions competitors barely mention.

Excel MID Function

MID pulls a chunk of text out of the middle of a string once you tell it where to start and how many characters to grab. Here's how to use it, plus how to pair it with FIND or SEARCH when you don't know the exact position in advance.

Excel MINUTE Function

MINUTE pulls the minute out of any time value, so you can sort, calculate, or reassemble time data piece by piece. Here's how it works, plus the rollover and text-parsing traps that catch beginners.

Excel MOD Function

MOD finds what's left over after division. It's one of the simplest math functions in Excel, and one of the most useful for building patterns, schedules, and checks.

Excel MONTH Function

MONTH pulls the month number out of a date so you can group, filter, or calculate by month. Pair it with TEXT for month names and EOMONTH for month-end dates.

Excel NETWORKDAYS Function

NETWORKDAYS calculates how many business days fall between a start and end date, which makes it the go-to formula for payroll, billing, and project timelines. It handles weekends automatically, but text-formatted dates and reversed date order are where it quietly breaks.

Excel NOW Function

NOW gives you a timestamp that refreshes automatically, useful for logging entries or building a live clock. Here's how the serial number works, when it actually updates, and how to freeze it when you don't want it changing.

Excel NUMBERVALUE Function

NUMBERVALUE turns text like "1.234,56" into an actual number Excel can calculate with, no matter which country's separator convention it was typed in. It's the fix for imported data that looks numeric but sorts, sums, and filters like text.

Excel OFFSET Function

OFFSET builds a reference on the fly instead of pointing at a fixed cell. That flexibility makes it useful for dynamic ranges and rolling calculations — and it's also why it can quietly slow down a large workbook.

Excel OR Function

OR checks up to 255 conditions and returns TRUE the moment one of them passes. Pair it with IF for 'this or that' logic, or nest it inside AND for compound rules.

Excel PMT Function

PMT turns a loan amount, interest rate, and term into a single monthly (or quarterly, or annual) payment figure. This article covers the sign convention that trips up nearly everyone, plus the gotchas competitors skip.

Excel PROPER Function

PROPER cleans up inconsistent text casing in one formula, turning 'JOHN SMITH' or 'jane doe' into 'John Smith.' It's the fastest fix for messy name and title data, but it has some odd blind spots worth knowing before you rely on it.

Excel RAND and RANDBETWEEN Functions

Need random numbers for test data or simulations? RAND and RANDBETWEEN generate them instantly. Here's when to use each one.

Excel RANK Function

RANK ranks a value against a list of other values. It still works in Excel 365, but Microsoft retired it in favor of RANK.EQ and RANK.AVG. Here's what changed and which one to use instead.

Excel REPLACE Function

REPLACE swaps characters by position instead of by content, which makes it ideal for fixed-length codes, account numbers, and structured text. Here's how the syntax works, and where it trips up on dates and numbers.

Excel REPT Function

Learn how REPT repeats text in Excel, from zero-padding invoice numbers to building star ratings and in-cell bar charts without a single chart object.

Excel RIGHT Function

RIGHT pulls characters from the end of a string, whether that's the last four digits of an account number or a file extension. Here's the syntax, the common pairings, and where it silently breaks.

Excel ROUND Function

ROUND changes a number's actual value, not just how it looks in a cell. Here's how the syntax works, when to use negative digits, and why formatting a cell isn't the same thing.

Excel ROUNDUP Function

ROUNDUP forces a number upward, no matter what the next digit is. Use it any time "close enough" isn't good enough, like packing boxes or billing hours.

Excel SEARCH Function

SEARCH locates a substring inside a longer string and returns its starting position as a number. It's the function to reach for when you need a case-insensitive text match with wildcard support.

Excel SECOND Function

SECOND pulls just the seconds digit out of any time value. Useful for time math, timestamp parsing, and building custom time formats, but it resets every 60 seconds instead of totaling elapsed time.

Excel SEQUENCE Function

SEQUENCE builds numbered lists, custom counting patterns, and date series without a fill handle or a single dragged formula. One cell, one array, done.

Excel SORT Function

SORT reorders rows or columns without touching your source data or writing a single helper column. Here's how the syntax works, where it breaks, and when to reach for SORTBY instead.

Excel SUBSTITUTE Function

SUBSTITUTE finds a piece of text inside a cell and swaps it for something else, no matter where it sits in the string. It's the formula-based find-and-replace Excel doesn't put on the ribbon.

Excel SUBTOTAL Function

SUBTOTAL is the one Excel function built to skip filtered rows automatically. Here's how the function_num codes work, how the Data > Subtotal feature uses them, and the formulas for summing only what's visible.

Excel SUMIF Function

SUMIF adds up values in a range that meet one condition you specify. This guide covers the syntax, real formulas, and the mistakes that trip up beginners.

Excel SUMIFS Function

SUMIFS adds up values that meet every condition you set — region, date, product, all at once. Here's the syntax, the argument-order trap everyone hits, and real formulas you can copy.

Excel SUMPRODUCT Function

SUMPRODUCT multiplies and sums arrays in a single step, which makes it the go-to formula for multi-criteria sums, OR logic, and weighted averages that SUMIFS can't touch. It also works on closed workbooks and needs no array-entry shortcut.

Excel SWITCH Function

SWITCH replaces long chains of nested IF statements with one clean, readable formula. Match a value against a list of options and return the result tied to the first one that fits.

Excel TEXT Function

TEXT turns raw numbers and dates into formatted text for reports, labels, and merged strings. Here's the full format code reference, common mistakes, and when to reach for VALUE instead.

Excel TEXTJOIN Function

TEXTJOIN merges text from cells, ranges, or arrays into a single string and lets you pick the separator. It's the fastest way to build comma-separated lists without nesting a dozen ampersands.

Excel TEXTSPLIT Function

TEXTSPLIT breaks a text string apart at any delimiter you specify and spills the pieces into columns or rows automatically. Unlike Text to Columns, it's a live formula that updates the moment your source data changes.

Excel TIME Function

Assemble an exact time from separate hour, minute, and second values instead of typing it in manually. Covers syntax, the 24-hour reset that trips up duration formulas, and how TIME compares to NOW.

Excel TIMEVALUE Function

TIMEVALUE turns text like '9:00 AM' into a number Excel can actually do math with. Essential when you're importing timestamps from CSVs, databases, or other systems that store time as plain text.

Excel TODAY Function

TODAY pulls the current date straight from your computer's clock, no arguments needed. Here's how it works, why it sometimes seems stuck, and how to freeze it when you don't want it to move.

Excel TRIM Function

TRIM cleans up messy text by stripping extra spaces, but it only catches one specific character. Here's what it fixes, what it misses, and how to catch the rest.

Excel UNIQUE Function

UNIQUE pulls out every distinct value in a range with one formula, no helper columns or manual dedup required. It's one of the dynamic array functions that changed how Excel handles lists.

Excel UPPER Function

UPPER converts any text to all capital letters, and it's the fastest way to standardize inconsistent data entry. Learn the syntax, real use cases, and what happens when a date or number ends up inside it.

Excel VALUE Function

VALUE turns numeric-looking text into an actual number so SUM, AVERAGE, and comparison formulas stop ignoring it. Useful for cleaning up imported data where quantities, prices, or dates arrive as text instead of true values.

Excel VLOOKUP Function

VLOOKUP searches the first column of a table for a value, then returns data from any column to the right. Widely used, easy to misconfigure — especially around its default match mode.

Excel VSTACK Function

VSTACK stitches ranges, tables, and even entire sheets into a single vertical array without a single copy-paste. It's the fastest way to consolidate data that lives in separate blocks.

Excel WEEKDAY Function

WEEKDAY turns any date into a number representing the day of the week, which makes it the building block for weekend flags, workday counts, and Monday-of-week formulas. The default numbering starts the week on Sunday, which trips up a lot of Monday-based schedules.

Excel WEEKNUM Function

WEEKNUM turns any date into a week number for reporting, scheduling, or grouping data by week. The default numbering system trips up anyone expecting ISO week numbers, so know the difference before you build a report on it.

Excel WORKDAY Function

WORKDAY calculates delivery dates, deadlines, and project milestones without you counting weekends by hand. Give it a start date and a number of working days, and it hands back the finished date.

Excel XLOOKUP Function

XLOOKUP replaces VLOOKUP and HLOOKUP with a single function that looks in any direction and stays intact when columns are inserted. Here's the syntax, the gotchas, and when to still reach for VLOOKUP instead.

Excel YEAR Function

YEAR pulls the year out of any date so you can group, compare, or calculate with it on its own. This guide covers the syntax, real examples, and the date-system quirks that trip up even experienced users.

INDEX and MATCH in Excel: Two-Dimensional Lookups

VLOOKUP and HLOOKUP each search in one direction. Combine INDEX and MATCH and you can look up any value at the intersection of a row and column — no column number hardcoding required.

VLOOKUP and HLOOKUP Functions