Free Excel Course Part 3: 60 Essential Formulas for Financial Modeling & Auditing

Welcome to Part 3 of our complete Excel curriculum. After mastering navigation shortcuts in Part 1 and intermediate functions like Goal Seek and Data Validation in Part 2, this module provides an exhaustive reference guide to sixty essential Excel formulas. These formulas form the backbone of corporate reporting, accounting audits, statistical evaluation and financial modeling.

Whether you need to clean unformatted text from database extracts, calculate amortized loan schedules, run two-way dynamic lookups or evaluate multi-criteria business metrics, this reference delivers exact syntax rules, practical corporate examples and clear operational guidance.

Master Formula Categories Overview

Our curriculum organizes sixty core formulas into six functional categories. Use this master directory to jump directly to any category section across the guide.

Category 1: Text & Array Formulas

Text formulas clean messy data exports from ERP and CRM platforms, ensuring text strings match standard presentation formats across reports.

01: UPPER, LOWER, PROPER

Clean raw database text by converting letter casing to uppercase, lowercase or proper case (capitalizing the initial letter of every word).

Corporate Scenario: An analyst exports raw vehicle inventory descriptions recorded in mixed casing. The analyst applies LOWER to normalize product types and PROPER to standardize regional branch locations:

02: TRANSPOSE

Re-orients vertical columns into horizontal rows (or horizontal rows into vertical columns) while maintaining active formula links to source data.

=TRANSPOSE(array)

Category 2: Math & Aggregation Formulas

Conditional math functions aggregate numbers matching specific single or multi-column criteria without requiring manual filtering.

03: SUMIF

Sums numeric values in a target column based on a single condition matched against another column.

=SUMIF(range, criteria, [sum_range])
=SUMIF(Branch_Column, "California", Revenue_Column)

Calculation Result: Returns total revenue generated exclusively by the California branch ($250,750).

04: COUNTIF

Counts the number of rows meeting a specific condition.

=COUNTIF(range, criteria)
=COUNTIF(Branch_Column, "California")

Calculation Result: Returns total count of transactions logged for California (20 transactions).

05: AVERAGEIF

Calculates the arithmetic mean of values that satisfy a single condition.

=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIF(Branch_Column, "California", Revenue_Column)

Calculation Result: Computes average deal value for vehicle sales in California ($12,538).

06: SUMIFS

Sums values matching multiple conditions simultaneously across distinct columns.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=SUMIFS(Revenue_Range, Branch_Range, "Alabama", Product_Range, "Sedan")
Calculation Output: $41,651

07: COUNTIFS

Counts rows that satisfy multiple conditions across multiple columns at the same time.

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
=COUNTIFS(Region_Range, "North", Status_Range, "Delivered")

08: AVERAGEIFS

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

=AVERAGEIFS(avg_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=AVERAGEIFS(Margin_Range, Region_Range, "East", Tier_Range, "Enterprise")

09: SUMPRODUCT

Multiplies corresponding components in two or more arrays and returns the sum of those products. It evaluates complex array conditions without requiring helper columns.

=SUMPRODUCT(array1, [array2], [array3], ...)
=SUMPRODUCT((Branch_Range="Alabama") * (Product_Range="Quarter panel")
  * (Price_Range * Qty_Range))

Category 3: Lookup & Reference Formulas

Lookup formulas retrieve data records across disparate tables, establishing relationships between dimension tables and transactional ledgers.

10: INDEX

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

=INDEX(array, row_num, [column_num])
=INDEX(Product_Catalog, 14, 3)

11: MATCH

Searches for a specific value in a range and returns its relative row or column position number.

=MATCH(lookup_value, lookup_array, [match_type])
=MATCH("SKU-104", Product_ID_Range, 0)

12: SUMIF with INDEX and MATCH

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

=SUMIF(Category_Range, "Electronics",
  INDEX(Sales_Table, 0, MATCH(Selected_Month, Month_Headers, 0)))

13: INDEX MATCH vs DGET

While INDEX(MATCH) delivers flexible two-way lookups across irregular grids, the database function DGET extracts single records from structured databases matching strict criteria blocks.

=DGET(Database_Range, "Total_Cost", Criteria_Block)

14: TEXTJOIN

Concatenates strings from multiple cells using a specified delimiter while ignoring blank entries.

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
=TEXTJOIN("-", TRUE, "Volkswagen", 2026, "CA143")
Calculation Output: "Volkswagen-2026-CA143"

Category 4: Statistical & Logical Formulas

Statistical and logical formulas apply business decision rules, evaluate numeric thresholds and enforce mathematical rounding standards.

15: MIN, MAX, MEDIAN

Extract the smallest value (MIN), largest value (MAX) or middle statistical distribution value (MEDIAN) from a numeric dataset.

=MIN(Salary_Range)
=MAX(Salary_Range)
=MEDIAN(Salary_Range)

16: MINIFS, MAXIFS

Determine minimum or maximum numeric values matching single or multiple criteria conditions.

=MINIFS(Cost_Range, Branch_Range, "North", Category_Range, "Hardware")
=MAXIFS(Margin_Range, Region_Range, "South", Rep_Range, "Active")

17: IF

Evaluates a logical condition and returns one value if true and another if false.

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

18: IF with AND

Evaluates multiple logical conditions, returning true only if every test evaluates to true.

=IF(AND(Sales_Volume > 1000, Customer_Score >= 9), "Tier 1 Bonus", "Standard Rate")

19: IF with OR

Evaluates multiple logical conditions, returning true if any single test evaluates to true.

=IF(OR(Defect_Rate > 0.05, Audit_Score < 80), "Escalate to QA", "Pass Audit")

20: ABS

Returns the absolute value of a number, stripping negative signs for balance sheet variances.

=ABS(Actual_Spend - Budgeted_Spend)

21: ROUND, ROUNDUP, ROUNDDOWN

Controls decimal precision across financial schedules.

=ROUND(124.567, 2)     → Result: 124.57
=ROUNDUP(124.01, 1)    → Result: 124.10
=ROUNDDOWN(124.99, 0)  → Result: 124.00

Category 5: Financial & Investment Formulas

Financial formulas evaluate capital investment opportunities, compute loan amortization schedules and calculate asset depreciation.

22: NPV

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

=NPV(discount_rate, cash_flow_range) + initial_cash_outflow

23: IRR & XIRR

Calculates the Internal Rate of Return for periodic (IRR) or date-specific cash flows (XIRR).

=IRR(cash_flow_range)
=XIRR(cash_flow_values, cash_flow_dates)

24: NPV vs IRR Comparative Framework

In capital budgeting, NPV measures the absolute dollar value added by an investment, while IRR reflects the percentage rate of return. Projects with positive NPV and an IRR exceeding the corporate hurdle rate warrant capital approval.

25: PMT

Calculates the constant periodic payment required to service a loan based on fixed interest rates and terms.

=PMT(rate, nper, pv, [fv], [type])
=PMT(7.5%/12, 60, -250000)

26: PPMT

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

=PPMT(rate, period, nper, pv, [fv], [type])
=PPMT(7.5%/12, 1, 60, -250000)

27: PMT, IPMT & PPMT Integration

In loan amortization schedules, the total periodic payment equals the principal payment plus the interest charge: PMT = PPMT + IPMT.

28: IPMT

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

=IPMT(rate, period, nper, pv, [fv], [type])
=IPMT(7.5%/12, 1, 60, -250000)

29: EFFECT

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

=EFFECT(nominal_rate, npery)
=EFFECT(12%, 12)  → Result: 12.68%

30: DB

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

=DB(cost, salvage, life, period, [month])
=DB(50000, 5000, 5, 1)

31: RATE

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

=RATE(nper, pmt, pv, [fv], [type], [guess])

32: PV

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

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

33: FV

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

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

Category 6: Advanced Lookups & Utility Formulas

Utility and matrix formulas inspect grid coordinates, parse character strings and automate dynamic reporting layouts.

34: SLOPE

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

=SLOPE(known_y_values, known_x_values)

35: HLOOKUP

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

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

36: VLOOKUP

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

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(Customer_ID, Master_Accounts, 4, FALSE)

37: OFFSET

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

=OFFSET(reference, rows, cols, [height], [width])
=OFFSET(A1, 5, 2, 10, 1)

38: LEFT & RIGHT

Extracts characters from the beginning (LEFT) or end (RIGHT) of a text string.

=LEFT("INV-98421", 3)   → Result: "INV"
=RIGHT("INV-98421", 5)  → Result: "98421"

39: COLUMN

Returns the numeric column coordinate of a specified cell reference.

=COLUMN(D12)  → Result: 4

40: ROW

Returns the numeric row coordinate of a specified cell reference.

=ROW(B15)  → Result: 15

41: COLUMNS

Counts the total number of columns contained within a selected range or table array.

=COLUMNS(A1:E20)  → Result: 5

42: ROWS

Counts the total number of rows contained within a selected range or table array.

=ROWS(A1:E20)  → Result: 20

43: FORMULATEXT

Returns the underlying formula string stored in a cell as readable text for auditing.

=FORMULATEXT(C10)  → Result: "=SUM(A10:B10)"

44: COUNTBLANK

Counts empty cells within a specified range to verify data entry completeness.

=COUNTBLANK(Registration_Form_Range)

45: DMIN

Returns the minimum value from a database column matching specific criteria blocks.

=DMIN(Database_Range, "Unit_Cost", Criteria_Block)

46: DMAX

Returns the maximum value from a database column matching specific criteria blocks.

=DMAX(Database_Range, "Unit_Cost", Criteria_Block)

47: FIXED

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

=FIXED(12345.678, 2, FALSE)  → Result: "12,345.68"

48: FIND

Locates the starting character position of a substring within text (case-sensitive).

=FIND("@", User_Email_Cell)

49: RAND

Generates an evenly distributed random real number between 0 and 1.

=RAND()

50: RANDBETWEEN

Generates a random integer between specified lower and upper bounds.

=RANDBETWEEN(1000, 9999)

51: TRIM

Removes all leading, trailing and duplicate internal spaces from text entries.

=TRIM(Raw_Customer_Name)

52: RANK

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

=RANK(Sales_Rep_Revenue, All_Reps_Revenue_Range, 0)

53: CHOOSE

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

=CHOOSE(2, "Q1", "Q2", "Q3", "Q4")  → Result: "Q2"

54: CORREL

Calculates the correlation coefficient between two numeric ranges.

=CORREL(Ad_Spend_Range, Sales_Volume_Range)

55: FORECAST

Calculates a future value along a linear trend using historical x and y data.

=FORECAST(Target_Period, Historical_Sales, Historical_Periods)

56: FREQUENCY

Calculates how often values occur within specific intervals, returning a vertical array.

=FREQUENCY(Scores_Array, Bins_Array)

57: ISNUMBER

Returns true if a cell contains a valid number; returns false otherwise.

=ISNUMBER(Entry_Cell)

58: Convert Column Letter to Number

Converts column letters (such as "AB") to column numbers dynamically using indirect evaluation.

=COLUMN(INDIRECT(Column_Letter_Cell & "1"))

59: Strip Non-Numeric Characters

Extracts numeric digits from mixed text-number strings using array parsing routines.

=TEXTJOIN("", TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * 1, ""))

60: Sum of Bottom N Values

Sums the N smallest values in a range using an array formula.

=SUM(SMALL(Data_Range, {1,2,3}))

Module C: Financial & Auditing Formula Pitfalls & Fixes

When implementing complex formulas across corporate workbooks, analysts encounter distinct calculation errors. Use this troubleshooting guide to diagnose root causes and apply fixes immediately.

Module H: Pro Formula Auditing Shortcuts & Best Practices

Formula auditing keyboard shortcuts accelerate model reviews and prevent circular reference mistakes. Memorize these standard Windows hotkeys to audit complex financial models efficiently.