Course 094:
Advanced Excel

(2 days)


Course Objectives

In this workshop, we will discuss:

  • Cell and column calculations
  • String and date manipulation
  • Financial functions and calculations
  • Charting and graphing techniques
  • Connecting Databases to Microsoft Excel

Course Outline

Chapter 1: Excel Essentials

  • Navigating Workbooks and Worksheets
  • Setting up Excel for Easy and Speedy Navigation
  • Entering and Formatting Data Using Advanced Excel Features
  • Cut, Copy, Paste, and Paste Special
  • Data Validation
  • Conditional Formatting

Chapter 2: Cell and Column Calculations

  • Math Functions
  • Statistical Function Review
  • Logical Functions and Operations
  • Absolute vs. Relative References
  • Naming Cells and Ranges for More Robust Referencing
  • Error Handling with IFERROR
  • Tracing Precedents and Dependents
  • Using Sparklines

Chapter 3: String and Date Manipulation

  • String Joining and Manipulation
  • Date and Time Functions
  • Lookups Functions to Cross Reference Data: VLOOKUP, HLOOKUP, MATCH, INDEX
  • Formatting Functions

Chapter 4: Financial Functions and Calculations

  • Internal Rate of Return
  • NPV and Net Present Value
  • Incremental Cost, Incremental Margin, and Incremental Revenue
  • Sensitivity Analysis (What-if)

Chapter 5: PivotTable and PivotChart Techniques

  • Understanding What Data is Relevant for a Pivot
  • Learning How to Use Row, Column, and Data Fields Effectively
  • Presenting the Data in the Most Efficient and Effective Way
  • Pivot Table and Pivot Chart Review
  • Multiple Pivots on the Same Data
  • Connecting to Databases from Excel
  • Writing a SQL Query
  • Pulling SQL Data into Pivot Tables
  • Refreshing the Data
  • Handling Empty Cells
  • Filtering Data and Using Slicer
  • Formatting Tables and Charts for Printing

Please Contact Your ROI Representative to Discuss Course Tailoring!