Course Objectives
Upon completion of the course, participant should be able to:
- Perform data grouping
- Use VLOOKUP and HLOOKUP function
- Use PivotTable, Slicer and Pivot Chart for data analyzing and summarizing
- Create a secure and protected worksheet
Duration
1 Day
Target Audience
This course is suitable for participant that such as Clerk, admin, and others who wish to start learning and using Microsoft Excel for their day-to-day job.
Prerequisites
- Have attended Microsoft Excel Foundation and Intermediate, OR
- Able to create function and functions in worksheet
- Able to organize and filter data in worksheet
- Able to manage multiple windows of workbook in the same worksheet
Methodology
This course will be conducted with lectures, PowerPoint Presentation, demonstration on lab, and practical exercise.
Module 1: Getting Different Views on Your Data
Topic A: Outlining and Grouping Data
- Using Automatic Outlining
- Displaying and Collapsing Levels
- Grouping Data Manually
- Creating Subtotals
Module 2: Advanced Functions
Topic A: Working with Array Formulas
- What are Array Formulas?
- Using Functions within Array Formulas
Topic B: Using the LOOKUP Function
- Using VLOOKUP to Find Data
- Find Exact Match with VLOOKUP
- Find Closest Match with VLOOKUP
- Find with HLOOKUP
Topic C: Linking, Consolidating, and Combining Data
- Linking Workbooks
- Consolidating Workbooks
- Combining Worksheets
Module 3: Summarizing and Analyzing Data with PivotTable, Slicer and Pivot Charts
Topic A: Create a PivotTable
- Create PivotTable Dialog Box
- PivotTable Fields Pane
- Summarize Data in a PivotTable
- Show Values as Functionality of a PivotTable
- Personalize the default PivotTable layout
Topic B: Filter Data by Using Slicer
- Slicers
- Insert Slicers Dialog Box
Topic C: Analyzing Data with PivotChart
- Creating PivotChart
- Editing a PivotChart
Module 4: Prevent User Changes with Protection
Topic A: Protecting Worksheet and Workbook
- Protecting the Current Sheet
- Protecting an Entire Workbook
- File Protection
