Introduction

As a manager, your time is precious. You need to focus on high-impact tasks that drive business growth and success rather than wrestling with spreadsheets. Excel's powerful formulas and functions can save you hours each week - once you know the right ones and how to use them effectively.

This guide covers ten essential Excel formulas that every manager should have in their toolkit, plus three bonus sections for the counting and logical cases that come up constantly. Each formula is explained with its purpose, syntax, and practical use cases so you can start applying them immediately.

For a deeper treatment of the lookup functions (VLOOKUP, XLOOKUP, INDEX-MATCH) covered in formula 5, see our dedicated Excel lookup guide.

Formula 1: SUMIFS() - Summing Data with Multiple Criteria

What it does: Sums values in a range that meet multiple specified criteria.

Syntax:

=SUMIFS(Sum_Range, Criteria_Range1, Criteria1, Criteria_Range2, Criteria2)

Example: Summing total sales for a specific product in a specific region.

=SUMIFS(F2:F1000, A2:A1000, "Widget A", B2:B1000, "East")

SUMIFS is far more flexible than the older SUMIF because it handles multiple conditions simultaneously. Use it for sales reports, expense categorisation, or any scenario where you need to total values across intersecting dimensions.

Pro tip: criteria can reference cells, so =SUMIFS(F2:F1000, A2:A1000, A1, B2:B1000, B1) turns the formula into a dashboard that updates when you change the two criteria cells. Use wildcards for partial matches: "Widget*" sums every product starting with "Widget".

Common error: criteria ranges must be the same size as the sum range. Mismatched ranges return #VALUE!.

Formula 2: INDEX-MATCH() - Efficient Data Lookup

What it does: Looks up a value in a table by matching a key, returning a corresponding result from any column.

Syntax:

=INDEX(Return_Range, MATCH(Lookup_Value, Lookup_Range, Match_Type))

Example: Finding the price of a product by its ID.

=INDEX(C2:C1000, MATCH("PRD-045", A2:A1000, 0))

Unlike VLOOKUP, INDEX-MATCH can look left (return values from columns to the left of the lookup column) and is not broken when columns are inserted or deleted. It also handles larger datasets faster.

Pro tip: the same pair does two-way lookups. =INDEX(B2:M13, MATCH("East", A2:A13, 0), MATCH("Mar", B1:M1, 0)) finds the value at the intersection of a row and a column, the classic pattern for summary dashboards.

Formula 3: COUNTIFS() - Counting Data with Multiple Criteria

What it does: Counts the number of cells in a range that meet multiple criteria.

Syntax:

=COUNTIFS(Count_Range, Criteria_Range1, Criteria1, Criteria_Range2, Criteria2)

Example: Counting how many orders were placed by a specific customer in January.

=COUNTIFS(A2:A1000, "Customer ABC", B2:B1000, ">=01/01/2024", B2:B1000, "<=31/01/2024")

Managers use COUNTIFS for headcount reporting, inventory counts, compliance checks, and any situation where a simple tally across dimensions is needed.

Pro tip: COUNTIFS works with wildcards ("*Overdue*" counts any cell containing the word Overdue) and with cell references for dashboard criteria. The single-criteria version COUNTIF is the same idea with one condition and is the right tool when you only need one filter.

Formula 4: IF() - Conditional Logic

What it does: Returns one value if a condition is true, and another if it is false.

Syntax:

=IF(Logical_Test, Value_If_True, Value_If_False)

Example: Classifying performance ratings.

=IF(D2>=100000, "Above Target", "Below Target")

Nest multiple IF functions to handle tiered logic, or combine with AND() and OR() for complex conditions (see the bonus section below). The IF function is the building block for dynamic dashboards, automated alerts, and scenario modelling.

Pro tip: for more than two or three tiers, IFS() is cleaner than nested IFs:

=IFS(D2>=100000, "Top", D2>=50000, "Mid", D2>=0, "Low")

IFS evaluates left to right and returns the first true result, no nesting required.

Common error: text returned by IF must be quoted. =IF(A2>10, Yes, No) returns a #NAME? error; write "Yes" and "No".

Formula 5: VLOOKUP() - Quick Data Lookup

What it does: Searches for a value in the first column of a table and returns a corresponding value from another column.

Syntax:

=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])

Example: Retrieving an employee's department from their ID.

=VLOOKUP("E103", A2:D500, 3, FALSE)

Set the last argument to FALSE for exact matches (the most common use case). Note that the lookup column must be the leftmost column in your table range. For more flexibility, consider INDEX-MATCH or XLOOKUP (if you have a newer Excel version).

Pro tip: wrap VLOOKUP in IFERROR to handle missing values gracefully:

=IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not found")

And if you keep getting #N/A on values you can see, the lookup value almost certainly has leading or trailing spaces; clean both sides with TRIM.

Modern alternative: XLOOKUP replaces VLOOKUP in Excel 2021 and Microsoft 365, with no left-column restriction and a built-in not-found argument. See the full VLOOKUP vs XLOOKUP vs INDEX-MATCH guide for the migration table and worked examples.

Formula 6: PMT() - Calculating Loan Payments

What it does: Calculates the periodic payment for a loan based on constant payments and a constant interest rate.

Syntax:

=PMT(Rate/Periods_Per_Year, Total_Periods, Present_Value, [FV], [Type])

Example: Monthly payment on a $500,000 loan at 6% p.a. over 25 years.

=PMT(6%/12, 25*12, 500000)

This formula returns a negative value (representing an outgoing payment). Prepend with a minus sign to show it as a positive figure. PMT is invaluable for equipment financing, mortgage modelling, and lease-vs-buy analysis.

Pro tip: build the inputs into cells (loan amount, rate, term) and reference them, so the formula becomes a what-if tool. Change the rate cell and the payment recalculates instantly. For a ready-made version, our loan calculator does the same job interactively.

Formula 7: IPMT() - Calculating Interest Payments

What it does: Returns the interest portion of a loan payment for a given period.

Syntax:

=IPMT(Rate/Periods_Per_Year, Period, Total_Periods, Present_Value, [FV], [Type])

Example: Interest portion in the first month on a $300,000 loan at 5% over 20 years.

=IPMT(5%/12, 1, 20*12, 300000)

Use IPMT alongside PPMT to build an amortisation schedule that separates the interest from principal components - crucial for tax reporting and cash flow forecasting.

Pro tip: copy the formula down one row per period, incrementing the period argument (or referencing a period column), to generate a full amortisation schedule in seconds. Sum the interest column to get the total interest cost of the loan.

Formula 8: PPMT() - Calculating Principal Payments

What it does: Returns the principal portion of a loan payment for a given period.

Syntax:

=PPMT(Rate/Periods_Per_Year, Period, Total_Periods, Present_Value, [FV], [Type])

Example: Principal portion in month 12 of the same loan.

=PPMT(5%/12, 12, 20*12, 300000)

Combined, PMT, IPMT, and PPMT give you a complete picture of any amortising loan. They are essential for financial due diligence, investment property analysis, and debt structuring.

Pro tip: for any period, PMT = IPMT + PPMT. Use that relationship to check your schedule: if the payment does not equal the sum of the interest and principal columns, the schedule has an error.

Formula 9: EOMONTH() - Determining the Last Day of a Month

What it does: Returns the serial number of the last day of the month, a specified number of months before or after a given date.

Syntax:

=EOMONTH(Start_Date, Months_Offset)

Example: Last day of the month three months from today.

=EOMONTH(TODAY(), 3)

EOMONTH is indispensable for financial close processes, maturity date calculations, and any reporting that aligns to month-end periods. It handles leap years and varying month lengths automatically.

Pro tip: format the result as a date, then use it to build month-end flags: =IF(A2=EOMONTH(A2,0), "Month end", "") marks every month-end row in a transaction log, which is the basis for month-end cut-offs and accruals. EOMONTH(A2,0) plus one day gives the first of the following month.

Formula 10: TODAY() - Returning the Current Date

What it does: Returns the current date (updated each time the worksheet is recalculated).

Syntax:

=TODAY()

Example: Calculating the number of days until a deadline.

=B2 - TODAY()

TODAY() is the simplest yet most frequently used date function. Combine it with EOMONTH, DATE, and NETWORKDAYS to build dynamic date logic that always reflects the current date without manual updates.

Pro tip: TODAY() recalculates whenever the workbook recalculates, which is usually fine for dashboards but can be a problem in audit trails. If you need a fixed date stamp, use the keyboard shortcut Ctrl+; instead of the formula.

Bonus: COUNT, COUNTA and COUNTBLANK - Knowing Your Data

What they do: COUNT counts cells containing numbers, COUNTA counts cells that are not empty (numbers and text), and COUNTBLANK counts empty cells.

Syntax:

=COUNT(range)
=COUNTA(range)
=COUNTBLANK(range)

Example: checking how complete an imported customer list is.

=COUNTA(A2:A1000)      ' how many rows have any data
=COUNT(A2:A1000)       ' how many contain numbers (e.g. customer IDs)
=COUNTBLANK(A2:A1000)  ' how many are empty

Use the trio to sanity-check data before analysis: if COUNTA and COUNT disagree, some entries are text where numbers belong. If COUNTBLANK is high, the import has gaps. Pair them with COUNTIFS when you need criteria-based counts, which is the formula 3 pattern applied to data quality.

Bonus: AND, OR and NOT - Building Conditional Tests

What they do: AND returns TRUE only if all conditions are true. OR returns TRUE if any condition is true. NOT reverses a logical value.

Syntax:

=AND(condition1, condition2, ...)
=OR(condition1, condition2, ...)
=NOT(condition)

Example: flagging orders that are both large and overdue.

=IF(AND(D2>=10000, E2<TODAY()), "Escalate", "OK")

Example: flagging orders that are large OR urgent.

=IF(OR(D2>=10000, E2<TODAY()), "Review", "OK")

AND and OR rarely appear on their own; they earn their keep inside IF, turning one-cell conditions into full business rules. The classic pattern is =IF(AND(condition1, condition2), "Yes", "No") for "both must be true" logic, and =IF(OR(condition1, condition2), "Yes", "No") for "either can be true". Use NOT sparingly; =IF(NOT(A2="Closed"), ...) reads the same as =IF(A2<>"Closed", ...) and the second is easier for others to follow.

Putting These Formulas to Work

The real power of these formulas emerges when you combine them. For example:

Master these formulas and you will be well on your way to becoming an Excel master: someone who can make spreadsheets do the heavy lifting instead of the other way around. For the dashboard patterns that put them all together, explore our data analysis and insight hub.

Further Reading

• Excel Lookup Guide: VLOOKUP, XLOOKUP and INDEX-MATCH
• Excel Data Analysis for Business Decisions: Numbers to Insights
• Excel Keyboard Shortcuts Every Power User Should Know