Essential formulas

SUM(number1, [number2], …)

Adds all numbers in one or more ranges. Text and empty cells are ignored.

Total revenue in B2 through B10

Use it for: Monthly sales total, total expenses.

AVERAGE(number1, [number2], …)

Returns the average of the numbers in a range. Empty cells and text are ignored, but cells that contain 0 are included.

Average revenue over 12 months

Use it for: Average monthly sales, average ticket price.

MIN(number1, [number2], …)

Returns the smallest value in a range.

Lowest monthly sales

Use it for: Lowest share price, weakest month.

MAX(number1, [number2], …)

Returns the largest value in a range.

Highest monthly revenue

Use it for: Peak revenue, highest expense.

COUNT(value1, [value2], …)

Counts cells that contain numbers. Dates are counted too, because Excel stores them as numbers. Text is not counted.

Number of numeric entries

Use it for: Number of transactions recorded this month.

COUNTA(value1, [value2], …)

Counts cells that are not empty, whatever the data type. Cells with a formula that returns empty text ("") are also counted.

Number of customer names in a list

Use it for: Total active customers, number of filled-in entries.

COUNTBLANK(range)

Counts empty cells in a range. Cells with a formula that returns empty text ("") are also counted as empty.

Number of values still missing

Use it for: Data quality check: how many entries are incomplete.

IF(logical_test, value_if_true, [value_if_false])

Tests one condition. Returns one value when the condition is true and another when it is false.

Label sales as high or low

Use it for: Pass or fail status, threshold checks.

IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

Tests several conditions in order and returns the value for the first one that is true. Easier to read than nested IF. Put TRUE as the last condition to set a default. Without it, the result is #N/A when no condition is true.

Grading system

Use it for: Tier classification, rating bands.

AND(logical1, [logical2], …)

Returns TRUE only when every condition is true.

High revenue and low cost

Use it for: Validation against several criteria at once.

OR(logical1, [logical2], …)

Returns TRUE when at least one condition is true.

VIP customer or large purchase

Use it for: Either-or business rules.

NOT(logical)

Reverses a logical value: TRUE becomes FALSE, and FALSE becomes TRUE.

Every status other than "Closed"

Use it for: Excluding a specific status.

IFERROR(value, value_if_error)

When a formula returns an error such as #N/A, #DIV/0! or #VALUE!, shows a replacement value instead.

Avoid #DIV/0! when the divisor is 0

Use it for: Keeping errors off a dashboard. Use it with care, because it also hides genuine mistakes in the formula.

IFNA(value, value_if_na)

Like IFERROR, but catches only #N/A, the usual error when a lookup finds no match. Other errors still show.

Handle a lookup that finds no match

Use it for: Cleaning up VLOOKUP results without hiding other errors.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks for a value in the first (leftmost) column of a table, then returns a value from another column in the same row.

Find a product price from its code

Use it for: Customer lookup, product price lookup. The default for range_lookup is TRUE (approximate match), so enter FALSE for an exact match.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Like VLOOKUP, but searches the first row of a table and returns a value from another row in the same column.

Find monthly data laid out horizontally

Use it for: Data arranged horizontally. In Excel 365, XLOOKUP can replace it.

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Replaces VLOOKUP and HLOOKUP. Uses an exact match by default, can return values from a column to the left, and accepts a fallback value when nothing matches.

Find a product price in the Products table

Use it for: The main lookup function in Excel 365 and Excel 2021 or later.

INDEX(array, MATCH(lookup_value, lookup_array, 0))

MATCH finds the position of a value in one column. INDEX returns the value at the same position in another column. The result column can sit to the left of the lookup column.

Find a name in column A from an ID in column C

Use it for: Excel versions without XLOOKUP (Excel 2019 and earlier).

SUMIF(range, criteria, [sum_range])

Adds the values that meet one criterion. If sum_range is omitted, the criteria range itself is added.

Total amount for the Sales category only

Use it for: Revenue by category or by month.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Adds the values that meet every criterion. The argument order differs from SUMIF: sum_range comes first.

Sales total for 2026 only

Use it for: The core dashboard formula: totals by month, category, and region.

COUNTIF(range, criteria)

Counts the cells that meet one criterion.

Number of transactions above 1,000

Use it for: Count of high-value customers, count of overdue invoices.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Counts the rows that meet every criterion. Each criteria range must be the same size.

Customers in KL who spent more than 5,000

Use it for: Customer segment analysis.

AVERAGEIF(range, criteria, [average_range])

Returns the average of the values that meet one criterion.

Average spend of VIP customers

Use it for: Average revenue per segment.

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Returns the average of the values that meet every criterion. As with SUMIFS, average_range comes first.

Average for Q1 2026

Use it for: Quarterly benchmarks.

CONCATENATE(text1, [text2], …) / text1 & text2

Joins text. The & operator is shorter. CONCATENATE still works in Excel 365, but CONCAT and TEXTJOIN replace it.

Join first name and last name with one space

Use it for: Full names, custom IDs.

LEFT(text, [num_chars])

Returns the first characters from the left of a text value. If num_chars is omitted, one character is returned.

First three characters of a code

Use it for: Extracting a country code or prefix.

RIGHT(text, [num_chars])

Returns the last characters from the right of a text value. If num_chars is omitted, one character is returned.

Last four digits of a phone number

Use it for: Last four digits, file name extensions.

MID(text, start_num, num_chars)

Returns a given number of characters from the middle of a text value, starting at the position you specify.

Characters 4 through 6

Use it for: Splitting national ID numbers or other structured codes.

LEN(text)

Returns the number of characters in a text value, including spaces.

Length of the text in A2

Use it for: Checking the length of ID or phone numbers.

TRIM(text)

Removes spaces at the start and end of text, and reduces repeated spaces inside it to a single space. Nonbreaking spaces (CHAR(160)) copied from web pages are not removed.

Clean up imported data

Use it for: Clean CSV data before VLOOKUP or XLOOKUP, because hidden spaces make lookups fail.

UPPER(text)

Converts all letters to uppercase.

ALI BABA

Use it for: Standardizing codes and IDs.

LOWER(text)

Converts all letters to lowercase.

ali baba

Use it for: Standardizing email addresses.

PROPER(text)

Capitalizes the first letter of each word and converts the remaining letters to lowercase.

Ali Baba

Use it for: Formatting customer names.

TODAY()

Returns the current date. The value updates every time the worksheet recalculates, including when the file opens.

Current date

Use it for: Last-updated date on a dashboard, age calculations.

NOW()

Returns the current date and time. The value updates every time the worksheet recalculates.

12/05/2026 14:30 (display follows your regional settings)

Use it for: Report timestamps.

DATE(year, month, day)

Builds a date from three numbers: year, month, and day.

May 12, 2026

Use it for: Building a date from user input or from separate columns.

YEAR(serial_number)

Returns the year of a date.

2026

Use it for: Grouping data by year.

MONTH(serial_number)

Returns the month of a date, from 1 to 12.

5 for May

Use it for: Grouping data by month.

DAY(serial_number)

Returns the day of the month of a date, from 1 to 31.

12

Use it for: Daily analysis.

EOMONTH(start_date, months)

Returns the last day of the month a given number of months before or after a start date. 0 is the same month, 1 the next month, and -1 the previous month. The result is a serial number, so format the cell as a date.

Last day of the current month

Use it for: Month-end reporting dates.

DATEDIF(start_date, end_date, unit)

Calculates the difference between two dates in years ("Y"), months ("M"), or days ("D"). Excel does not list this function in formula AutoComplete, so type the full name. The start date must be earlier than the end date. Otherwise, the result is #NUM!.

Age in years

Use it for: Length of service, age, days outstanding.

PMT(rate, nper, pv, [fv], [type])

Calculates the periodic payment for a loan with a fixed interest rate. For monthly payments, divide the annual rate by 12 and give nper in months. The result is negative because it is money paid out.

A 50,000 loan at 5% a year over 36 months: about -1,498.54 a month

Use it for: Car loans, mortgage planning.

FV(rate, nper, pmt, [pv], [type])

Calculates the future value of regular savings or investments at a fixed rate. Enter the payment as a negative number, because it is money paid out.

Saving 500 a month for 20 years at 6% a year: about 231,020.45

Use it for: Retirement planning, savings goals.

PV(rate, nper, pmt, [fv], [type])

Calculates the present value of a series of future payments, that is, what money received later is worth today.

Value today of 1,000 a month for 5 years at 5% a year: about 52,990.71

Use it for: Investment valuation, comparing a lump sum with installments.

NPV(rate, value1, [value2], …)

Calculates the net present value of cash flows that occur at the end of each period. The first value is discounted too, so subtract the initial investment (period 0) outside the function.

Invest 10,000 today and receive 3,000, 4,500, and 6,000 over the next three years: about 954.17

Use it for: Capital budgeting decisions. A positive NPV means the project returns more than the discount rate.