Duration: 1 day – 7 hr.
Overview
This Microsoft Excel Advanced Training course, you will learn how to maximize Excel even further to help you analyze data quicker. You will learn features like PivotTables, macros, and how to protect your spreadsheets from being misused by others. At this level of use and collaboration, you have also likely encountered your fair share of issues and challenges. You’re too busy, though, to waste time scouring over workbooks to resolve issues or to perform repetitive, monotonous tasks. You need to know how to get Excel to do more for you so you can focus on what’s really important: staying ahead of the competition. That’s exactly what this course aims to help you do.
Objectives
- Resolve formula errors
- Manage data such as removing duplicates, validating and transposing data
- Answer “What-if” scenarios using goal seek
- Work with PivotTables
- Create basic dashboards to visualize data
- Use Macros to automate repetitive tasks
- Protect your spreadsheet from misuse by others
Audience
- Anyone who wants to use Microsoft Excel to create spreadsheets and automate data processing. Basic Excel knowledge at the level of Microsoft Excel Basic and Microsoft Excel Intermediate is expected for this Microsoft excel training.
Pre- requisites
- Basic Excel knowledge at the level of Microsoft Excel Basic and Microsoft Excel Intermediate
Course Content
Part 1. Basic to Advanced MS Excel Functions
- Text Functions
- Combining Texts (CONCATENATE)
- Extracting Values from Texts (LEFT, RIGHT, MID)
- Changing Case (UPPER, LOWER, PROPER)
- Other Accessory Text Functions (TRIM, VALUE, SUBSTITUTE)
- Logical Functions
- Basic IF Statements
- Nested and Series IF Statements
- Combining Logical Statements with AND and OR
- Math Functions
- Basic Math Functions (SUM, AVERAGE, MAX, MIN, COUNT)
- Named Ranges
- Conditional Math Functions (COUNTIF, SUMIF, AVERAGEIFS, etc.)
- Shortening Long Formulas with Arrays
- Lookup Functions
- VLOOKUP
- Advanced Usage of VLOOKUP
- INDEX and MATCH
Part 2. Managing Data Using Excel Tools
- Conditional Formatting
- Basic Conditional Formatting
- Formula-Based Formatting
- Data Validation
- Basic Dropdown Menus and Cell Validations
- Advanced Dropdown Menus
- Subtotal Tool
Part 3. Analyzing and Presenting Data Using PivotTables
- Basic PivotTable Features
- Refresh and Change Data Source
- Grouping Data
- Using Slicers
- PivotCharts
Part 4. Introduction to Macros
- Understanding Macro Security
- Macro Recording
