Formulas & functions
How a function works, with worked examples you can paste.
- IFERROR to Return Zero in Excel: Formula and UsesTo make an Excel formula return zero when it produces an error, wrap it in IFERROR. For example, with revenue in A2…
- Structured References in Excel Formulas: A Practical GuideStructured references are Excel table references that use a table’s name and column headings instead of ordinary cell addresses. For example, a…
- How to Add IFERROR in Excel to Handle Formula ErrorsTo add IFERROR in Excel, wrap your existing formula inside IFERROR and specify what to show if that formula returns an error.…
- COUNTIFS Does Not Contain: Excel Formula and ExamplesTo count cells that do not contain a particular text string in Excel, use a COUNTIFS criterion with an inequality sign and…
- COUNTIF Not Including Zero? Excel Formula ExamplesIn Excel for Microsoft 365, use =COUNTIF(A2:A20,"<>0") to count cells whose values are not zero, assuming your data is in A2:A20. If…
- XLOOKUP Partial Match: Wildcard Examples in ExcelFor a contains-style partial match, put the text to find in E2 and use =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2). The final 2 enables wildcard matching,…
- Transpose Formula Not Spreading? Excel FixesIf an Excel TRANSPOSE formula is not spreading, check that you entered it in a version with dynamic arrays, that the destination…
- IFERROR in Excel: How to Catch and Handle Formula ErrorsTo use IFERROR in Excel, wrap a calculation or formula in IFERROR and choose what to show if it returns an error.…
- Remove Spaces After Text in Excel Cells with TRIMTo remove only ordinary spaces at the end of text while preserving spaces inside words, put this formula beside the original value…
- Unique Excel List Creation: Formulas, Tools, and TipsTo create a unique list in Microsoft Excel for Microsoft 365 on Windows, enter =UNIQUE(A2:A100) in an empty cell, replacing the range…
- Excel Right-Click Shortcut: Open the Context MenuIn Excel for Microsoft 365 on Windows, select a cell or range and press Shift+F10 to open its right-click context menu without…
- Excel Filter Drop-Down Shortcut: Open a Column MenuIn Excel for Microsoft 365 on Windows, select a cell in a filtered column and press Alt+Down Arrow to open that column’s…
- XLOOKUP Tutorial: Examples, Match Modes, and TipsIn Excel for Microsoft 365 on Windows, XLOOKUP searches one range and returns the corresponding value from another: with product IDs in…
- XLOOKUP Syntax Examples for ExcelFor an exact lookup, with the value to find in E2, lookup keys in A2:A100, and results in B2:B100, use =XLOOKUP(E2,A2:A100,B2:B100). XLOOKUP…
- XLOOKUP Practice Examples for Everyday Excel TasksFor an exact-match lookup, put the value to find in one cell, then use XLOOKUP to search a lookup range and return…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas tab; if that does…
- XLOOKUP Method Examples: Formulas That WorkXLOOKUP finds a value in one range and returns the corresponding value from another. For example, with product IDs in A2:A6, product…
- XLOOKUP Match Mode Examples: Exact and ApproximateXLOOKUP’s match_mode argument controls what counts as a match: use 0 for an exact match, -1 for an exact match or the…
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For an exact match, put…
- XLOOKUP Wildcards: Examples, Rules, and Common FixesWith region names in A2:A6 and matching IDs in B2:B6, use =XLOOKUP("North*",A2:A6,B2:B6,"Not found",2) to return 101, the ID on the first matching…
- XLOOKUP Examples for Beginners: Formulas, Tips, and FixesXLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product codes in A2:A5, names…
- XLOOKUP Examples With Arrays in ExcelWith product names in A2:A6, prices in B2:B6, and a name to find in D2, use =XLOOKUP(D2,A2:A6,B2:B6,"Not found"). XLOOKUP searches the lookup…
- XLOOKUP Entry Examples: Formulas for Common SearchesXLOOKUP entry examples show how to find a value in one range and return its matching value from another. In Microsoft Excel,…
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If E2 contains SKU-102, this…
- XLOOKUP Advanced Examples for Real-World Excel TasksXLOOKUP advanced examples include returning several columns at once, matching on multiple conditions, finding the last occurrence, and using approximate or wildcard…
- XLOOKUP Calculation Examples: Formulas That Work in ExcelXLOOKUP can find a matching value and return a related value, or calculate a result from the matched row. For example, use…
- SUMIFS Won’t Work: Causes and FixesSUMIFS usually fails because its ranges do not line up, its criteria do not match the values in the data, or the…
- When VLOOKUP Does Not Work: Common Causes and FixesVLOOKUP usually fails because its lookup value does not exactly match the first column of the selected range, the range or column…
- VLOOKUP vs XLOOKUP: Examples and Key DifferencesVLOOKUP and XLOOKUP both find a value in a range and return related information, but XLOOKUP is generally more flexible and defaults…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from a column to its…