Formulas & functions
How a function works, with worked examples you can paste.
- How to Lock Cells in Excel Formulas With the F4 ShortcutTo lock a cell reference in an Excel formula, edit the formula, select the reference, and press F4. Excel adds dollar signs…
- Insert a Row in Excel and Keep FormulasTo insert a row in Excel and keep formulas, select the row heading where the new row should go, then choose Home…
- Excel Conditional Formatting Formula Examples That WorkExcel conditional formatting formulas let you highlight cells or entire rows when a logical test is true. In Microsoft 365 for Windows,…
- Clear Excel Spreadsheet but Keep Formulas in PlaceTo clear entered values in Excel while keeping formulas, select the cells to clean, choose Home > Find & Select > Go…
- UPPER Excel Formula: Capitalize Text in Excel CellsTo make every letter in text uppercase in Excel, use the UPPER function. If the original text is in A2, enter =UPPER(A2)…
- How to Clear an Excel Sheet and Keep All FormulasYes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Find & Select > Go To Special…
- Text After an Excel Formula: Add Labels and Sheet NamesTo put text after an Excel formula, join the formula result to a quoted text string with an ampersand (&). With values…
- Excel NPV With Different Rates: Formula, Steps, ExampleExcel’s NPV function accepts one discount rate, so it cannot directly calculate net present value when the rate changes from period to…
- Split Text in Excel Without a Formula: Easy MethodsYes. In Excel for Microsoft 365 on Windows, split text without a formula using Data > Text to Columns, Flash Fill, or…
- Excel PV Formula for Different PaymentsExcel’s PV function is designed for a fixed payment repeated over a set number of periods, so it cannot directly value a…
- How to Sort an Excel PivotTable Largest to SmallestTo sort an Excel PivotTable from largest to smallest, right-click a number in the value column you want to rank, then choose…
- INDIRECT Excel Formula for a Different Sheet: ExamplesIf cell A1 contains a worksheet name and you want the value in B2 on that sheet, use =INDIRECT("'"&A1&"'!B2"). For example, if…
- Excel FILTER Formula for Multiple CriteriaTo filter rows in Excel by multiple criteria, use FILTER with a condition for each test: multiply the conditions for AND logic,…
- Excel SUM Formula That Changes With FiltersUse =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates the result using the…
- Excel Rank Formula Without Duplicate Ranks: ExamplesTo give tied values different, consecutive ranks in Excel, enter =RANK.EQ(B2,$B$2:$B$10,0)+COUNTIF($B$2:B2,B2)-1 beside the first value and fill down. This ranks the largest…
- Excel MIN and MAX in the Same Formula: Examples and TipsYes. In Excel for Microsoft 365 on Windows, you can calculate both endpoints in one formula, such as =MIN(A2:A5)&" to "&MAX(A2:A5), which…
- FLOOR Excel Formula vs. ROUNDDOWN: Key DifferencesIn Excel, FLOOR rounds a number down to a chosen multiple, while ROUNDDOWN cuts off digits at a chosen decimal place and…
- Excel Formula to Round to the Nearest Whole NumberTo round a number in Excel to the nearest whole number, use the ROUND function with zero decimal places. If the value…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function. For example, if A2…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without entering a formula: select…
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains a real Excel date;…
- Excel Day of Week Formula: Return a Name or NumberIf the date is in cell A2, use =WEEKDAY(A2,2) to return its weekday number, with Monday as 1 and Sunday as 7.…
- Excel Day and Date Formulas: Show Days, Dates, and NamesIn Excel, use =DAY(A2) to return the day of the month from a date in A2, or =TEXT(A2,"dddd") to show its weekday…
- Excel Day Count Formula: Calculate Days Between DatesTo count calendar days between two dates in Excel, subtract the earlier date from the later one: with a start date in…
- Excel Formula: Days Between Two Dates and Today’s DateTo calculate days between two dates in Excel, subtract the earlier date from the later one: with a start date in A2…
- Today’s Date in Excel: Shortcut and TODAY() FormulaIn Excel for Microsoft 365 on Windows, press Ctrl+; (Control plus semicolon) to insert today’s date as a fixed value in the…
- Excel Date Calculation Methods and FormulasIn Excel, calculate dates by adding or subtracting dates and day counts, or use functions such as DATE, EDATE, WORKDAY, and NETWORKDAYS…
- Excel LEN, FIND, LEFT & RIGHT: Text Extraction FormulasTo split text in Excel, combine LEN and FIND with LEFT or RIGHT: use =LEFT(A2,FIND(" ",A2)-1) to return the text before the…
- Excel Formula Bar Not Showing? How to Restore ItIf the formula bar is missing in Excel for Microsoft 365 on Windows, open the View tab and, in the Show group,…
- Excel HLOOKUP: Return a Column NumberHLOOKUP does not return the number of the column where it finds a match; it returns a value from a row you…