Master Microsot Office Excel's full potential by learning how to work with others, automate hard or repetitive activities, and create and apply elaborate formulae and functions using conditional logic.
To help you get the most out of your Excel experience, this Microsoft Office Excel Advanced course expands on the foundational and intermediate skills offered in the Excel Foundation and Excel Intermediate courses.
Training Duration: 2 Days
- Certificate Of Completion Available
- Group Private Class
- VILT Class Available
- SBL-Khas Claimable
This SA-EXA-2: Microsoft Excel Advanced course is intended for participants who wish to gain more knowledge from the intermediate level of Excel.
For participants who are working with nested formulas, performs what if analysis, consolidating data and creates Pivot Table to have in depth understanding of how an electronic spreadsheet works.
Upon completion of this program, participants should be able to:
- Create Subtotal Using The Subtotal Function
- Analyze data Using Pivot Tables
- Perform What If Analysis using:
- Goal Seek
- Solver
- Input Table
- Scenarios
- Use VLOOKUP function to extract data
- Nesting INDEX and MATCH Function
- Combining & Consolidating Data
- Record and Run a Macro
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
- Have attended Microsoft Excel – Foundation & Microsoft Excel - Intermediate Levels; OR
- Able to switch between task applications
- Able to create simple to complex formulas and functions, like:
- COUNTA, COUNTIF & COUNTIFS Function
- AVERAGEA, AVERAGEIF & AVERAGEIFS Function
- SUMIF & SUMIFS Function
- IF Functions
- Nested Functions
- Database Function
- Able to validate data in a Worksheet
- Able to apply Filter data using Auto & Advanced Filters
- Able to clean Duplicate Records
Module 1: Getting the Most From Your Data
Topic A: Outlining and Grouping Data- Using Automatic Outlining
- Displaying and Collapsing Levels
- Grouping Data Manually
- Creating Subtotals
Module 2: What If Analysis?
Topic A: Using Data Analysis Tools- Using a One or Two Input Data Table
- Using Goal Seek
Topic B: Exploring Scenarios
- What is a Scenario?
- Creating a Scenario
- Saving Multiple Scenarios
- Creating a Scenario Summary Report
Topic C: Using Solver
- Understanding Solver
- Generating Reports and Scenarios with Solver
- Changing Solver Values
- Managing Solver Constraints
- Using Solver as a Goal Seek Tool
Module 3: Advanced Excel Task
Topic A: Working with Array Formulas- What are Array Formulas?
- Defining Basic Array Formulas
- Using Functions within Array Formulas
- Using the IF Function in Array Formulas
- Using IFERROR with Array Formulas
Topic B: Using the VLOOKUP Function
- Understanding VLOOKUP and HLOOKUP
- Using VLOOKUP to Find Data
- How to Find an Exact Match with VLOOKUP
- Finding an Approximate Match with VLOOKUP
- Using VLOOKUP as an Array Formula
Topic C: Using the Advanced Function
- Using the INDEX Function
- Using the MATCH Function
- Combining the MATCH and INDEX functions
Topic D: Linking, Consolidating, and Combining Data
- Linking Workbooks
- Consolidating Workbooks
- Combining Worksheets
Module 4: Advanced Chart
Topic A: Create Advanced Chart- Dual Axis Charts
- Creating Custom Chart Templates
Module 5: Analyzing Data with PivotTable, Slicer and PivotCharts
Topic A: Create a PivotTable- Start with Questions End with Structure
- Create PivotTable Dialog Box
- PivotTable Fields Pane
- Summarize Data in a PivotTable
- Show Values As Functionality of a PivotTable
Topic B: Filter Data by Using Slicer
- Slicers
- Insert Slicers Dialog Box
Topic C: Analyzing Data with PivotChart
- Creating PivotChart
- Applying a Style to a PivotChart
Module 6: Enhancing Workbooks
Topic A: Customizing Workbooks- What is a Hyperlink?
- Inserting Hyperlinks
- Editing Hyperlinks
- Formatting Hyperlinks
- Using Hyperlinks in Excel
Module 7: Macros And Form Controls
Topic A: Macros- Recording Macros
- Saving to Macros Enabled Workbook
- Closing and Re-Opening a Macro Workbook
- Security Warning Message
- Playing a Macro
- Assigning a Keystroke to a Macro
Topic B: Form Controls
- What is a Form Control?
- Adding a Control to a Worksheet
- Assigning a Macro to a Control
- Using Form Controls
Module 8: Publishing & Protection
Topic A: Finishing Workbook- Publishing to PDF
Topic B: Protecting Worksheet and Workbook
- Protecting the Current Sheet
- Protecting an Entire Workbook
- File Protection
GRC-101: An Analytical Journey with Microsoft Power BI
This GRC-101: An Analytical Journey with Microsoft Power BI custom developed instructor-led adventurous workshop is designed for audiences to be equipped with the necessary knowledge and skills to kickstart their analytical journey with Microsoft Power BI.
GRC-107: Building A Data Literate Culture In Your Organization
When information becomes a valuable asset, data becomes a strategic priority. Data is becoming a competitive differentiator for top organizations as they focus on digital transformation while still in the early stages of adoption. Data serves as a significant catalyst for a company's digitalization and transformation activities.
PL-300T00: Microsoft Power BI Data Analyst Training Certification Course
This PL-300T00: Microsoft Power BI Data Analyst course will discuss the various methods and best practices that are in line with business and technical requirements for modeling, visualizing, and analyzing data with Power BI. Check out our Microsoft Power BI Training here.