Formulas & functions
How a function works, with worked examples you can paste.
- Excel Yes or No Formula: How to Return Either AnswerYes. To show “Yes” when a test is true and “No” when it is false, put the value to check in A2…
- Excel Formula for Years of Service From Hire DateTo calculate completed years of service from a hire date in Excel, use =DATEDIF(A2,TODAY(),"Y"), assuming the hire date is in A2. The…
- Excel Wildcard Text Search: Formulas and Examples GuideTo check whether text in A2 contains the search term in E2, use =ISNUMBER(SEARCH(E2,A2)). It returns TRUE when the term is found…
- Excel VLOOKUP Yes or No: How to Return a Match ResultYes. In Excel for Microsoft 365, you can make VLOOKUP return “Yes” or “No” by wrapping an exact-match lookup in a formula…
- Excel Formula to Uppercase the First Letter in CellsTo uppercase only the first character in an Excel cell while leaving the rest unchanged, use =UPPER(LEFT(A2,1))&MID(A2,2,LEN(A2)). If the original text is…
- Excel Formula: Check If a Value Is Between Two NumbersIf the value is in A2, the lower limit in B2, and the upper limit in C2, use =AND(A2>=B2,A2<=C2) to check whether…
- Excel Formula to Count Cells With Text in Any RangeTo count cells containing text in Excel, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. The asterisk is a wildcard that matches text…
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text and the comma with…
- Excel Formula to Round Up to the Nearest Thousand in ExcelTo round a number in cell A2 up to the next thousand in Excel, use =ROUNDUP(A2,-3). The negative 3 tells Excel to…
- Excel Formula for Quarter From Date: 3 Easy MethodsIf the date is in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from 1 to 4. To display…
- Excel Proper Case Formula: How PROPER Works in CellsIn Excel for Microsoft 365, use =PROPER(A2) to convert the text in A2 to proper case: the first letter of each word…
- Excel Formula for Percentage Increase: Examples and TipsTo calculate percentage increase in Excel, subtract the old value from the new value, then divide by the old value. With the…
- Excel Formula or Operator? Understand the DifferenceIn Excel, a formula is an expression that calculates a result, while an operator is a symbol that tells Excel what action…
- Excel Formula Opposite of CONCATENATE: Split Text ApartThe closest Excel formula to the opposite of CONCATENATE is =TEXTSPLIT(A2,"|"): it takes text in A2 and splits it wherever it finds…
- Excel Formula: Capitalize Only the First Letter in TextTo capitalize the first character in a cell and make the remaining characters lowercase, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))), assuming the text is in A2.…
- Excel Formula Line Break: How to Add One in a CellTo put a line break in an Excel formula, join the text with CHAR(10), then turn on Wrap Text for the cell.…
- Excel Formula Cell Reference Lock: Absolute ReferencesTo lock a cell reference in an Excel formula, add dollar signs before the column letter, row number, or both: $A$1 locks…
- Excel Formula Showing Instead of Result? Causes and FixesIf Excel is showing a formula instead of its result, first check whether Show Formulas is turned on; if it is not,…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date to test in A2…
- Excel Formula to Join Two Text CellsTo join two text cells in Excel, enter =A2&B2 in another cell, replacing A2 and B2 with the cells you want to…
- Excel Formula If Cell Contains Value: Practical ExamplesTo return one result when a cell is not empty and another when it is empty, use =IF(A2<>"","Yes","No"). With the value to…
- Excel Formula to Highlight a Row If a Cell ContainsWith the search term in E2, the value to check in column A, and rows 2–100 to format across A:D, use =AND($E$2<>"",ISNUMBER(SEARCH($E$2,$A2)))…
- Excel Formula to Highlight Every Other Row AutomaticallyTo highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule, choose “Use a formula…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare are in cells B2…
- Excel Formula Greyed Out? Causes and FixesIf Excel’s formula commands are greyed out, first press Esc to leave cell-edit mode, then select a worksheet cell and check whether…
- Excel Greater Than or Equal To: Formula and ExamplesIn Excel, the greater-than-or-equal-to operator is >=. Put it between two values in a formula, such as =A1>=B1, and Excel returns TRUE…
- Excel Greater Than and Less Than Formulas ExplainedIn Excel, use > to test whether a value is greater than another and < to test whether it is less. If…
- Excel Formula for the Greater of Two CellsTo return the larger of two cell values in Microsoft Excel, use =MAX(A2,B2). This example assumes the two values are in A2…
- Excel Formula for the Tab Name: Get the Worksheet NameYes. In Excel for Microsoft 365 on Windows, enter =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) in a cell on the worksheet whose name you want; it returns…
- Excel EOMONTH Formula: Find the Last Day of a MonthUse Excel’s EOMONTH function to return the last day of a month: enter =EOMONTH(start_date, months), replacing the arguments with a date and…