Skip to content
  • Sign In
  • Try Now
View all events
Finance

Excel Skills for Finance

Published by O'Reilly Media, Inc.

Intermediate content levelIntermediate

Financial functions, lookups, conditional formatting, and forecasts

Course outcomes

  • Use core financial functions such as PV, FV, PMT, NPV, and IRR to determine loan payments, analyze investments, and evaluate financial decisions
  • Use lookup functions such as VLOOKUP, XLOOKUP, INDEX, and MATCH to extract and cross-reference data
  • Apply number formats, including custom number formats
  • Format cells, including conditional formatting
  • Use TREND and GROWTH to make linear and exponential growth forecasts
  • Use Excel’s Forecast Sheet to make seasonal or cyclical forecasts
  • Apply custom data validation rules
  • Use the Stock and Currency data types, and create and interpret stock charts
  • Write your own custom financial functions

Course description

Join expert Dawn Griffiths to learn the core Excel skills you need to work with financial data more effectively. You’ll use financial functions such as PMT, FV, NPV, and IRR to determine the future value of an investment, calculate loan payments, and determine project viability. You’ll learn how to use the VLOOKUP, XLOOKUP, INDEX, and MATCH functions to look up values. You’ll find out how to use custom number formats and apply conditional formatting to highlight information and make patterns more apparent. You’ll discover techniques for working with linear, exponential, and seasonal data, including analyzing trends with moving averages and using the Forecast Sheet to make seasonal forecasts. You’ll create custom data validation rules and learn techniques for working with stock and currency data. Finally, you’ll discover how to create custom functions, making it easier to calculate financial quarters and years. By the end of the course, you’ll have acquired key Excel skills for working with financial and other types of data.

What you’ll learn and how you can apply it

  • Use core financial functions and interpret their output
  • Use lookup functions, including XLOOKUP, INDEX, and MATCH
  • Understand conditional formatting to highlight data and draw out patterns
  • Make linear and exponential growth forecasts using the TREND and GROWTH functions
  • Use the Forecast Sheet and forecast functions to make seasonal forecasts
  • Validate data and create custom data validation rules
  • Define and use custom financial functions

This live event is for you because...

  • You work with financial data and want to learn how to use Excel’s main financial functions.
  • You want to know how Excel can help you make financial decisions.
  • You want to be able to use lookup functions and conditional formatting in your spreadsheets.
  • You’re an Excel power user and want to know how to use more of Excel’s features.
  • You want to know how to use Excel’s Forecast Sheet to make sense of seasonal data.
  • You want to be able to write your own custom functions.

Prerequisites

  • A computer with a working copy of Microsoft Excel (preferably Excel 2016+)
  • Basic knowledge of Excel functions and tables

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Using financial functions (70 minutes)

  • Demonstration: Using financial functions such as PMT, FV, NPV, and IRR with loans and investments
  • Hands-on exercises: Calculate payments; meet investment goals
  • Q&A
  • Break

Using lookup functions (50 minutes)

  • Demonstration: Using VLOOKUP and XLOOKUP, MATCH and XMATCH, and INDEX
  • Hands-on exercises: Look up values using different functions
  • Q&A
  • Break

Formatting and conditional formatting (60 minutes)

  • Demonstration: Formatting numbers; custom number formats; formatting cells; using conditional formatting; applying a custom conditional formatting rule
  • Hands-on exercises: Apply the format; create the rule
  • Q&A
  • Break

Data validation (20 minutes)

  • Demonstration: Using data validation; defining custom data validation rules
  • Hands-on exercises: Validate the data
  • Q&A

Forecasting (40 minutes)

  • Demonstration: Making linear and exponential forecasts with TREND and GROWTH; using moving averages; making seasonal forecasts with Excel’s Forecast Sheet
  • Hands-on exercises: Make the forecasts
  • Q&A
  • Break

Stocks and currency (20 minutes)

  • Demonstration: Using the Stock and Currency data types; creating stock charts
  • Hands-on exercises: Create the stock chart
  • Q&A

Custom financial functions (40 minutes)

  • Demonstration: Calculating the financial quarter and year; creating custom functions using VBA and LAMBDA
  • Hands-on exercises: Create the function
  • Q&A

Your Instructor

  • Dawn Griffiths

    Dawn Griffiths is an author and trainer with over 20 years of experience using Excel. Her most recent book is Excel Cookbook, and she's also written several books in the Head First series, including Head First Statistics, Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, to teach key concepts and techniques in a way that keeps your brain active and engaged.

    Xlinksearch