Formulas & functions
How a function works, with worked examples you can paste.
- Replace a Circular Reference With an Excel FormulaYes—replace a circular reference by rewriting the calculation so it uses input cells or earlier results, rather than referring back to the…
- Power Query Group By: Concatenate Text by GroupYes. Power Query’s Group By dialog does not offer a direct “concatenate text” operation, but you can group rows and combine each…
- Excel Data Validation Custom Formula: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells to control, choose Data > Data Validation, set Allow to Custom, and…
- Lock a Named Range in an Excel Formula?Usually, you do not need to add dollar signs to lock a named range in an Excel formula: a name such as…
- Hide a Column If a Cell Is Blank in ExcelYes—but Excel has no built-in setting that automatically hides a column when a cell becomes blank. In Excel for Microsoft 365 on…
- Isnumber in C++: Standard Function or Platform Extension?No: C++ has no standard function named isnumber. To test whether one character is a decimal digit, use std::isdigit from <cctype>; to…
- Power Query Fuzzy Match: How It Works and How to Use ItYes. Power Query can fuzzy-match text when you merge queries, helping connect values that are similar but not identical, such as “Northwind…
- XLOOKUP to Compare Two Sheets in Excel Step by StepIf Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, enter this in Sheet1 cell B2 to check whether each…
- ISERROR with VLOOKUP: How to Handle Lookup ErrorsYes. In Excel, ISERROR can test a VLOOKUP result and report whether it returns an error; wrap the test in IF if…
- Flash Fill in Excel: Is There a Formula or Shortcut?Flash Fill in Excel is not a formula: it detects a pattern in your example and fills matching values into nearby cells.…
- IFERROR With IF in Excel: Syntax, Examples, and TipsTo use IFERROR with IF in Excel, put the IF function inside IFERROR: =IFERROR(IF(B2=0,"No quantity",A2/B2),"Check values"). IF makes the decision, while IFERROR…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a constant interest rate and…
- Conditional Formatting Formula If Statement in ExcelIn Excel, a conditional-formatting formula must evaluate to TRUE or FALSE for the cell or row you want to format. You can…
- Conditional Formatting Custom Formula: Excel RulesIn Excel for Microsoft 365 on Windows, a conditional formatting custom formula is a TRUE-or-FALSE formula that decides which cells receive a…
- COUNTA Function in Excel: Count Nonblank Cells CorrectlyTo count nonblank cells in Excel, use COUNTA. With five entries and two truly empty cells in A2:A8, enter =COUNTA(A2:A8); the result…
- Excel Data Table Cheat Sheet: What-If Analysis GuideAn Excel Data Table is a What-If Analysis tool that recalculates a formula for different values in one or two input cells.…
- Excel Conditional Formatting Based on a FormulaYes. In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule…
- Conditional Formatting If a Cell Is Blank in ExcelTo highlight blank cells in Excel for Microsoft 365 on Windows, select the cells, choose Home > Conditional Formatting > New Rule…
- PMT Function in SQL: Calculate Loan Payments by PeriodSQL does not have a universally available built-in PMT function. To calculate a fixed loan payment, use the PMT-style formula in a…
- XLOOKUP Multiple Criteria: Examples and Common FixesTo use XLOOKUP with multiple criteria in Excel, multiply the criteria tests and look for 1: =XLOOKUP(1,(A2:A100=H2)*(B2:B100=H3),C2:C100,"Not found"). This returns the value…
- Named Ranges in XLOOKUP: How to Use Them in ExcelTo use a named range in XLOOKUP, replace a cell reference such as A2:A6 with the range’s name in the formula. For…
- Excel OFFSET Function: How to Use It in Formulas and RangesIn Excel for Microsoft 365 on Windows, OFFSET returns a reference moved a chosen number of rows and columns from a starting…
- A1 Protect Data Sheet: Find the Right DocumentThere is no single product datasheet that can be reliably identified from the words “A1 Protect” alone; the name needs a manufacturer,…
- XLOOKUP Example With IFERROR: Excel Formula and TipsAn XLOOKUP formula wrapped in IFERROR can show a useful message instead of an Excel error: with the lookup value in E2,…
- SUMIFS Not Summing Correctly? Excel Causes and FixesSUMIFS usually returns an unexpected total because its ranges do not line up, a criterion does not match the underlying data, or…
- COUNTA and COUNTIF in Excel: How to Use Them TogetherTo find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five cells say Complete, three…
- SUMIF With Multiple Criteria: Examples and AlternativesFor multiple criteria in Excel, use SUMIFS rather than SUMIF. With dates in A2:A8, regions in B2:B8, products in D2:D8, and sales…
- IRR Function in Excel: Calculate an Investment ReturnUse Excel’s IRR function to estimate the rate of return for a series of cash flows that occur at regular intervals. Enter…
- XLOOKUP Example With SUM in ExcelWith product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)). If E2 contains Lamp…
- Excel IFERROR With IF: How to Handle Formula ErrorsIn Excel, use IFERROR to return a chosen result when a calculation produces an error, and use IF when the result should…