Course Objective :
This Advanced Microsoft Excel Course is designed by Industry Expert and covers in-depth knowledge of functions, conditional formatting, pivot tables, audit and analyze worksheet data, VBA Macro, utilize data tools, collaborate with others, and create and manage macros.
Target Audience :
This course is designed for the basic and intermediate Excel user who desires to learn more advanced skills. Learn the most advanced formulas, functions, charts and types of financial analysis to be an Excel power user.
Pre – Requisites :
Basic Excel user and worksheet knowledge.
Course Outline :
Using Formatting and Functions
- Applying Formatting to Numbers
- Creating Custom Number Formats
- Applying Conditional Formats
- Using Formulas in Conditional Formats
- Absolute and Relative References
- Naming a Range
- Using a Named Range in a Formula
- Using Lookup Functions (vlookup, hlookup)
- Logical Functions (if, sumif, countif etc…)
Working with Data
- What is a List?
- Sorting Data
- Subtotaling and Outlining Data
- Extracting Data with Filters
- Applying Filters
- Autofill
Analyzing Data
- Using PivotTable
- Managing PivotTable Reports
- Creating PivotChart Reports
- Using Charts and Trend lines
Working with Data tools
- What if analysis (Scenario Manager, Goal Seek)
- Text to Columns
- Removing duplication
- Data Validations
Sharing Workbooks
- Creating a Shared Workbooks
- Consolidating Data
- Security Features in Excel
- Assigning a Password
- Adding Comments
- Preparing a Workbook
3 D reference Formula
- What is an Excel 3-D reference?
- Creating a 3D reference in Excel
- Including a new sheet in an existing 3D formula
- Creating a defined name for a 3D reference
Macros
- Adding the Developer Tab
- Starting the Excel macro recorder
- Excel macro recorder options
- Recording the macro
- Running the macro
- Macro errors / Editing the macro