Course Summary:

It's time to show Excel who's boss! Whether you're starting from square one or aspiring to become an absolute Excel wizard, this is the right place for you. This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Through hands-on, contextual examples, you'll learn why these formulas are awesome and how they can be applied in a number of ways. This is especially useful for including in your portfolio or resume, so future employers can feel confident in your skill set.

This advanced tutorial will help you become a world-class financial analyst for careers in investment banking, private equity, corporate development, equity research and FP&A.  By watching the instructor build all the formulas and functions right on your screen, you can easily pause, rewatch, and repeat exercises until you've mastered them.

Course Objectives:

At the end of this workshop, participants should be able to:

• Save a workbook as a template, with colors, fonts, cell styles and themes
• Reference data using structured references or data in another workbook
• Protect a workbook from further editing
• Prepare a workbook for internationalization
• Apply custom data formats and validation
• Apply advanced conditional formatting and filtering
• Use form controls
• Work with macros
• Define and use named ranges
• Apply functions in formulas using logical functions, math & trig functions, statistical functions and date & time functions
• Get & transform data
• Perform what if analysis
• Troubleshoot formulas
• Create advanced charts
• Create and manage PivotTables
• Create and manage PivotCharts

Course Outline

Module One: Getting Started

• Workshop Objectives

Module Two: Manage Workbook Options and Settings

• Manage Workbooks
• Save a Workbook as a Template
• Reference Data in Another Workbook
• Reference Data by Using Structured References
• Display Hidden Ribbon Tabs

Manage Workbook Review..

• Mark a Workbook as Final
• Protect a Workbook with a Password.
• Protect a Worksheet to Restrict Editing.
• Protect Workbook Structure.
• Manage Workbook Versions.
• Setting Calculation Options.

Module Two: Review Questions.

Module Three: Apply Custom Data Formats and Layouts.

Prepare a Workbook for Internationalization.

• Apply International Currency Formats.
• Apply Locale to Date or Time Formats.

Apply Custom Data Formats and Validation.

• Create Custom Number Formats.
• Populate Cells by Using Advanced Fill Series Options.
• Configure Data Validation.

Apply Advanced Conditional Formatting and Filtering.

• Create Custom Conditional Formatting Rules.
• Create Conditional Formatting Rules that Use Formulas.
• Manage Conditional Formatting Rules.

Create and Modify Custom Workbook Elements.

• Create Custom Color Formats.
• Create Custom Font Sets.
• Create and Modify Cell Styles.
• Create and Modify Custom Themes.
• Manage Multiple Options for Theme Fonts.
• Insert and Configure Form Controls.

Work with Macros.

• Enable Macros.
• Record a Macro.
• Run a Macro.
• Edit a Macro.
• Copy Macros from One Workbook to Another.

Module Three: Review Questions.

Module Four: Create Advanced Formulas.

Define Named Ranges and Objects.

• Name Cells.
• Use Named Cells in a Formula.
• Manage Named Ranges and Objects.

Apply Functions in Formulas.

• Perform the AND Function and the OR Function.
• Perform NOT Function.
• Perform Logical Operations by Using Nested Functions.
• Perform SUMIFS, AVERAGEIFS, and COUNTIFS Functions.

Look Up Data by Using Functions.

• Look Up Data by Using the VLOOKUP Function.
• Look Up Data by Using the HLOOKUP Function.
• Look Up Data by using the MATCH Function.
• Look Up Data by Using the INDEX Function.

Apply Advanced Time and Date Functions.

• Serialize Numbers by Using Date and Time Functions.
• Reference the Date and Time Using the NOW and TODAY functions.

Perform Data Analysis and Business Intelligence.

• Import, Transform, Combine, Display, and Connect to Data.
• Consolidate Data.
• Perform What-If Analysis by Using Goal Seek.
• Perform What-If Analysis by Using Scenario Manager.

Troubleshoot Formulas.

• Trace Precedence and Dependence.
• Monitor Cells and Formulas using the Watch Window..
• Validate Formulas by Using Error Checking Rules.
• Evaluate Formulas.

Module Four: Review Questions.

Module Five: Create Advanced Charts and Tables.

Create Advanced Charts.

• Add Trendlines to Charts.
• Create Dual-Axis Charts.
• Save a Chart as a Template.

Create and Manage PivotTables.

• Create Slicers.
• Create PivotTables.
• Modify Field Selections and Options.
• Group PivotTable Data.
• Reference Data in a PivotTable by Using the GETPIVOTDATA Function.
• Add Calculated Fields.
• Format Data.

Create and Manage PivotCharts.

• Create PivotCharts.
• Drill Down into PivotChart Details.
• Apply Styles to PivotCharts.
• Manipulate Options in Existing PivotCharts.

Module Five: Review Questions.

Module Six: Wrapping Up.

• End of Course Assessments and SUmmary