Module 1: Intro, Setup, and Navigation (Separate PC and Mac Versions)
In this module, you’ll learn how to set up and optimize your system and Excel settings, as well as how to navigate, use the ribbon menu, the format dialog box, and the key shortcuts for working with cells, rows, and columns.
You’ll also learn how to enter formulas, text, and numbers, use absolute and relative references, name and jump to cells, and manipulate worksheets.
Module 2: Formatting and Printing (Separate PC and Mac Versions)
In this module, you’ll learn the key shortcuts for formatting, how to use built-in number formats, and how to use date, time, and text manipulation functions.
You’ll also learn how to clean up data, set up custom number formats, format financial models, and use conditional formatting that changes entire rows based on the contents of individual cells.
The final lessons teach you how to group and hide rows and columns, how to set up freeze panes and split panes, and how to format spreadsheets for printing.
Module 3: Financial Formulas and Lookup Functions (Combined PC/Mac)
In this module, you’ll learn about the key functions and formulas required for financial modeling in Excel, including logical, arithmetic, and financial functions, as well as lookup functions, INDEX, MATCH, and INDIRECT.
You will practice these functions across several exercises, and you’ll learn how to use array functions, CHOOSE and OFFSET, sensitivity tables, and Goal Seek and Solver for more advanced features.
You’ll also learn how to handle circular references in models, how to audit formulas and find problems, and how to leave comments.
Module 4: Data Manipulation and Analysis (Combined PC/Mac)
In this module, you’ll learn how to manipulate and analyze data in Excel, including data tables, sorting and filtering, Power Query for importing data from the internet, SUMIFS, SUMPRODUCT, and database functions.
You’ll also learn how to use pivot tables and Power Pivot to slice, dice, and aggregate data according to different criteria, and you’ll practice customizing the tables, creating visualizations, and using Calculated Columns and Measures to build KPIs.
The last few lessons here only work in the PC versions of Excel as of the time of this course because Microsoft had not yet implemented Power Pivot in Mac Excel; however, you can replicate many of these features by creating extra columns in normal data tables.
Module 5: Charts and Graphs (Combined PC/Mac)
In this module, you’ll learn how to set up charts and graphs in Excel, ranging from the basic line and column charts up through dynamic charts with checkboxes and scroll bars.
You’ll learn how to create charts that are specific to investment banking and finance roles, such as valuation multiple graphs, football field valuation charts, price-volume graphs, and waterfall bridge charts for analysis of companies’ financial results.
You’ll conclude by learning how to create dynamic pie charts and combo charts using INDEX/MATCH and form controls in the Developer Toolbar.
Module 6: VBA and Macros (Combined PC/Mac)
In this module, you’ll learn VBA and macros by writing your own package of useful Excel macros using concepts such as loops, range and cell manipulation, variables and constants, arrays and dictionaries, and string manipulation.
You’ll create macros for “Input Box” cell creation and the color-coding and printing of financial models, and then you’ll expand on those by writing macros to cycle number formatting, change the decimal places for numbers with different formats, flip the signs, and flash fill right and down.
You’ll also write macros to toggle the error-wrapping of formulas, toggle absolute vs. relative vs. mixed references, go to the min and max of a range, create Tables of Contents, and create Price-Volume Graphs.