Free Excel Course Part 3 — 60 Formulas Explained

By Subham Ghadge  |  July 2026

Welcome to Part 3 of our complete Excel training series. Having covered shortcuts and formatting standards in Part 1, and intermediate functions in Part 2, this module provides an exhaustive reference guide to 60 essential Excel formulas used across financial modeling, accounting audit, data cleaning, and statistical reporting.

Clean text data extracted from database systems by converting letter casing to UPPERCASE, lowercase, or Proper Case (capitalizing the first letter of each word).

A financial analyst exports product descriptions (exported in ALL CAPS) and dealer locations (exported in all lowercase) from an ERP database. Before presenting to management, the analyst uses LOWER on product descriptions and PROPER on dealer locations for clean formatting.

Re-orient vertical column data into horizontal rows (or vice-versa) while maintaining live dynamic links to original cells.

Sum numbers in a target column based on a single condition matched in another column.

=SUMIF(range, criteria, [sum_range])

Example: Calculate total sales revenue generated exclusively by the California branch:

=SUMIF(Branch_Column, "California", Revenue_Column) → Output: $250,750

Count the number of rows that satisfy a specific condition.

=COUNTIF(range, criteria)

Example: Count total transactions logged for the California dealership branch:

=COUNTIF(Branch_Column, "California") → Output: 20 Transactions

Calculate the arithmetic average of numeric values matching a single condition.

=AVERAGEIF(range, criteria, [average_range])

Example: Compute average deal size for vehicle sales in California:

=AVERAGEIF(Branch_Column, "California", Revenue_Column) → Output: $12,538

Sum values in a range matching multiple criteria pairs simultaneously across different columns.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

Example: Calculate total Sedan sales in the Alabama branch:

=SUMIFS(Revenue_Range, Branch_Range, "Alabama", Product_Range, "Sedan") → Output: $41,651

Count rows that satisfy multiple conditions across multiple columns simultaneously.

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Calculate the average of cells matching multiple criteria pairs across different columns.

Multiplies corresponding components in two or more arrays and returns the sum of those products. Can evaluate complex array conditions without needing SUMIFS.

=SUMPRODUCT(array1, [array2], [array3], ...)

Example: Calculate revenue directly from Price and Quantity columns for a specific branch and product:

=SUMPRODUCT((Branch_Range="Alabama") * (Product_Range="Quarter panel") * (Price_Range * Qty_Range))

Returns the value of a cell at a specific row and column intersection within a table array.

=INDEX(array, row_num, [column_num])

Searches for a specified item in a range of cells and returns the relative row or column position of that item.

=MATCH(lookup_value, lookup_array, [match_type])

Combines SUMIF with INDEX and MATCH to dynamically sum column ranges based on user-selected column headers.

Compare flexible 2-way INDEX(MATCH) lookups against DGET database query functions for financial reporting models.

Concatenates text strings from multiple cells or arrays using a defined delimiter while optionally skipping blank cells.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Example: Combine Brand, Year, and Model ID into a clean Receipt ID:

=TEXTJOIN("-", TRUE, "Volkswagen", 2026, "CA143") → Output: "Volkswagen-2026-CA143"

Extract lowest value (MIN), highest value (MAX), or middle value (MEDIAN) from a numerical dataset.

Determine minimum or maximum values matching specific single or multi-criteria conditions.

Evaluates a logical test and returns one value if TRUE and another if FALSE.

=IF(Bid_Price > Floor_Price, "Accept Offer", "Reject Bid")

Evaluates multiple logical tests; returns TRUE only if ALL conditions are met.

Evaluates multiple logical tests; returns TRUE if ANY condition is met.

Returns the absolute value of a number, stripping negative signs for trial balance reporting.

Rounds numbers to specified decimal places or nearest 10, 100, 1000 bounds.

Calculates the Net Present Value of an investment using a discount rate and a series of future cash flows.

=NPV(discount_rate, cash_flow_range) + initial_outflow

Calculates the Internal Rate of Return for a series of periodic (IRR) or non-periodic scheduled (XIRR) cash flows.

Comparative decision framework for evaluating capital budgeting proposals using NPV and IRR metrics.

Calculates the periodic payment for a loan based on constant interest rates and constant payment terms.

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

Calculates the principal payment portion for a given period of an amortizing loan.

Build a full loan amortization schedule separating interest charges from principal reduction over time.

Calculates the interest payment portion for a given period of an investment or loan.

Calculates the effective annual interest rate given the nominal rate and number of compounding periods per year.

Calculates the depreciation of an asset for a specified period using the fixed-declining balance method.

Returns the interest rate per period of an annuity or loan schedule.

Calculates the present value of a loan or investment based on constant future payments.

Calculates the future value of an investment based on constant periodic payments and interest rate.

Returns the slope of the linear regression line through data points in known y's and known x's.

Searches horizontally across the top row of a table array and returns a value in the same column from a specified row.

Searches vertically down the first column of a table array and returns a value in the same row from a specified column.

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

Returns a reference to a range that is a specified number of rows and columns from a starting cell reference.

=OFFSET(reference, rows, cols, [height], [width])

Extracts a specified number of characters from the start (LEFT) or end (RIGHT) of a text string.

Returns the column number of a specified cell reference.

Returns the row number of a specified cell reference.

Returns the total count of columns contained within a target cell range.

Returns the total count of rows contained within a target cell range.

Returns the formula text string stored inside a cell reference as readable text.

Counts empty cells within a specified range.

Returns the minimum value from a database column matching specified criteria.

Returns the maximum value from a database column matching specified criteria.

Rounds a number to a specified number of decimals and formats it as text with or without commas.

Returns the starting position of one text string within another (case-sensitive).

Generates an evenly distributed random real number greater than or equal to 0 and less than 1.

Generates a random integer between specified lower and upper bounds.

Removes all leading, trailing, and double spacing from a text string.

Returns the rank position of a number in a list of numbers relative to other values.

Selects and returns a value from a list of value arguments based on an index number.

Calculates the correlation coefficient between two cell ranges.

Calculates or predicts a future value along a linear trend using existing historical values.

Calculates how often values occur within a range of values, returning a vertical array of numbers.

Returns TRUE if a cell contains a valid number; returns FALSE otherwise.

Convert column letters (like "AB") to column numbers dynamically using COLUMN(INDIRECT(letter & "1")).

Extract clean numeric digits from mixed text-number strings using array parsing formulas.

Sum the N smallest values in a range using =SUM(SMALL(range, {1,2,3})) array formulas.

---

Download Practice Workbook — Free

Download the complete 60-formula practice workbook containing exercise tabs, formula syntax guides, and working corporate datasets.