The Financial Analyst's Complete Guide to Excel

Introduction to Section 1 on efficiency tips.

Use your keyboard to move efficiently through your workbooks.

Set up your screen(s) for success!

Filter values by one or more columns of your choosing.

Introduction to Section 2 on keyboard shortcuts and formatting techniques.

Introduction to basic keyboard shortcuts you'll be using as an analyst.

Various types of anchoring which are used to maintain cell references in formulas.

Keyboard shortcuts which are used to create a more professional presentation.

Custom cell formats which are popular in accounting & finance.

Introduction to Section 3 on Excel's catalog of lookup & reference functions.

Use MATCH to determine whether or not a value exists in a row or column.  If it does, MATCH returns a numerical output as to where this value can be found.

Use VLOOKUP to search for values within a column.

Pair VLOOKUP with MATCH for a more dynamic search function.

VLOOKUP's range lookup can be used to perform exact or approximate searches.

Use HLOOKUP to search for values within a row.

XLOOKUP is a modern alternative to VLOOKUP and HLOOKUP.

INDEX/MATCH is a dynamic lookup & reference function and is the preferred search technique for advanced users.

INDEX/MATCH applied to the Section 3 Workbook.

INDEX/MATCH applied in a financial statement setting.

OFFSET is similar to INDEX/MATCH and can be valuable for financial analysts.

In this video we'll pair OFFSET with INDEX/MATCH for a truly dynamic exercise.

Introduction to Section 4 on "IF" Functions, which are used to conduct logical operations.

The "IF" function is at the core of all logical operator functions.

A nested IF statement is an IF statement within an IF statement.  Use nested IF statements when testing for multiple conditions and/or when multiple outputs are needed.

An alternative and more visually appealing way to test for multiple conditions.

Use OR to test if any of the specified conditions are true.  Use AND to test if all of the specified conditions are true.

IFERROR signals to Excel what the output should be when an error occurs.

SUMIF is a summation function that tallies the sum of all cells which meet a single criteria.

SUMIF is a summation function that tallies the sum of all cells which meet multiple criteria.

A very important concept for you to understand as a financial analyst.  Consolidate your financial statements and improve your presentation using SUMIF.

Use COUNTIF to count the number of instances that meet a specified criteria.  Use COUNTA to simply count the number of cells in a row, column, or workbook.

A brief summary of some of Excel's rounding functions.

Introduction to Section 6 on financial functions.

Use Excel to perform time value of money calculations.

NPV is a capital budgeting tool used for evaluating investment opportunities.

IRR is another capital budgeting tool used for evaluating investment opportunities.

A comprehensive NPV & IRR exercise with additional financial calculations thrown in for good measure.

Introduction to Section 7 on Conditional Formatting.

Learn the basics of conditional formatting.

Use conditional formatting to provide a color callout as to when the balance sheet is in, and out of balance.

Dynamic example which ties together the concepts of Conditional Formatting.

Introduction to Section 8 on data analysis tools.

Use pivot tables to quickly sort and analyze large datasets.

Use What If Analysis to perform a sensitivity analysis.

Solver is an extension to What If Analysis and can be used to perform a breakeven analysis.

Use macros to automate repetitive and mundane tasks.