Excel how-to guides
48 straight answers to the Excel questions people search most. Every formula result is calculated by a real spreadsheet engine, not typed by hand.
No guides match “”. Try a single word such as duplicates, date or error, or browse all functions and all guides.
SUM and friends
- #NAME? error in Excel#NAME? means Excel does not recognize something in your formula, usually a misspelled function name (=SUMM), text without quotes, or a range missing its colon.
- Excel formulas cheat sheet: the 25 everyone should knowThese 25 formulas cover most everyday Excel work: SUM, AVERAGE, MIN/MAX, COUNT, COUNTA and ROUND for math; IF, AND, OR, IFS and IFERROR for logic; COUNTIF, COUNTIFS, SUMIF and SUMIFS for conditional totals; XLOOKUP, VLOOKUP and INDEX/MATCH for lookups; TRIM, LEFT/RIGHT/MID and TEXTJOIN for text; and TODAY, DATEDIF, UNIQUE and FILTER for dates and lists.
Cell references
Logic with IF
- Excel IF statement with multiple conditionsPut the conditions inside AND or OR, then use that as the IF test.
- How to write a nested IF in ExcelA nested IF puts a second IF in the "value if false" slot of the first: =IF(B2>=100,0,IF(B2>=50,4.99,9.99)).
- How to use IF with AND and OR in ExcelPut AND or OR in the first argument of IF.
- Excel formula for letter gradesFor a standard 90/80/70/60 scale use =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F") in Excel 2019 or later.
Conditional math
- How to sum if a cell contains text in ExcelUse SUMIF with asterisk wildcards around the text: =SUMIF(A2:A8,"*blue*",B2:B8).
- How to use COUNTIF greater than in ExcelPut the operator and number together in quotes: =COUNTIF(B2:B9,">80") counts cells above 80.
- How to count cells with text in ExcelUse =COUNTIF(A2:A10,"*").
- COUNTIFS with multiple criteria in ExcelCOUNTIFS takes pairs of range and criteria and counts the rows where every pair matches: =COUNTIFS(B2:B9,"Open",C2:C9,"High") counts open, high-priority tickets.
- SUMIFS with multiple criteria in ExcelSUMIFS puts the range to add first, then pairs of criteria range and criteria: =SUMIFS(D2:D9,A2:A9,"East",B2:B9,"Laptop") adds the amounts where the region is East and the product is Laptop.
Working with text
- How to combine first and last name in ExcelType =A2&" "&B2 where A2 holds the first name and B2 the last name, then fill the formula down.
- How to remove spaces in ExcelUse =TRIM(A2) to remove leading and trailing spaces and shrink double spaces between words to one.
- How to capitalize the first letter in ExcelTo capitalize the first letter of every word, use =PROPER(A2).
- How to concatenate with a space in ExcelPut a space in quotes between the cells: =A2&" "&B2.
Lookups
- How to use INDEX MATCH in ExcelWrite =INDEX(return_range,MATCH(lookup_value,lookup_range,0)).
- XLOOKUP with multiple criteriaUse =XLOOKUP(1,(A2:A9="North")*(B2:B9="Laptop"),C2:C9).
- VLOOKUP vs XLOOKUPXLOOKUP is the better function if everyone using the file has Excel 365 or 2021 and later.
- How to return multiple matches in ExcelUse =FILTER(B2:B9,A2:A9="Sales").
- #N/A error in Excel: causes and fixes#N/A means "not available": a lookup such as VLOOKUP, XLOOKUP or MATCH could not find the value you asked for.
- #REF! error in Excel#REF! means a formula points to a cell that is not valid: it was deleted, or the formula asks for a column or row outside its range.
Clean results
- How to round to the nearest 5, 10 or 100 in ExcelFor 10 or 100 use ROUND with a negative digit count: =ROUND(A2,-1) rounds to the nearest 10 and =ROUND(A2,-2) to the nearest 100.
- How to use IFERROR in ExcelWrap your formula like this: =IFERROR(your_formula, value_if_error).
- #DIV/0! error in Excel#DIV/0! appears when a formula divides by zero or by an empty cell.
Dates and time
- How to calculate age in Excel from date of birthUse =DATEDIF(B2,TODAY(),"y"), where B2 holds the date of birth.
- How to calculate days between two dates in ExcelSubtract the earlier date from the later one: =C2-B2.
- How to add months to a date in ExcelUse =EDATE(A2,3) to add 3 months to the date in A2.
- How to count working days between two dates in ExcelUse =NETWORKDAYS(B2,C2).
- How to calculate months between two dates in ExcelUse =DATEDIF(B2,C2,"m") to count complete months between a start date in B2 and an end date in C2.
Data cleanup
- How to remove duplicates in ExcelSelect your data, go to Data > Remove Duplicates, tick the columns that must match, and click OK.
- How to separate first and last name in ExcelSelect the name column, go to Data > Text to Columns, choose Delimited, tick Space, and click Finish.
- How to count unique values in ExcelIn Excel 365 or 2021, use =COUNTA(UNIQUE(A2:A10)).
- How to find duplicates in ExcelSelect the column, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and click OK.
- How to compare two columns in ExcelTo compare row by row, use =IF(A2=B2,"Match","Different") and fill it down.
- How to extract text before a character in ExcelIn Excel 365, use =TEXTBEFORE(A2,"@") to return everything before the first @.
- How to remove characters from a cell in ExcelTo remove a specific character, use =SUBSTITUTE(A2,"-","") to replace it with nothing.
- How to extract numbers from a string in ExcelIf the number always sits next to the same character, cut it out and convert it: =VALUE(TEXTAFTER(A2,"-")) turns "INV-2041" into 2041.
- If a cell contains text, return a value in ExcelUse =IF(ISNUMBER(SEARCH("wireless",A2)),"Yes","No").
- #VALUE! error in Excel: causes and fixes#VALUE! means a formula got the wrong kind of data, most often text where it expected a number, such as "4 units" or "n/a" in a math formula.
Business math
- How to calculate percentage in ExcelDivide the part by the whole, for example =B2/C2, then format the cell as a percentage with Home > Number > Percent Style (Ctrl+Shift+%).
- How to calculate percent change in ExcelSubtract the old value from the new value and divide by the old value: =(C2-B2)/B2.
- How to calculate percentage of total in ExcelDivide each value by the total and lock the total with dollar signs: =B2/SUM($B$2:$B$7).
- How to calculate a weighted average in ExcelMultiply each value by its weight, add them up, and divide by the total weight: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5).
- How to make a running total in ExcelIn the first row type =SUM($B$2:B2) and fill it down.
- How to calculate a loan payment in ExcelUse =PMT(rate/12, years*12, -amount).
- How to calculate compound interest in ExcelUse =P*(1+r/n)^(n*t), for example =B1*(1+B2/B4)^(B4*B3), where P is the starting amount, r the annual rate, n the compounds per year and t the years.