Excel how-to guides

Straight answers to the Excel questions people search most, with formulas checked against a real spreadsheet engine.

• How to remove duplicates in ExcelSelect your data, go to Data > Remove Duplicates, tick the columns that must match, and click OK.
• 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 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 remove spaces in ExcelUse =TRIM(A2) to remove leading and trailing spaces and shrink double spaces between words to one.
• 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).
• 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 capitalize the first letter in ExcelTo capitalize the first letter of every word, use =PROPER(A2).
• How to remove characters from a cell in ExcelTo remove a specific character, use =SUBSTITUTE(A2,"-","") to replace it with nothing.
• How to concatenate with a space in ExcelPut a space in quotes between the cells: =A2&" "&B2.
• VLOOKUP vs XLOOKUPXLOOKUP is the better function if everyone using the file has Excel 365 or 2021 and later.
• 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.
• How to return multiple matches in ExcelUse =FILTER(B2:B9,A2:A9="Sales").
• 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 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 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 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 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,"*").
• How to make a running total in ExcelIn the first row type =SUM($B$2:B2) and fill it down.
• 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.
• 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.
• 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)).
• If a cell contains text, return a value in ExcelUse =IF(ISNUMBER(SEARCH("wireless",A2)),"Yes","No").
• How to use IF with AND and OR in ExcelPut AND or OR in the first argument of IF.
• How to use IFERROR in ExcelWrap your formula like this: =IFERROR(your_formula, value_if_error).
• #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.
• #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.
• #DIV/0! error in Excel#DIV/0! appears when a formula divides by zero or by an empty cell.
• #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.
• #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.
• #SPILL! error in Excel#SPILL! means a formula that returns several values (such as FILTER, UNIQUE, SORT or SEQUENCE) has no empty space to spill into.
• Absolute vs relative cell references in ExcelA relative reference like B2 shifts when you copy a formula; an absolute reference like $B$2 always points to the same cell.
• 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.
• 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.
• 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.