Formulas & functions
How a function works, with worked examples you can paste.
- Excel Table of Contents: Create a Clickable Sheet IndexExcel does not have a dedicated command that automatically creates a table of contents for every worksheet. The practical solution is to…
- Excel Table Name: Find, Change, and Use Names in FormulasAn Excel table name is the workbook-level identifier assigned to a formatted Excel table, such as Table1 or Sales. To find or…
- Excel Table Header Formula: What Works and What Doesn’tExcel table headers are column labels, not the place to put a formula you expect to calculate or update like a normal…
- Excel Table Drag Formula: Lock Cell ReferencesTo keep a cell reference fixed when you fill a formula down an Excel table, add dollar signs to its column and…
- Excel Table Array Lookup: Using VLOOKUP in a TableAn Excel table array lookup usually means using the table_array argument in VLOOKUP: it tells Excel which range contains both the value…
- Excel Row Index: Find and Use Worksheet Row NumbersAn Excel row index is a row’s position: on a worksheet, it is the number shown at the left edge, from 1…
- Excel Column Width to Match Row Height?Excel has no built-in setting that makes a column’s width equal a row’s height: column width is measured in character-based units, while…
- Excel Column Total Formula: SUM, AutoSum, and TipsTo total a column in Excel, use =SUM(A2:A100) for a specific range, or =SUM(A:A) for the entire column. Put the result in…
- Excel Column Subtraction Formula: Examples and TipsTo subtract values in one Excel column from the matching values in another, enter =A2-B2 in the first result cell, then fill…
- Excel Column Minus Column Formula: Examples and FixesIf your values are in A2:A100 and B2:B100, enter =A2-B2 in C2 to subtract the value in column B from the value…
- Excel Column Match Formula: Find Values and PositionsIf the value to find is in E2 and the column to search is A2:A100, use =MATCH(E2,$A$2:$A$100,0). MATCH returns the value’s position…
- Excel COLUMN Function: Return a Column Letter from a NumberExcel’s COLUMN function returns a column number, not a letter. To display a column letter, combine it with ADDRESS and SUBSTITUTE: with…
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to total. If your values…
- Excel Cell Protection Options: Lock Cells the Right WayExcel cell protection has three main parts: mark cells as Locked or Hidden, then use Protect Sheet to enforce those settings and…
- Excel Cell Contains String? Find Text With a FormulaTo check whether a cell contains a particular string of text, use =ISNUMBER(SEARCH("apple",A2)). With the word apple anywhere in A2, Excel returns…
- Excel AND Function: Why It Does Not Return an ArrayExcel’s AND function returns one value, either TRUE or FALSE; it does not return a separate result for each row in an…
- Excel AND and OR Functions Combined: How to Use ThemYes. You can combine Excel’s AND and OR functions by nesting one inside the other. For example, with a score in B2,…
- Excel AND in MATCH: Find Rows With Multiple CriteriaTo use AND conditions with Excel MATCH, compare each criteria range with its requested value and multiply the resulting TRUE/FALSE arrays. With…
- Excel AND Function in an IF Statement: How It WorksYes. In Excel, put AND inside IF when the result should depend on several conditions being true at once. With a pass…
- Excel UPPER Function: How to Make Text All CapsExcel’s uppercase text function is UPPER. In Microsoft Excel for Microsoft 365, enter =UPPER(A2) to convert the text in cell A2 to…
- Excel Text Unique Values: Extract, Count, and CompareYes. In Excel for Microsoft 365, use UNIQUE to return a list of distinct text values from a range; for example, with…
- Excel Text Join With Line Breaks: Formulas and TipsTo join text with a line break in Excel for Microsoft 365, use TEXTJOIN with CHAR(10) as the delimiter. With values in…
- Excel Text Handling: Formulas, Formatting, and FixesExcel text handling means entering, cleaning, combining, splitting, and displaying text without accidentally changing its meaning. In Microsoft Excel for Microsoft 365…
- Excel Test If a String Contains Text: Formula ExamplesTo test whether a cell contains a particular string in Excel, use =ISNUMBER(SEARCH("apple",A2)), replacing “apple” with the text to find and A2…
- Excel Test If Two Cells Are Equal: Formulas and FixesTo test whether two cells are equal in Excel, enter =A1=B1 in another cell, replacing A1 and B1 with the cells you…
- Excel Test If a Cell Is Empty: Formulas and ExamplesTo test whether a cell is genuinely empty in Excel, use =ISBLANK(A1), replacing A1 with the cell you want to check. If…
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when the text appears anywhere…
- Excel Test Cell Value: Formulas for Common ChecksTo test whether a cell contains a particular value in Excel, use an IF formula such as =IF(A2="Paid","Match","No match"). Replace A2 with…
- Excel Function to Extract Part of a String: ExamplesExcel has several functions for extracting part of a string: use LEFT for characters at the start, RIGHT for characters at the…
- Excel Function to Remove Text from a StringExcel does not have one universal “remove text” function: use SUBSTITUTE to remove specified characters or words, REPLACE to remove text at…