✦ Modern spreadsheet intelligence studio
Excel Made Easy — AI Formula Generator
Generate, explain, repair and automate Excel formulas in one focused workspace — with a live calculation sandbox and a searchable reference of 150 copy-ready formulas.
Describe your calculation requirement:Ctrl + Enter to run
✨ Possible correction:
Sum by condition
Lookup value
Count this month
Split text
IF condition
Logic & Evaluation
Implementation Steps
Edge Cases & Audit Notes
⚡ Interactive Sandbox
Test calculations live in-browser and watch the result update instantly.
150 Excel Formula Reference
Copy-ready examples for lookups, errors, text, dates, finance, analysis, dynamic arrays and everyday spreadsheet work.
⌕
Showing 150 of 150•Search works instantly on mobile and desktop
| Target Intent / Problem | Copy-Ready Formula | Category | Action |
|---|---|---|---|
| XLOOKUP exact match Modern exact lookup with a friendly fallback. |
=XLOOKUP(E2,A2:A500,B2:B500,"Not found",0) |
Lookup | |
| XLOOKUP approximate match Returns the next smaller matching value when an exact match is unavailable. |
=XLOOKUP(E2,A2:A500,B2:B500,"Not found",-1) |
Lookup | |
| XLOOKUP wildcard lookup Finds a text match using wildcard criteria. |
=XLOOKUP("*"&E2&"*",A2:A500,B2:B500,"Not found",2) |
Lookup | |
| XLOOKUP from right to left Looks up a value and returns a result from a column on either side. |
=XLOOKUP(E2,B2:B500,A2:A500,"Not found") |
Lookup | |
| XLOOKUP last match Returns the last exact match instead of the first. |
=XLOOKUP(E2,A2:A500,B2:B500,"Not found",0,-1) |
Lookup | |
| XLOOKUP multiple criteria Matches two conditions before returning a result. |
=XLOOKUP(1,(A2:A500=E2)*(B2:B500=F2),C2:C500,"Not found") |
Lookup | |
| INDEX MATCH exact Classic flexible alternative to VLOOKUP. |
=INDEX(B2:B500,MATCH(E2,A2:A500,0)) |
Lookup | |
| INDEX MATCH two-way Finds the intersection of a row and column header. |
=INDEX(B2:M100,MATCH(O2,A2:A100,0),MATCH(P2,B1:M1,0)) |
Lookup | |
| INDEX MATCH multiple criteria Returns a result for multiple conditions. |
=INDEX(C2:C500,MATCH(1,(A2:A500=E2)*(B2:B500=F2),0)) |
Lookup | |
| VLOOKUP exact Exact-match lookup using a table's first column. |
=VLOOKUP(E2,A2:C500,3,FALSE) |
Lookup | |
| VLOOKUP approximate Approximate lookup for sorted ranges. |
=VLOOKUP(E2,A2:C500,3,TRUE) |
Lookup | |
| VLOOKUP with IFNA Shows a readable result instead of #N/A. |
=IFNA(VLOOKUP(E2,A2:C500,3,FALSE),"Not found") |
Lookup | |
| HLOOKUP exact Looks across a header row for an exact match. |
=HLOOKUP(E2,A1:M3,3,FALSE) |
Lookup | |
| MATCH exact position Returns the position of an exact match. |
=MATCH(E2,A2:A500,0) |
Lookup | |
| XMATCH exact position Modern position lookup with flexible match options. |
=XMATCH(E2,A2:A500,0) |
Lookup | |
| XMATCH last match Finds the last exact occurrence. |
=XMATCH(E2,A2:A500,0,-1) |
Lookup | |
| SUMIF one condition Adds values meeting one condition. |
=SUMIF(B2:B500,"Paid",C2:C500) |
Aggregation | |
| SUMIFS multiple conditions Sums values using multiple criteria. |
=SUMIFS(C2:C500,A2:A500,"Closed",B2:B500,"Q3") |
Aggregation | |
| SUMIFS date range Sums records between two dates. |
=SUMIFS(C:C,A:A,">="&E2,A:A,"<="&F2) |
Aggregation | |
| SUMPRODUCT conditional sum Performs a condition-based sum without a helper column. |
=SUMPRODUCT((A2:A500="Paid")*C2:C500) |
Aggregation | |
| COUNTIF one condition Counts cells matching one condition. |
=COUNTIF(B2:B500,"Completed") |
Counting | |
| COUNTIFS multiple conditions Counts rows satisfying several conditions. |
=COUNTIFS(A2:A500,"East",B2:B500,"Completed") |
Counting | |
| COUNTIFS date range Counts records within a date interval. |
=COUNTIFS(A:A,">="&E2,A:A,"<="&F2) |
Counting | |
| COUNTA nonblank Counts non-empty cells. |
=COUNTA(A2:A500) |
Counting | |
| COUNT numbers Counts cells containing numeric values. |
=COUNT(A2:A500) |
Counting | |
| COUNTBLANK Counts blank cells in a range. |
=COUNTBLANK(A2:A500) |
Counting | |
| AVERAGEIF Averages values meeting one condition. |
=AVERAGEIF(B2:B500,"Paid",C2:C500) |
Statistics | |
| AVERAGEIFS Averages values using multiple criteria. |
=AVERAGEIFS(C2:C500,A2:A500,"East",B2:B500,"Paid") |
Statistics | |
| MAXIFS Returns the largest value meeting a condition. |
=MAXIFS(C2:C500,A2:A500,"East") |
Statistics | |
| MINIFS Returns the smallest value meeting a condition. |
=MINIFS(C2:C500,A2:A500,"East") |
Statistics | |
| FILTER by condition Returns only rows that meet a condition. |
=FILTER(A2:D500,B2:B500="Approved","No records") |
Dynamic Array | |
| FILTER multiple conditions Filters with AND-style multiple conditions. |
=FILTER(A2:D500,(B2:B500="Approved")*(C2:C500>1000),"No records") |
Dynamic Array | |
| FILTER OR conditions Filters rows matching either of two conditions. |
=FILTER(A2:D500,(B2:B500="Approved")+(B2:B500="Pending"),"No records") |
Dynamic Array | |
| FILTER dates Filters records between two dates. |
=FILTER(A2:D500,(A2:A500>=F1)*(A2:A500<=G1),"No records") |
Dynamic Array | |
| SORT ascending Sorts a table by its second column ascending. |
=SORT(A2:D500,2,1) |
Dynamic Array | |
| SORT descending Sorts a table by its second column descending. |
=SORT(A2:D500,2,-1) |
Dynamic Array | |
| SORTBY another range Sorts one range using another range as the key. |
=SORTBY(A2:D500,C2:C500,-1) |
Dynamic Array | |
| UNIQUE values Returns a distinct list from a range. |
=UNIQUE(A2:A500) |
Dynamic Array | |
| UNIQUE sorted Creates a unique list and sorts it. |
=SORT(UNIQUE(A2:A500)) |
Dynamic Array | |
| UNIQUE count Counts the number of distinct values. |
=COUNTA(UNIQUE(A2:A500)) |
Dynamic Array | |
| UNIQUE multi-column Returns distinct row combinations from two columns. |
=UNIQUE(A2:B500) |
Dynamic Array | |
| TAKE top rows Returns the first ten rows. |
=TAKE(A2:D500,10) |
Dynamic Array | |
| TAKE bottom rows Returns the last ten rows. |
=TAKE(A2:D500,-10) |
Dynamic Array | |
| DROP header Removes the first row from a spilled range. |
=DROP(A2:D500,1) |
Dynamic Array | |
| CHOOSECOLS selected columns Returns only selected columns. |
=CHOOSECOLS(A2:F500,1,3,6) |
Dynamic Array | |
| CHOOSEROWS selected rows Returns selected rows from a range. |
=CHOOSEROWS(A2:D500,1,5,10) |
Dynamic Array | |
| TOCOL flatten range Converts a multi-column range into one column. |
=TOCOL(A2:D500,1) |
Dynamic Array | |
| TOROW flatten range Converts a range into one row. |
=TOROW(A2:D500,1) |
Dynamic Array | |
| VSTACK tables Stacks two tables vertically. |
=VSTACK(A2:C100,E2:G100) |
Dynamic Array | |
| HSTACK ranges Joins ranges side by side. |
=HSTACK(A2:C100,E2:G100) |
Dynamic Array | |
| WRAPROWS Wraps a list into rows of a chosen width. |
=WRAPROWS(A2:A20,5) |
Dynamic Array | |
| WRAPCOLS Wraps a list into columns of a chosen height. |
=WRAPCOLS(A2:A20,5) |
Dynamic Array | |
| LET reusable calculation Names an intermediate value to make a formula clearer. |
=LET(x,A2*B2,x*(1-C2)) |
Logic | |
| IF condition Returns one result when a condition is true and another when false. |
=IF(A2>100,"High","Low") |
Logic | |
| IFS multiple conditions Evaluates several conditions in order. |
=IFS(A2>=90,"A",A2>=75,"B",A2>=60,"C",TRUE,"D") |
Logic | |
| AND conditions Returns TRUE only when every condition is true. |
=AND(A2>0,B2="Paid") |
Logic | |
| OR conditions Returns TRUE when at least one condition is true. |
=OR(B2="Paid",B2="Completed") |
Logic | |
| NOT condition Reverses a logical result. |
=NOT(A2="Closed") |
Logic | |
| IFERROR fallback Replaces formula errors with a controlled fallback. |
=IFERROR(A2/B2,0) |
Error Handling | |
| IFNA fallback Handles #N/A without masking other error types. |
=IFNA(XLOOKUP(E2,A:A,B:B),"Not found") |
Error Handling | |
| Safe division #DIV/0 Prevents division by zero. |
=IF(B2=0,0,A2/B2) |
Error Handling | |
| ISERROR test Tests whether a cell contains any error. |
=ISERROR(A2) |
Error Handling | |
| ISNA test Tests specifically for #N/A. |
=ISNA(A2) |
Error Handling | |
| ISBLANK test Tests whether a cell is truly blank. |
=ISBLANK(A2) |
Data Checks | |
| ISNUMBER test Checks whether a value is numeric. |
=ISNUMBER(A2) |
Data Checks | |
| ISTEXT test Checks whether a value is text. |
=ISTEXT(A2) |
Data Checks | |
| TRIM extra spaces Removes leading, trailing, and repeated standard spaces. |
=TRIM(A2) |
Text | |
| CLEAN nonprintable characters Removes non-printing characters. |
=CLEAN(A2) |
Text | |
| Remove non-breaking spaces Cleans web/ERP text containing non-breaking spaces. |
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) |
Data Hygiene | |
| UPPER text Converts text to uppercase. |
=UPPER(A2) |
Text | |
| LOWER text Converts text to lowercase. |
=LOWER(A2) |
Text | |
| PROPER names Capitalizes words in a text value. |
=PROPER(A2) |
Text | |
| LEFT characters Returns the first five characters. |
=LEFT(A2,5) |
Text | |
| RIGHT characters Returns the last five characters. |
=RIGHT(A2,5) |
Text | |
| MID characters Extracts a fixed-length substring. |
=MID(A2,3,5) |
Text | |
| LEN text length Counts characters in a cell. |
=LEN(A2) |
Text | |
| FIND exact text position Finds a case-sensitive text position. |
=FIND("-",A2) |
Text | |
| SEARCH text position Finds text without case sensitivity. |
=SEARCH("apple",A2) |
Text | |
| SUBSTITUTE text Replaces selected text within a cell. |
=SUBSTITUTE(A2,"old","new") |
Text | |
| REPLACE by position Replaces characters at specified positions. |
=REPLACE(A2,1,3,"New") |
Text | |
| TEXTJOIN delimiter Combines multiple cells with a delimiter. |
=TEXTJOIN(", ",TRUE,A2:A10) |
Text | |
| CONCAT text Combines text values. |
=CONCAT(A2," - ",B2) |
Text | |
| TEXTSPLIT delimiters Splits text by row and column delimiters. |
=TEXTSPLIT(A2,",",";",TRUE) |
Text Parsing | |
| TEXTBEFORE Returns text before a delimiter. |
=TEXTBEFORE(A2,"-") |
Text Parsing | |
| TEXTAFTER Returns text after a delimiter. |
=TEXTAFTER(A2,"-") |
Text Parsing | |
| EXACT text comparison Checks whether two text values match exactly, including case. |
=EXACT(A2,B2) |
Text | |
| VALUE text to number Converts a numeric text string into a number. |
=VALUE(A2) |
Data Conversion | |
| TEXT format number Formats a numeric value as text. |
=TEXT(A2,"#,##0.00") |
Formatting | |
| ROUND Rounds a number to two decimal places. |
=ROUND(A2,2) |
Math | |
| ROUNDUP Rounds away from zero to two decimal places. |
=ROUNDUP(A2,2) |
Math | |
| ROUNDDOWN Rounds toward zero to two decimal places. |
=ROUNDDOWN(A2,2) |
Math | |
| MROUND Rounds to the nearest multiple. |
=MROUND(A2,5) |
Math | |
| CEILING Rounds up to a specified multiple. |
=CEILING(A2,5) |
Math | |
| FLOOR Rounds down to a specified multiple. |
=FLOOR(A2,5) |
Math | |
| INT Rounds down to the nearest integer. |
=INT(A2) |
Math | |
| MOD Returns the remainder after division. |
=MOD(A2,B2) |
Math | |
| ABS Returns the absolute value. |
=ABS(A2) |
Math | |
| SUM range Adds all numeric values in a range. |
=SUM(A2:A500) |
Aggregation | |
| SUBTOTAL filtered sum Sums visible cells while respecting filtered rows. |
=SUBTOTAL(9,C2:C500) |
Aggregation | |
| AGGREGATE filtered average Calculates an aggregate while ignoring hidden rows and errors. |
=AGGREGATE(1,5,C2:C500) |
Aggregation | |
| PRODUCT range Multiplies all numbers in a range. |
=PRODUCT(A2:A10) |
Math | |
| SUMPRODUCT weighted total Calculates a row-by-row product and sums the results. |
=SUMPRODUCT(B2:B20,C2:C20) |
Math | |
| RANK descending Ranks a value from largest to smallest. |
=RANK.EQ(A2,$A$2:$A$20,0) |
Statistics | |
| PERCENTRANK Returns the relative percentile rank of a value. |
=PERCENTRANK.INC($A$2:$A$100,A2) |
Statistics | |
| PERCENTILE Returns the 90th percentile. |
=PERCENTILE.INC(A2:A100,0.9) |
Statistics | |
| QUARTILE Returns the third quartile. |
=QUARTILE.INC(A2:A100,3) |
Statistics | |
| MEDIAN Returns the middle value of a dataset. |
=MEDIAN(A2:A100) |
Statistics | |
| MODE Returns the most frequently occurring number. |
=MODE.SNGL(A2:A100) |
Statistics | |
| STDEV sample Calculates sample standard deviation. |
=STDEV.S(A2:A100) |
Statistics | |
| VAR sample Calculates sample variance. |
=VAR.S(A2:A100) |
Statistics | |
| LARGE Returns the third-largest value. |
=LARGE(A2:A100,3) |
Statistics | |
| SMALL Returns the third-smallest value. |
=SMALL(A2:A100,3) |
Statistics | |
| DATE create date Builds a date from year, month, and day. |
=DATE(2026,9,18) |
Dates & Time | |
| TODAY Returns the current date. |
=TODAY() |
Dates & Time | |
| NOW Returns the current date and time. |
=NOW() |
Dates & Time | |
| YEAR Extracts the year from a date. |
=YEAR(A2) |
Dates & Time | |
| MONTH Extracts the month from a date. |
=MONTH(A2) |
Dates & Time | |
| DAY Extracts the day from a date. |
=DAY(A2) |
Dates & Time | |
| WEEKDAY Returns weekday number with Monday as 1. |
=WEEKDAY(A2,2) |
Dates & Time | |
| WEEKNUM Returns the ISO-style week number convention. |
=WEEKNUM(A2,2) |
Dates & Time | |
| EDATE Moves a date forward by three months. |
=EDATE(A2,3) |
Dates & Time | |
| EOMONTH Returns the last day of the month. |
=EOMONTH(A2,0) |
Dates & Time | |
| DATEDIF years Calculates completed years between dates. |
=DATEDIF(A2,B2,"Y") |
Dates & Time | |
| NETWORKDAYS Counts working days excluding weekends and listed holidays. |
=NETWORKDAYS(A2,B2,Holidays!A2:A20) |
Dates & Time | |
| NETWORKDAYS.INTL Counts working days with a configurable weekend. |
=NETWORKDAYS.INTL(A2,B2,1,Holidays!A2:A20) |
Dates & Time | |
| WORKDAY Returns a date after ten working days. |
=WORKDAY(A2,10,Holidays!A2:A20) |
Dates & Time | |
| YEARFRAC Calculates the fractional number of years between dates. |
=YEARFRAC(A2,B2) |
Dates & Time | |
| CAGR Calculates compound annual growth rate. |
=((B2/A2)^(1/C2))-1 |
Finance | |
| NPV Calculates net present value, including the initial cash flow separately. |
=NPV(10%,B2:B6)+A2 |
Finance | |
| IRR Calculates the internal rate of return for periodic cash flows. |
=IRR(B2:B10) |
Finance | |
| XIRR Calculates IRR for cash flows occurring on irregular dates. |
=XIRR(B2:B10,A2:A10) |
Finance | |
| PMT loan payment Calculates a fixed monthly loan payment. |
=PMT(8%/12,60,-500000) |
Finance | |
| FV future value Calculates future value with periodic payments and an initial value. |
=FV(8%/12,60,-5000,-100000) |
Finance | |
| PV present value Calculates present value of periodic payments. |
=PV(8%/12,60,-10000) |
Finance | |
| IPMT interest payment Calculates the interest portion of a loan payment. |
=IPMT(8%/12,1,60,-500000) |
Finance | |
| PPMT principal payment Calculates the principal portion of a loan payment. |
=PPMT(8%/12,1,60,-500000) |
Finance | |
| Loan balance after payment Estimates remaining loan balance after a payment period. |
=FV(8%/12,24,PMT(8%/12,60,-500000),500000) |
Finance | |
| Percentage change Calculates percentage change from an old value to a new value. |
=(B2-A2)/A2 |
Business Math | |
| Percentage of total Calculates each row's share of a total. |
=A2/SUM($A$2:$A$20) |
Business Math | |
| Markup price Adds a markup percentage to cost. |
=A2*(1+B2) |
Business Math | |
| Discounted price Applies a percentage discount. |
=A2*(1-B2) |
Business Math | |
| Gross margin Calculates gross margin from revenue and cost. |
=(B2-C2)/B2 |
Finance | |
| Weighted average Calculates a weighted average. |
=SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10) |
Statistics | |
| Running total Creates a cumulative total as the formula is filled down. |
=SUM($B$2:B2) |
Analysis | |
| Running average Creates a cumulative average as the formula is filled down. |
=AVERAGE($B$2:B2) |
Analysis | |
| Duplicate flag Flags repeated values while retaining the first occurrence as unique. |
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique") |
Data Quality | |
| Duplicate count Counts how many times a value appears. |
=COUNTIF(A:A,A2) |
Data Quality | |
| Remove duplicates list Creates a distinct list without modifying the source data. |
=UNIQUE(A2:A500) |
Data Quality | |
| Blank check Labels whether a cell is blank. |
=IF(A2="","Blank","Filled") |
Data Quality | |
| Text number check Detects numbers stored as text. |
=IF(AND(ISTEXT(A2),ISNUMBER(--A2)),"Text number","Other") |
Data Quality |