Formulas & functions
How a function works, with worked examples you can paste.
- Excel UPPER Function: Convert Text to UppercaseIn Excel, use the UPPER function to convert text to uppercase: with the text to change in cell A2, enter =UPPER(A2) in…
- Excel Count If Not Blank: Formulas and Common TrapsTo count cells that are not blank in Excel, use =COUNTA(A2:A100), replacing the range with your data. If you need a condition…
- Excel Count Nonblank Cells: Formulas and ExamplesTo count nonblank cells in Excel, use =COUNTA(A2:A20), replacing the range with the cells you want to check. COUNTA counts cells containing…
- COUNTIF in Excel: How to Count Matching CellsIn Excel, use COUNTIF to count cells that meet one condition. With statuses in A2:A10 and four cells containing Paid, enter =COUNTIF(A2:A10,"Paid");…
- Aggregate Excel Columns with Formulas and PivotTablesTo aggregate Excel columns, use a function such as SUM, AVERAGE, COUNT, MIN, or MAX across the cells you want to summarize.…
- Excel SUBTOTAL COUNTIF: Count Matching Visible RowsExcel has no built-in function that combines SUBTOTAL and COUNTIF: SUBTOTAL can ignore filtered-out rows, but it cannot apply COUNTIF’s condition. To…
- Excel SUBTOTAL for Columns: Formulas, Filters, and TipsTo subtotal a column in Excel, enter a formula such as =SUBTOTAL(9,B2:B20) in a cell below the values; it adds the numbers…
- Text Split in Excel: Methods, Formulas, and Common FixesTo split text in Excel, select the cells and choose Data > Text to Columns to separate values with a delimiter such…
- Excel Growth Rate Formula: Calculate Change Over TimeTo calculate growth rate in Excel, divide the change between the new and old values by the old value. If the old…
- Excel Exchange Rate Formula: Convert Currencies EasilyFor a basic Excel exchange rate formula, multiply the amount by the rate when the rate tells you how many units of…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of return, not a limit…
- Excel IRR Monthly: Formula, Setup, and Common ErrorsTo calculate a monthly internal rate of return in Excel, put cash flows in chronological order at equal monthly intervals, including the…
- Excel IRR for Monthly Cash Flows: Formula and ExamplesFor equally spaced monthly cash flows in B2:B14, enter =IRR(B2:B14) to calculate the rate per month; the first value is month zero…
- Excel NPV Formula Year by Year: How It WorksIn Excel, use =NPV(rate, year_1_cash_flow:year_n_cash_flow)+year_0_cash_flow to calculate net present value when cash flows occur at the end of each year. Excel’s NPV…
- Excel NPV of a Series of Cash Flows: Formula and ExamplesTo calculate the net present value of a cash-flow series in Excel, use =NPV(B1,B3:B5)+B2 when the discount rate is in B1, the…
- Excel IF OR Formula: Combine Multiple Conditions EasilyTo use Excel’s IF with OR, put OR in IF’s logical-test position: Excel checks whether at least one condition is true, then…
- PV of Annuity Formula in Excel: Examples and TimingWith the annual interest rate in A2, the number of annual payments in B2, and each end-of-year payment entered as a negative…
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then add any cash flow…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic payments: =PV(rate, nper, 0,…
- Excel PV Factor Formula: Calculate Present ValueIn Excel, calculate a single-payment present value factor by dividing 1 by 1 plus the discount rate raised to the number of…
- Excel PV vs. NPV: Formulas, Examples, and DifferencesIn Excel, use PV to find the value today of a fixed series of equal payments, and use NPV to discount a…
- Excel FV Compound Interest: Formula, Examples, and TipsIn Excel, use the FV function to calculate a value in the future after interest compounds: =FV(rate,nper,pmt,[pv],[type]). For example, an initial $1,000…
- Excel PMT Function for Loans: Formula and ExamplesExcel’s PMT function calculates the regular payment for a loan with a constant interest rate and equal payments. For a monthly loan,…
- Excel Payment Schedule: Build One With FormulasTo build a basic loan payment schedule in Excel, enter the annual interest rate, number of monthly payments, and loan amount, then…
- Excel Payment Tracker: Setup, Formulas, and Practical TipsAn Excel payment tracker is a worksheet for recording what is owed, what has been paid, when payments are due, and which…
- Excel Payment Calculator: Calculate Loan Payments With PMTTo make an Excel payment calculator for a fixed-rate loan, use PMT: with the loan amount in B1, annual interest rate in…
- Rows in Excel Nederlands: Meaning and the RIJEN FunctionIn Dutch, Excel rows are called rijen; the English Excel function ROWS is named RIJEN in Dutch-language Excel. It counts how many…
- Switch Excel Formulas to Values: Methods and TipsTo switch formulas to fixed values in Excel for Microsoft 365 on Windows, select the formula cells, press Ctrl+C, then press Ctrl+Alt+V,…
- Excel LAMBDA Use Cases: Practical Examples and LimitsExcel’s LAMBDA function lets you turn a calculation into a reusable custom function, so you can give a formula a meaningful name…
- Excel ETA LAMBDA: Build a Reusable Arrival-Time FormulaExcel has no built-in function named ETA, but you can calculate an estimated arrival time with a normal time formula or create…