Microsoft Excel - Advanced
Duration: 2 Days
Who is this course for?
This 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, perform what-if analysis, consolidating data and creating Pivot Tables to have in-depth understanding of how an electronic spreadsheet works.
Pre-requisites
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
- Able to switch between task applications
- Able to create simple to complex formulas and functions
- Able to validate data in a worksheet
- Able to apply filter data using Auto & Advanced Filters
- Able to clean duplicate records
Learning outcomes / benefits
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
- Use VLOOKUP function to extract data
- Nesting INDEX and MATCH Function
- Combining & consolidating data
- Record and run a macro
Key content
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
- Understand 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 INDEX Function
- Using MATCH Function
- Combining MATCH & 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 Pivot Table, Slicer & Pivot Charts
Topic A - Create a Pivot Table
- Create Pivot Table Dialog Box
- Pivot Table Fields Pane
- Summarize Data in a Pivot Table
- Show Values as Functionality of a Pivot Table
Topic B - Filter Data by Using Slicer
- Insert Slicers Dialog Box
Topic C - Analyzing Data with Pivot Chart
- Creating a Pivot Chart
- Applying a Style to a Pivot Chart
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 & form controls
Topic A - Macros
- Recording Macros
- Saving 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
- 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 or XPS
Topic B - Protecting Worksheet and Workbook
- Protecting Current Sheet
- Protecting Entire Workbook
- File Protection
Methodology
Informative presentation, group discussion and presentation, hands-on practical session.
