Formulas & functions
How a function works, with worked examples you can paste.
- Excel MAX of Filtered Data: Formula, Limits, and FixesIf your numbers are in B2:B100, use =SUBTOTAL(4,B2:B100) to return the largest value among rows currently shown by an Excel filter. The…
- Excel Max Index: Row, Column, and INDEX Function LimitsIn current Microsoft Excel for Microsoft 365, the largest worksheet row index is 1,048,576, and the largest column index is 16,384, labeled…
- Excel MAX IF Formula: Find the Largest Matching ValueTo find the largest value in Excel that meets a condition, use MAXIFS. For example, with categories in A2:A8 and values in…
- Excel MAX Ignore Errors: Formula, Examples, and TipsTo find the largest value in Excel while ignoring error cells, use =AGGREGATE(4,6,A2:A10). In this formula, 4 selects MAX and 6 tells…
- Excel MIN With Criteria: Formulas, Examples, and FixesTo find the smallest value in Excel that meets a condition, use MINIFS: =MINIFS(B2:B10,A2:A10,"East"). It returns the minimum from B2:B10 only for…
- Excel Minutes Between Two Times: Formula and ExamplesIf the start time is in A2 and the end time is in B2, enter =(B2-A2)*1440 to calculate the minutes between them.…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring #N/A and other errors.…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN, and option 6 tells…
- Excel Min Date: Earliest Date and MIN FormulaIn Excel for Microsoft 365 on Windows, the earliest date Excel can store as a date in its standard 1900 date system…
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions in B2:B7 and values…
- Excel Percentile Function: Rank, Syntax, and ExamplesExcel has separate functions for percentiles and percentile ranks: use PERCENTILE.INC or PERCENTILE.EXC to find the value at a chosen percentile, and…
- Large Excel Cheat Sheet: Formulas, Shortcuts & TipsA large Excel cheat sheet is a quick reference for common formulas, keyboard shortcuts, data tools, formatting, and troubleshooting. For Excel for…
- Rank in Excel by Multiple Criteria: Formulas and TipsWith primary scores in B2:B5, secondary scores in C2:C5, and a third tie-breaker in D2:D5, enter this in E2 and fill down…
- Rank in Excel Ignoring Blanks: Formula and ExamplesYes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank cell blank when that…
- Rank in Excel Without Duplicates: Formula and TipsYes. To give every numeric value its own rank, including tied values, add a tie-break count to Excel’s ranking function. With scores…
- Excel Rank Formula for Scores: How to Rank ResultsTo rank scores in Microsoft Excel for Microsoft 365, use =RANK.EQ(B2,$B$2:$B$6,0) when the score to rank is in B2 and all scores…
- Excel Rank Formula: Rank a Number List Highest to LowestTo rank numbers from highest to lowest in Excel, use RANK.EQ with the order argument set to 0. With scores in B2:B6,…
- Excel FLOOR Formula for Time: Round Down by MinutesTo round an Excel time down to the nearest 15 minutes, use =FLOOR.MATH(A2,TIME(0,15,0)), assuming the time is in A2. The formula returns…
- Excel FLOOR Function: How to Round Decimal Values DownTo round a decimal down in Excel, use FLOOR.MATH with the increment you want. With a value of 3.87 in A2, =FLOOR.MATH(A2,1)…
- Excel Roundup a Sum: 10 Useful Formula PatternsTo round an Excel total up to the next whole number, put the amounts in B2:B5 and enter =ROUNDUP(SUM(B2:B5),0); for 12.4, 8.1,…
- Round Excel Numbers to the Nearest Ten With a FormulaTo round a number to the nearest ten in Excel, use the ROUND function with -1 as its second argument: with the…
- Round Excel to Significant Figures: Formulas and MethodsTo round a number in Excel to a chosen number of significant figures, put the value in A2 and the desired figure…
- How to Round Numbers in Excel to the Nearest ThousandIf the value is in A2, enter =ROUND(A2,-3) to round it to the nearest thousand; for example, 1,234,567 becomes 1,235,000. The negative…
- Round Excel to 2 Decimal Places: Formula or FormatTo round a number to two decimal places in Excel, use the ROUND function: with the value to round in A2, enter…
- Round Excel Numbers to the Nearest Integer with FormulasTo round a number to the nearest whole number in Excel, use the ROUND function with zero decimal places. If the value…
- Excel Days Until Formula: Calculate a CountdownTo calculate calendar days until a date in Excel, put the target date in A2 and enter =A2-TODAY() in B2. The result…
- Excel Auto Round Off? What Really Happens to ValuesExcel does not normally change a number just because it displays fewer decimal places: formatting rounds the appearance, while the stored value…
- Excel Rounding: Does It Always Round Up in Formulas?No. Excel does not always round up: it can round to the nearest value, round away from zero, round toward zero, or…
- Excel Days Until End of Month: Formula and ExamplesWith no input cells needed, enter =EOMONTH(TODAY(),0)-TODAY() to calculate the number of days from today to the last day of this month,…
- Excel Days Until Date: Formulas, Examples, and FixesTo calculate calendar days until a date in Excel, subtract today’s date from the target date: with the target date in A2,…