Excel for Financial Modelling: Formulas & Data Analysis

The core navigation shortcuts that let you move around a workbook without ever reaching for the mouse.

Using the keyboard to trigger any Ribbon command instantly, without memorising every individual shortcut.

Recognising the specific situations where the mouse is genuinely faster than a keyboard shortcut — and using it there.

A practical shortlist of the shortcuts you'll use daily as a financial modeller, ready to build into muscle memory.

The mechanics of writing your first Excel formula — operators, cell references, and the order Excel calculates in.

Dragging and filling a formula across a range correctly, and the pitfalls that trip people up when they first try it.

Locking rows and columns with $ signs so a formula copies correctly across a model instead of drifting.

Naming cells and ranges to make formulas readable and models easier for anyone to audit later.

Using exponents and the power operator for the calculations that come up constantly in financial modelling — growth rates, discounting, and more.

Formatting numbers so a model reads clearly — currency, percentages, decimals, and custom formats.

A faster way to apply and adjust number formats that will save you real time across a large model.

Using SUM correctly, including the shortcuts and selection tricks that speed up totalling large ranges.

Calculating averages with AVERAGE, and when you need AVERAGEIF instead.

Finding the smallest and largest values in a range with MIN and MAX — useful for sanity-checking model outputs.

The COUNT family of functions — COUNT, COUNTA, COUNTBLANK — and when each one is the right tool.

Excel's different paste options — values, formats, formulas — and how to use them to avoid breaking a model.

An introduction to how Excel stores and handles dates internally, and why that matters for every date formula you'll write.

Extracting the day, month, or year from a date with DAY, MONTH, and YEAR — and combining them back with DATE.

Using EOMONTH to find the last day of a month, a function that comes up constantly in period-end financial models.

Calculating a 360-day year with DAYS360, the convention used in many financial and loan calculations.

Writing a basic IF statement to return one of two outcomes based on a logical test.

Understanding the difference between IF's logical test and the comparison operators that build it, so your conditions actually test what you intend.

Nesting multiple IF statements to handle more than two possible outcomes — and when to reach for a cleaner alternative instead.

Using AVERAGEIF and COUNTIF to average or count values that meet a single condition.

Using SUMIF and MAXIFS to total or find the largest value among cells that meet a condition.

Looking up values across rows or columns with VLOOKUP and HLOOKUP, and the common mistakes that break them.

Combining INDEX and MATCH for a more flexible, robust lookup than VLOOKUP alone.

Using XLOOKUP, the modern replacement for VLOOKUP and INDEX/MATCH, with cleaner syntax and built-in error handling.

Using OFFSET to build dynamic ranges that resize automatically as your model changes.

Using SUBSTITUTE to replace specific text within a cell, a handy tool for cleaning up messy imported data.

Using LEN to count the number of characters in a cell — a building block for more advanced text formulas.

Extracting characters from the start or end of a text string with LEFT and RIGHT.

Pulling characters from the middle of a text string with MID, useful when parsing codes or IDs.

Rounding numbers up, down, or to the nearest value with ROUND, ROUNDUP, and ROUNDDOWN.

Understanding Excel's different data types and why mismatched types are a common source of formula errors.

Building your first Pivot Table from raw data — the setup steps that make everything else in this section possible.

Sorting and filtering data inside a Pivot Table to focus in on what matters.

Grouping Pivot Table data by date, range, or category to summarise at the right level of detail.

Changing how Pivot Table values are summarised — sum, count, average, and more — to analyse data from different angles.

Turning a Pivot Table into a Pivot Chart that updates automatically as the underlying data changes.

Wrapping up Pivot Tables with a recap of when and how to use them in a financial model.

Building your first chart from a data range — the fastest way to turn numbers into a visual.

Switching between Excel's chart types to find the one that best fits your data.

Customizing a chart's colors, labels, and layout so it communicates cleanly.

Advanced formatting techniques for charts that make them presentation-ready.

Building specialized chart types — waterfall, combo, and more — for data that a standard chart can't show well.

Making a chart dynamic so it updates automatically as your underlying data or model assumptions change.