Formulas & functions
How a function works, with worked examples you can paste.
- Excel Round Up to Dollar: 4 Easy WaysTo round a number up to the nearest whole dollar in Excel, use the ROUNDUP function with 0 decimal places: =ROUNDUP(A1,0). For…
- Excel Round Up to Next Dollar: 4 Easy WaysTo round a number up to the next whole dollar in Excel, use the ROUNDUP function with 0 decimal places, like =ROUNDUP(A1,0).…
- Excel Round to Nearest Quarter: Formula & StepsTo round a number to the nearest quarter (0.25) in Excel, use the formula =MROUND(A1, 0.25). This rounds up or down to…
- Excel Round to Nearest Quarter Dollar: 3 Easy WaysTo round a price to the nearest quarter dollar in Excel, use =MROUND(A1, 0.25) for standard rounding, =CEILING(A1, 0.25) to always round…
- Excel Round to Nearest X: The Complete Formula GuideTo round a number to the nearest specified multiple in Excel, use the MROUND function: =MROUND(number, multiple). For example, =MROUND(17, 5) returns…
- Excel Round to Dollar: 5 Formulas That WorkTo round a number to the nearest dollar in Excel, use the =ROUND(cell, 0) formula. For example, =ROUND(A1, 0) turns 12.50 into…
- Excel Reverse Lookup: Index Match ExplainedThe direct answer: to perform a reverse lookup in Excel (find a value in a left column based on a value in…
- Excel Return Index of Match? Use MATCH or XMATCHTo return the index (position) of a match in Excel, use the MATCH function for most cases, or XMATCH if you have…
- Excel Reference Range in Formula: How to Use ItIn Excel, a reference range in a formula is a group of cells you point to so the formula can use those…
- Excel Rank Formula with Tie BreakerThe direct answer: use =RANK(B2,$B$2:$B$10)+COUNTIF($B$2:B2,B2)-1 to break ties by giving the first occurrence a higher rank. This adds a small increment based…
- Excel RANK Function with Ties: How to Handle ThemThe Excel RANK function assigns a rank to a number within a list, and when two values are identical, they receive the…
- Excel Rank No Ties? Use RANK.EQ and COUNTIFYes, Excel’s RANK.EQ function assigns the same rank to tied values by default, so two identical scores both get the same rank…
- Excel Power Query Trim Text: How to Clean DataYes, Power Query has a dedicated Trim function that removes leading and trailing spaces from text, and you can apply it from…
- Excel Power Query Split Function: How to Use ItThe Excel Power Query split function is the Split Column command in the Power Query Editor, which divides a single text column…
- Excel Percentage of Cells with Text: 2 FormulasThe quickest way to find the percentage of cells containing text in Excel is to use =COUNTIF(range,"*")/COUNTA(range) for non-empty cells, or =COUNTIF(range,"*")/ROWS(range)…
- Excel Partial Match Lookup: 4 Formulas That WorkThe quickest way to do a partial match lookup in Excel is to use XLOOKUP with wildcards: =XLOOKUP("*"&D2&"*",A2:A10,B2:B10,,2). For older versions, use…
- Excel OFFSET Function Dynamic Range ExplainedThe Excel OFFSET function creates a dynamic range by returning a reference to a cell or range that is a specified number…
- Excel Only Show Cells with Data? Filter & Hide BlanksNo, Excel does not automatically show only cells with data; you must use filtering, Go To Special, or hiding methods to exclude…
- Excel Not Calculating? Fixes for FormulasIf your Excel formulas are not calculating, the most common cause is that calculation is set to Manual, and the fix is…
- Excel Offset Formula Dynamic RangeThe Excel OFFSET formula creates a dynamic range by returning a reference to a range that is a specified number of rows…
- Excel Multiply If Not Zero: Formula & GuideTo multiply only when a cell is not zero in Excel, use the IF function: =IF(A2=0,0,A2*B2) or, if you want a blank…
- Excel Nested INDEX MATCH: How to Use ItTo create a nested INDEX MATCH in Excel, you use one INDEX MATCH formula as the lookup value inside another INDEX MATCH…
- Excel Months Left in Year: Formula & GuideTo calculate the months left in the year from a date in Excel, use the formula =DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"m") — this returns the number…
- Excel Mortgage Formula PMT: How to Use ItThe Excel PMT function calculates the periodic payment for a loan based on a constant interest rate and a constant payment schedule.…
- Excel Monthly Loan Payment Formula: PMT ExplainedThe Excel formula for a monthly loan payment is =PMT(rate/12, nper*12, pv), where rate is the annual interest rate, nper is the…
- Excel Month to Date Average FormulaThe simplest Excel formula for a month-to-date average is =AVERAGEIFS(values, dates, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), dates, "="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "="&DATE(YEAR(D1),MONTH(D1),1), A2:A100, "="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), A2:A100, "="&DATE(YEAR(TODAY()),1,1), A2:A100, "="&DATE(YEAR(D1),MONTH(D1),1),…
- Excel Min Date with Criteria: Formula & ExamplesThe fastest way to find the minimum date in Excel that meets a condition is to use the MINIFS function. For example,…
- Excel MIN IF Multiple Criteria: How to Find the Smallest MatchThe direct answer: Excel has no single MINIFS function, but you can combine MIN with the IF function as an array formula,…
- Excel Min Date Formula: Find Earliest DateThe simplest Excel formula to find the minimum (earliest) date in a range is =MIN(A2:A100), assuming your dates are in cells A2…
- Excel SUBTOTAL Formula: How to Use It CorrectlyThe SUBTOTAL formula in Excel is =SUBTOTAL(function_num, ref1, [ref2], ...). For example, =SUBTOTAL(9, A2:A10) returns the sum of values in A2:A10, ignoring…