Formula library

Excel formula list: ready-to-copy formulas for Excel and Google Sheets

42 formulas checked by hand, each with an explanation, an example and a Google Sheets version. Free and unlimited.

Comma ,Semicolon ;

Lookup

INDEX and MATCH
A flexible lookup that works in every version of Excel and Google Sheets and can look left.
```
=INDEX(Products!$C$2:$C$100, MATCH(A2, Products!$A$2:$A$100, 0))
```
Explanation, example and Sheets version
Look up a value from another file or tab
Pull matching data from a different tab, or from another Google Sheets file with IMPORTRANGE.
```
=XLOOKUP(A2, 'Price List'!A:A, 'Price List'!B:B, "")
```
Explanation, example and Sheets version
Lookup with two criteria
Return a value where two columns both match, for example a price by product and size.
```
=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), C2:C100, "Not found")
```
Explanation, example and Sheets version
VLOOKUP with an exact match
Find a value in the first column of a table and return a value from another column in the same row.
```
=VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE)
```
Explanation, example and Sheets version
XLOOKUP with a "not found" message
The modern replacement for VLOOKUP: look up in any column, return from any column, with a built-in fallback.
```
=XLOOKUP(A2, Products!A2:A100, Products!C2:C100, "Not found")
```
Explanation, example and Sheets version

Conditional sums & counts

Average with conditions
Average only the values that meet one or more conditions.
```
=IFERROR(AVERAGEIFS(C2:C100, B2:B100, "Sales"), 0)
```
Explanation, example and Sheets version
COUNTIFS with a number and a text condition
Count rows that pass several tests, such as orders over 500 that are marked Paid.
```
=COUNTIFS(D2:D100, ">500", E2:E100, "Paid")
```
Explanation, example and Sheets version
Sum values for a given month
Total everything dated in the same month as a date you choose.
```
=SUMIFS(C2:C100, A2:A100, ">="&F1, A2:A100, "<="&EOMONTH(F1, 0))
```
Explanation, example and Sheets version
Sum only visible (filtered) rows
A total that updates when you filter the table and ignores hidden rows.
```
=SUBTOTAL(109, C2:C100)
```
Explanation, example and Sheets version
SUMIFS with multiple criteria
Add up values that meet several conditions at once, like sales for one region and status.
```
=SUMIFS(D2:D100, B2:B100, "West", C2:C100, "Paid")
```
Explanation, example and Sheets version

Logic & errors

IF with AND / OR
Return one result when several conditions are all true (AND) or when any is true (OR).
```
=IF(AND(B2>=50, C2="Yes"), "Pass", "Fail")
```
Explanation, example and Sheets version
Hide errors with IFERROR
Show a clean value instead of #DIV/0!, #N/A or other errors.
```
=IFERROR(B2/C2, 0)
```
Explanation, example and Sheets version
Letter grades with IFS
Turn a score into a grade without deeply nested IFs.
```
=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2>=60, "D", TRUE, "F")
```
Explanation, example and Sheets version
Flag overdue tasks
Mark a row as Overdue when its due date has passed and it is not done.
```
=IF(AND(C2<>"", C2<TODAY(), D2<>"Done"), "Overdue", "")
```
Explanation, example and Sheets version

Text

Combine text from several cells
Join first and last names, or any columns, with a separator and no stray spaces.
```
=TEXTJOIN(" ", TRUE, A2, B2, C2)
```
Explanation, example and Sheets version
Check if a cell contains a word
Return Yes or No depending on whether some text appears anywhere in a cell.
```
=IF(ISNUMBER(SEARCH("refund", A2)), "Yes", "No")
```
Explanation, example and Sheets version
Extract an email address from text
Pull the email address out of a sentence or a messy cell using a regular expression.
```
=REGEXEXTRACT(A2, "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}")
```
Explanation, example and Sheets version
Extract the first name
Get everything before the first space in a full name.
```
=LEFT(A2, FIND(" ", A2&" ")-1)
```
Explanation, example and Sheets version
Extract the last name
Get the last word of a full name, even when there is a middle name.
```
=TRIM(RIGHT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", 100)), 100))
```
Explanation, example and Sheets version
Keep only the digits
Strip letters, spaces and symbols so only the numbers remain, for example from phone numbers.
```
=REGEXREPLACE(A2, "[^0-9]", "")
```
Explanation, example and Sheets version
Change text case
Convert names to Proper Case, or any text to UPPER or lower case.
```
=PROPER(TRIM(A2))
```
Explanation, example and Sheets version
Split text into columns
Break "red, green, blue" into separate cells with one formula.
```
=TEXTSPLIT(A2, ", ")
```
Explanation, example and Sheets version
Remove extra spaces and hidden characters
Clean up text pasted from websites or other systems so lookups and comparisons work.
```
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))
```
Explanation, example and Sheets version

Dates

Add months to a date
Move a date forward or back by whole months, for renewals and due dates.
```
=EDATE(A2, B2)
```
Explanation, example and Sheets version
Calculate age from a date of birth
Get someone's age in whole years, as of today.
```
=DATEDIF(A2, TODAY(), "Y")
```
Explanation, example and Sheets version
Days between two dates
Count the calendar days from a start date to an end date.
```
=DAYS(B2, A2)
```
Explanation, example and Sheets version
Last day of the month
Find the month-end date for any date, useful for billing and reports.
```
=EOMONTH(A2, 0)
```
Explanation, example and Sheets version
Day of the week from a date
Show the weekday name, like Monday, for any date.
```
=TEXT(A2, "dddd")
```
Explanation, example and Sheets version
Working days between two dates
Count Monday to Friday between two dates, optionally skipping holidays.
```
=NETWORKDAYS(A2, B2, Holidays!$A$2:$A$20)
```
Explanation, example and Sheets version

Lists & duplicates

Count unique values
Count how many different values a column contains.
```
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))
```
Explanation, example and Sheets version
Show only rows that match
Return every row where a column has a certain value, as a live list.
```
=FILTER(A2:D100, C2:C100="Open", "None")
```
Explanation, example and Sheets version
Flag duplicate values
Mark every value that appears more than once in a column.
```
=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "")
```
Explanation, example and Sheets version
Rank values
Give each row its position, with 1 for the highest value.
```
=RANK.EQ(B2, $B$2:$B$100, 0)
```
Explanation, example and Sheets version
Top 5 by value
List the five highest rows, for example top products by sales.
```
=TAKE(SORT(A2:B100, 2, -1), 5)
```
Explanation, example and Sheets version
Get a list of unique values
Make a de-duplicated, sorted list from a column that updates automatically.
```
=SORT(UNIQUE(FILTER(A2:A100, A2:A100<>"")))
```
Explanation, example and Sheets version

Math, percentages & finance

Compound interest
See what savings grow to with monthly compounding.
```
=FV(B1/12, B2*12, 0, -B3)
```
Explanation, example and Sheets version
Monthly loan payment
Work out the fixed monthly payment for a loan or mortgage.
```
=PMT(B1/12, B2*12, -B3)
```
Explanation, example and Sheets version
Percentage change
Show the growth or decline from an old value to a new value.
```
=IFERROR((B2-A2)/A2, "")
```
Explanation, example and Sheets version
Percentage of total
Show each row's share of the column total.
```
=B2/SUM($B$2:$B$100)
```
Explanation, example and Sheets version
Pick a random item from a list
Choose a random name or row, for giveaways or sampling.
```
=INDEX(A2:A100, RANDBETWEEN(1, COUNTA(A2:A100)))
```
Explanation, example and Sheets version
Running total
A cumulative sum that grows row by row.
```
=SUM($B$2:B2)
```
Explanation, example and Sheets version
Weighted average
Average where some values count more than others, like grades with credit hours.
```
=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10)
```
Explanation, example and Sheets version

Can't find the one you need?

Describe it in plain English and the AI writes it for your exact columns and Excel version.

Generate a formula