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! |