Description
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
METHODOLOGY
- This course will be conducted with lectures, PowerPoint Presentation, demonstration on lab, and practical exercise.
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
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.
COURSE OUTLINE
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
