Spreadsheet formulas templates
Excel and Google Sheets formulas for lookups, dates, text, maths, conditions and cleanup. 75 free templates. Open one to fill in the blanks and copy it.
Lookups
Find values in tables.
- XLOOKUP Snippet Modern lookup. =XLOOKUP([Lookup value], [Lookup range], [Return range], "Not found")
- VLOOKUP exact Snippet Classic lookup. =VLOOKUP([Lookup value], [Table], [Column number], FALSE)
- INDEX MATCH Snippet Flexible lookup. =INDEX([Return range], MATCH([Lookup value], [Lookup range], 0))
- Two-way lookup Snippet Row and column match. =INDEX([Table], MATCH([Row value], [Row headers], 0), MATCH([Column value], [Column headers], 0))
- Lookup last value Snippet Last non-empty cell. =LOOKUP(2, 1/([Range]<>""), [Range])
- FILTER rows Snippet Rows that match. =FILTER([Range], [Criteria range]=[Criteria], "None")
- UNIQUE values Snippet Distinct list. =UNIQUE([Range])
- SORT range Snippet Sorted copy. =SORT([Range], [Sort column], 1)
- Check exists Snippet Is it in the list? =IF(COUNTIF([Range], [Value])>0, "Yes", "No")
- IMPORTRANGE Snippet Sheets across files. =IMPORTRANGE("[Sheet URL]", "[Sheet name]![Range]")
Conditions and counting
If, sums and counts.
- IF Snippet Simple condition. =IF([Condition], [Value if true], [Value if false])
- IFS Snippet Several conditions. =IFS([Condition 1], [Result 1], [Condition 2], [Result 2], TRUE, [Otherwise])
- SUMIFS Snippet Sum with conditions. =SUMIFS([Sum range], [Criteria range], [Criteria])
- COUNTIFS Snippet Count with conditions. =COUNTIFS([Range 1], [Criteria 1], [Range 2], [Criteria 2])
- AVERAGEIFS Snippet Average with conditions. =AVERAGEIFS([Average range], [Criteria range], [Criteria])
- IFERROR Snippet Hide errors. =IFERROR([Formula], 0)
- AND OR Snippet Combine tests. =IF(AND([Test 1], [Test 2]), "Both", IF(OR([Test 1], [Test 2]), "One", "Neither"))
- Count blanks Snippet Empty cells. =COUNTBLANK([Range])
- Count text cells Snippet Non-empty text. =COUNTIF([Range], "*")
- Sum visible rows Snippet Ignore filtered rows. =SUBTOTAL(109, [Range])
- Percent of total Snippet Share. =[Cell]/SUM([Range])
- Rank Snippet Rank a value. =RANK.EQ([Cell], [Range], 0)
Dates and times
Work with calendars.
- Today Snippet Current date. =TODAY()
- Days between Snippet Difference in days. =[End date]-[Start date]
- Working days between Snippet Excludes weekends. =NETWORKDAYS([Start date], [End date])
- Add working days Snippet Due date calc. =WORKDAY([Start date], [Days])
- Add months Snippet Same day, later month. =EDATE([Start date], [Months])
- End of month Snippet Last day of month. =EOMONTH([today], 0)
- Age in years Snippet From birthday. =DATEDIF([Birth date], TODAY(), "Y")
- Week number Snippet ISO week. =ISOWEEKNUM([today])
- Day name Snippet Weekday text. =TEXT([today], "dddd")
- Month name Snippet Month text. =TEXT([today], "mmmm")
- Quarter Snippet Q1 to Q4. ="Q"&ROUNDUP(MONTH([today])/3, 0)
- Hours between times Snippet Time difference. =([End time]-[Start time])*24
- Build a date Snippet From parts. =DATE([Year], [Month], [Day])
Text
Clean and combine text.
- Join with separator Snippet TEXTJOIN. =TEXTJOIN(", ", TRUE, [Range])
- Concatenate Snippet Join cells. =[Cell 1]&" "&[Cell 2]
- Trim spaces Snippet Remove extra spaces. =TRIM([Cell])
- Proper case Snippet Capitalise words. =PROPER([Cell])
- Upper case Snippet All caps. =UPPER([Cell])
- Left characters Snippet First N characters. =LEFT([Cell], [N])
- Text before a character Snippet Split on a delimiter. =TEXTBEFORE([Cell], "[Delimiter]")
- Text after a character Snippet After the delimiter. =TEXTAFTER([Cell], "[Delimiter]")
- Split into columns Snippet TEXTSPLIT. =TEXTSPLIT([Cell], "[Delimiter]")
- Replace text Snippet SUBSTITUTE. =SUBSTITUTE([Cell], "[Old]", "[New]")
- Contains text Snippet Search in text. =ISNUMBER(SEARCH("[Text]", [Cell]))
- First name from full name Snippet Before the space. =LEFT([Cell], FIND(" ", [Cell])-1)
- Email domain Snippet After the @. =MID([Cell], FIND("@", [Cell])+1, 100)
- Format number as text Snippet Currency text. =TEXT([Cell], "$#,0.00")
- Character count Snippet LEN. =LEN([Cell])
- Remove non-printing Snippet CLEAN. =CLEAN(TRIM([Cell]))
Maths and finance
Numbers and money.
- Percent change Snippet Growth. =([New]-[Old])/[Old]
- Round to decimals Snippet ROUND. =ROUND([Cell], 2)
- Round up to nearest Snippet CEILING. =CEILING([Cell], [Multiple])
- Compound growth rate Snippet CAGR. =([End value]/[Start value])^(1/[Years])-1
- Loan payment Snippet PMT. =PMT([Annual rate]/12, [Months], -[Loan amount])
- Future value Snippet FV. =FV([Rate], [Periods], -[Payment], -[Present value])
- Net present value Snippet NPV. =NPV([Rate], [Cash flows])+[Initial investment]
- Weighted average Snippet SUMPRODUCT. =SUMPRODUCT([Values], [Weights])/SUM([Weights])
- Median Snippet Middle value. =MEDIAN([Range])
- Standard deviation Snippet Spread. =STDEV.S([Range])
- Random number Snippet Between two values. =RANDBETWEEN([Low], [High])
- Running total Snippet Cumulative sum. =SUM($[Column]$2:[Column]2)
- Tax amount Snippet Price times rate. =[Price]*[Tax rate]
- Margin Snippet Gross margin percent. =([Price]-[Cost])/[Price]
Sheets tricks
Google Sheets specials.
- QUERY group by Snippet SQL-like query. =QUERY([Range], "select A, sum(B) where C = '[Value]' group by A", 1)
- ARRAYFORMULA Snippet Apply to a whole column. =ARRAYFORMULA(IF([Range]="", "", [Formula]))
- GOOGLETRANSLATE Snippet Translate text. =GOOGLETRANSLATE([Cell], "auto", "[Language code]")
- IMAGE Snippet Show an image. =IMAGE("[Image URL]")
- SPARKLINE Snippet Tiny chart. =SPARKLINE([Range])
- GOOGLEFINANCE Snippet Stock price. =GOOGLEFINANCE("[Ticker]", "price")
- REGEXMATCH Snippet Pattern test. =REGEXMATCH([Cell], "[Pattern]")
- REGEXEXTRACT Snippet Pull out text. =REGEXEXTRACT([Cell], "[Pattern]")
- HYPERLINK Snippet Clickable link. =HYPERLINK("[URL]", "[Label]")
- Checkbox count Snippet Ticked boxes. =COUNTIF([Range], TRUE)