Video summary

Excel for Finance and Accounting Full Course Tutorial (3+ Hours)

Main summary

Key takeaways

Educational

Main ideas, concepts, and lessons

Course purpose and roadmap (intro)

  • The instructor, Elyssa Smith, presents an end-to-end Excel training focused on finance and accounting workflows.
  • The course progresses from:
    • Navigation and core spreadsheet skills
    • Formatting and data handling
    • Excel formulas/functions (basic → advanced)
    • Financial modeling concepts (e.g., loan schedules, statement building)
    • Data visualization (charts, pivot tables) and analysis tools
    • Building full financial statements and a three-statement model

Part 1: Excel basics for finance/accounting

1) Excel navigation vocabulary and structure

  • Everything in Excel is stored in cells
    • Cells are identified by column letters and row numbers.
    • Selecting a cell lets you type into it.
  • Interface basics
    • Uses the ribbon system like other Microsoft Office apps.
  • Ranges
    • Multiple cells selected together are called a range.
    • Non-adjacent selections: select a group, then hold Ctrl to select additional groups.
  • Workbooks and sheets
    • A workbook contains sheets (organized tabs).
    • Add/organize data by splitting it across sheets.

2) Data entry methods + navigation shortcuts

Data entry

  • Click a cell and type (example described: entering in B2).
  • Press Enter to confirm.
  • Alternatively use the Formula Bar (located under the ribbon):
    • Typed text in the formula bar appears in the selected cell.

Fast navigation

  • Use the cell name box (near formula bar) to jump:
    • Type a cell reference like M500 → Enter.
  • Jump back to the top-left quickly:
    • PC: Ctrl + Home (or described as Ctrl home)
    • Mac: Command + Home (described as Command + Page Up/Home concept)

Practice

  • Encourages users to open practice files and practice typing and navigation.

3) Editing using rows and columns

Deleting

  • Click a column letter (e.g., F) → deletes whole column with Delete key.
  • Use Undo to restore.

Inserting columns

  • Right-click a column letter (e.g., G) → choose Insert.
  • Insert defaults to adding a new column to the left of the selected column.

Inserting rows

  • Highlight multiple rows (e.g., rows 7–11).
  • Right-click → Insert.
  • Insert adds new rows above the selected rows.

4) Printing, saving, opening files

Printing

  • Go to File ribbon → Print
  • Uses:
    • Print task pane (left)
    • Print preview (right)
  • Can choose:
    • Number of copies
    • Printer
    • Which workbook portion to print
    • Orientation and margins
  • Note:
    • Default prints the current sheet
  • Exit print preview via Back button
  • Page breaks may appear as dashed lines in the worksheet after printing.

Saving

  • Use File → Save (or Save As for new filename/location).
  • Saving behaves similarly to Word/PowerPoint save flows.

Opening

  • File → Open
  • Browse to file → double-click or select → Open
  • Switch among open Excel files using the Windows taskbar Excel icon.

5) Formatting content (color, alignment, borders)

Goal

  • Make data easier to read and interpret.

Approach

  • Select the target cells first.
  • Most tools are on Home ribbon.

Color

  • Font color: change text color (ensure readability)
  • Fill color: change cell background color (ensure readability)

Alignment

  • Adjust row height (drag between row headers) so content fits.
  • Horizontal alignment: left/center/right
  • Vertical alignment: top/middle/bottom
  • Text can be tilted using the alignment options (ensure cell/row size is sufficient).

Borders

  • Gridlines do not print by default.
  • Use Borders button to apply print-ready borders to selected cells.

6) Formatting numbers/dates + using Format Painter

Alignment by type

  • Numeric/date values: right-aligned
  • Text: left-aligned

Currency formatting

  • Select cells → Home → Number group → click the $ currency format button.
  • Adjust decimals carefully:
    • Decreasing decimals rounds values.

Date formatting

  • Use dropdown next to current date format to change short → long.
  • If Excel shows #####:
    • Double-click between column headers to auto-fit width.

Format Painter

  • Select a “formatted” source cell/range.
  • Click Format Painter.
  • Highlight the target cells to copy the formatting.
  • Double-click Format Painter to keep it active for multiple uses.

7) Conditional formatting + merge and center

Merge and center (title)

  • Select A1–G1 → Merge & Center button (Home ribbon, alignment group).

Conditional formatting

  • Highlight a range (e.g., expenses B7–G10)
  • Home → Conditional Formatting:
    • Highlight Cell Rules (criteria-based highlighting)
      • Example: values greater than 5000 → red text/fill.
    • Data Bars
      • Visual bar length represents relative value magnitude (e.g., green gradient).
  • Editing/clearing rules
    • Conditional Formatting → Manage Rules → Edit Rule
    • Clear Rules:
      • Prefer “clear rules from selected cells” to avoid wiping everything.

8) Navigating large worksheets: Split and Freeze Panes

Split

  • View ribbon → Window group → Split
  • Creates adjustable horizontal/vertical panes with independent scrolling.

Freeze Panes (recommended)

  • View ribbon → Freeze Panes (snowflake icon)
  • Options:
    • Freeze top row
    • Freeze first column
    • Freeze at a cell corner (e.g., freeze everything above/left of C2)
  • Unfreeze via Unfreeze Panes.

9) Speed techniques: Quick Access Toolbar + keyboard shortcuts

Quick Access Toolbar

  • Customize frequently used commands (print, spell check, undo/redo are usually included).
  • Add/remove commands from the dropdown.
  • Can be displayed above or below the ribbon.

Keyboard shortcuts covered

  • Ctrl + A: select entire sheet (all data)
  • Ctrl + Home: jump to A1
  • End key + Ctrl: jump to last entered cell (described as Ctrl+End behavior)
  • Select to last filled cell in a column:
    • Ctrl + Shift + Down Arrow (from a starting cell in a column)
  • Select to last filled cell in a row:
    • Ctrl + Shift + Right Arrow (from a starting cell in a row)

Part 1 formulas/functions (core to finance tasks)

1) Basic aggregate functions + AutoSum

  • Functions reviewed:
    • SUM (add range)
    • AVERAGE
    • MAX
    • MIN
    • COUNT (counts numeric items; also usable with text “count numbers” setting)
  • Method:
    • Use AutoSum (Σ) on the Formulas ribbon.
    • Correct the auto-selected range if needed.
    • Use fill handle (autofill) to copy formulas across.
  • Emphasized concept:
    • Relative references update when copied.

2) IF statement (logical test → true/false outputs)

Business rule example

  • Bonus is calculated only if sales total ≥ $7 million
    • If true: sales * 5%
    • If false: “no bonus”

IF structure

  • IF(logical_test, value_if_true, value_if_false)

Notes

  • Numbers must have correct zero count.
  • Text outputs must be in quotes.
  • Can copy the formula down for all reps.
  • Nested IFs are mentioned as a path to more complex logic (false branch may contain another IF).

3) SUMIF and AVERAGEIF (sum/average with criteria)

SUMIF

  • sum: totals in one column
  • criteria: destination equals a specific text value (e.g., “Cancun”)
  • Example structure:
    • SUMIF(criteria_range, “criteria_text”, sum_range)

AVERAGEIF

  • Similar but averages matching values (e.g., destination equals “Boston”)
  • Output formatting suggested to reduce excessive decimals.

4) Left, Right, Mid + Flash Fill

Text extraction

  • LEFT(cell, n): first N characters
  • RIGHT(cell, n): last N characters
  • MID(cell, start_num, num_chars): substring from middle
  • Copied down using fill handle.

Flash Fill

  • Insert a new column (e.g., first name)
  • Type one example and let Excel infer the pattern
  • Trigger Flash Fill:
    • Ctrl + E
    • or Data ribbon → Flash Fill (lightning bolt icon)

5) COUNTIF and COUNTIFS (count with one or multiple criteria)

COUNTIF

  • Count “yes” commission entries:
    • COUNTIF(range, “yes”)
  • Count “no commission” entries:
    • COUNTIF(range, “no”)

COUNTIFS

  • Count where multiple criteria are true:
    • e.g., commission is “yes” AND destination equals “St. Louis”
  • COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

Part 2: Advanced finance functions + modeling tools

1) Date/time functions

  • TODAY(): current date; updates when file is opened/recalculated
  • NOW(): current date + time; updates similarly
  • Keyboard shortcuts for static (non-updating) timestamps:
    • Ctrl + ; : current date (static)
    • Ctrl + Shift + ; : current time (static)

2) Data validation

Purpose

  • Ensure users enter correct values.

Rules covered

  • Add validation to blank or existing cells:
    • Existing contents may be grandfathered in.
  • Example validation:
    • Column H: allow whole numbers >= 15000
  • Validation setup includes tabs:
    • Settings: criteria type and threshold
    • Input Message: shows when user selects cell
    • Error Alert:
      • “Stop” style prevents saving invalid data

Clearing validation

  • Select validated cells → Data validation → Clear All.

3) Lookup functions: VLOOKUP, INDEX+MATCH, XLOOKUP

VLOOKUP

  • Purpose: vertical lookup using a leftmost key column
  • Arguments:
    • lookup_value
    • table_array
    • column_index_number (must be numeric, not letter)
    • range_lookup:
      • “false” forces exact match; omission triggers approximate match
  • Example described:
    • Enter customer number → return annual ticket cost from a table

INDEX + MATCH

  • Purpose: avoid left-to-right limitation of VLOOKUP
  • INDEX(range, row_num, col_num): returns value at a location
  • MATCH(lookup_value, list_range, match_type):
    • 0 for exact match
    • (+1/-1 variants discussed for approximate matching depending on sort order)
  • Combined:
    • MATCH finds position (replaces row/col inputs), INDEX returns from that location.

XLOOKUP

  • “Newer” and simpler:
    • lookup_value
    • lookup_array
    • return_array
    • optional: if_not_found, match_mode
  • Defaults to exact match, improving reliability vs VLOOKUP.
  • Can return multiple items (example returns both sales and location).

4) What-if analysis: Scenario Manager and Goal Seek

Scenario Manager

  • Choose a set of input cells (e.g., loan amount, term, interest rate).
  • Add multiple scenarios (e.g., first try / second try / third try).
  • Use Show to toggle scenarios.
  • Use Summary to generate:
    • scenario summary report (or pivot table option)

Goal Seek

  • Set a target value in one cell (e.g., monthly payment = 2000)
  • Change another cell (e.g., loan amount/borrowing)
  • Excel solves for the required input.

5) Financial functions for valuation and loans

PV and FV

  • PV: present value of loan/investment
  • FV: future value of investment
  • Both use interest rate and payment schedule
  • Notes:
    • Adjust rate/payment frequency (monthly vs yearly)
    • PMT often negative to reflect payment outflow
    • type indicates timing (beginning vs end of period)

NPV

  • Net present value with discount rate and periodic cash flows
  • Uses:
    • discount rate
    • stream of future cash flows (income positive, investment negative)

XNPV

  • Like NPV but for non-periodic cash flow schedules
  • Requires explicit dates for each cash flow

IRR

  • Internal rate of return for a sequence of cash flows
  • Requires at least one positive and one negative cash flow
  • Values must be ordered in time sequence

6) Building loan schedules: PMT, PPMT, IPMT, CUMIPMT (where available)

PMT

  • Calculates constant periodic payment.
  • Needed for loan schedule setup.
  • Uses absolute references to allow autofill.

PPMT

  • Principal portion of a payment in a given period
  • Uses:
    • rate (monthly adjusted)
    • period (starting payment period)
    • number of payments
    • PV (principal)
  • Fixed references for consistent repayment schedule; period changes by row.

IPMT

  • Interest portion of a payment in a given period
  • Similar argument structure but returns interest

CUMIPMT / cumi PMT

  • Cumulative interest between start period and end period
  • Requires:
    • rate, term, PV
    • period range or start/end periods
    • type timing argument (0/1)
  • Used to compute:
    • cumulative interest for a year or single month slices

7) Loan schedule templates

  • File → New → search for:
    • “loan calculator”
    • “loan analysis spreadsheet”
    • “loan amortization schedule”
  • Templates are vetted by Microsoft and can be customized and saved.

Part 3: Visualization and analytics

Chart types and when to use them

Bar/Column charts

  • Show changes over time or comparisons.
  • Select the simplest relevant dataset (avoid title/totals rows).
  • Insert → Column/Bar charts → choose subtype → embedded chart.

Area charts

  • Filled area under a line; good for trends; note printing may prefer line charts.
  • Created from Insert → All Charts → Area category.

Pie and Doughnut

  • Show parts of a whole.
  • Doughnut better for multiple rows.

Combo charts

  • Combine chart types (e.g., clustered column + line on secondary axis).
  • Can change which series goes to which axis.

Waterfall charts

  • Running total effect of positives/negatives.
  • Color-coded.
  • “Set as total” for proper interpretation of negative points.

Football field charts

  • “Valuation analyses side by side” (like DCF/LBO style assumptions).
  • Use Stock chart subtype (open-high-low-close).
  • Customizations include:
    • category axis labels
    • data labels (high/low)
    • number formats (currency)
    • axes formatting
    • chart title enhancements
    • optional target price line (dashed)

Spark lines

  • Mini charts inside cells.
  • Types:
    • line
    • column
    • win/loss (high/low & positive/negative)
  • Editing via sparkline contextual ribbon; clear option to remove.

Pivot tables and pivot charts

Pivot table creation workflow

  • Prerequisites:
    • Column headers exist
    • No entire blank rows in source data
  • Steps:
    • Insert → (Recommended pivot tables) or Insert Pivot Table
    • Place in new sheet
    • Use PivotTable Field List:
      • drag dimensions to Rows/Columns
      • drag numeric fields to Values
  • Pivot table behavior:
    • Summary defaults to SUM; change via Value Field Settings (average, etc.)
    • Refresh is required when source data changes (pivot tables do not auto-update).
  • Formatting:
    • apply currency/number formatting to value fields.

Pivot charts

  • Create from same principles but using PivotChart wizard.
  • Pivot chart updates based on underlying pivot table filters/fields.
  • Pivot charts support chart elements/styles via contextual buttons.

Sorting and filtering

Normal dataset

  • Data ribbon → Sort & Filter group → Filter (funnel icons)
  • Sorting:
    • Ascending/Descending keeps rows intact
  • Filtering:
    • check/uncheck items
  • Custom number filters:
    • example: amounts >= 500
  • Clear filter:
    • clear filter from header or use Clear button.

Pivot tables

  • Similar filter/sort arrows exist in row/column areas.
  • Supports:
    • text begins with wildcard matching (e.g., “N*”)
    • overall pivot filters field area:
      • multi-select option
      • clear by reselecting all.

Regression analysis

  • Requires the Analysis ToolPak add-in.
  • Use Data ribbon → Data Analysis → Regression.
  • Inputs include:
    • Y range (dependent variable)
    • X range (independent variable)
    • optional labels and output range
  • Interprets:
    • correlation strength (Multiple R)
    • R square (goodness of fit)
    • ANOVA and p-values
  • Can plot scatter charts and add trend lines.

Analyze Data (Microsoft 365)

  • Data ribbon → Analyze Data
  • Excel suggests charts, pivot tables, and answers to questions based on selected data.
  • Learns/improves across usage.

Part 4: Financial modeling and statements (rate of return → 3-statement model)

1) Rate of Return / Holding Period Return (HPR)

Concept

  • HPR measures investment total return over time using:
    • ending value, beginning value
  • Emphasizes using adjusted close prices (accounts for splits/dividends).

Data sourcing

  • Example sources:
    • Google Finance (optional mention)
    • finance.yahoo.com for historical data
  • Process:
    • Download historical prices as CSV
    • Open in Excel

Worksheet preparation

  • Keep only required columns:
    • date
    • adjusted close

HPR calculation

  • Per period:
    • HPR = (Ending - Beginning) / Beginning
  • Convert decimals to percentage formatting.

Summary statistics

  • Mean/average return:
    • AVERAGE across HPR values
  • Geometric mean return:
    • GEOmean concept mentioned (array formula usage)
    • Uses +1 adjustment and subtract 1 afterwards to convert growth rates correctly.

2) Building a three-statement financial model

Three statements

  • Income Statement (P&L)

Rate this summary

Your feedback will help improve summaries.

Improve this summary

Reprocess with a stronger model when the summary feels incomplete or inaccurate.

Pro

Translate summary in another language

Pro

Ask questions to this video

Chat for follow-up questions, clarifications, and source-backed answers.

Coming soon

Share this summary

Share on X/Twitter Share on LinkedIn Share on Reddit

Original video