Duration
1 days
INTRODUCTIONS
In this course, the participant will understand the concepts of excel calculation to prepare data. Participant shall understand and be able to use formulas and functions for Excel spreadsheet. Beside the calculation, participant shall understand and be able to work with Microsoft Excel table.
TARGET AUDIENCE
Designed for anyone with minimal or no prior knowledge of Microsoft Excel 365 and looking to acquire solid foundational skills to perform calculation and table in a spreadsheet.
PRE-REQUISITE
Basic knowledge of computers, using the keyboard, mouse and Windows and Excel is required.
LEARNING OUTCOME
In this course, participants will:
- Discover calculation features to increase data reliability for reporting use.
- Understand how to work with formulas and functions.
- Learn on how to work with Excel table and its features.
- Increase knowledge on filtering and sorting data in Table and data list.
Module 1: Working with Formulas
- Concept of Formulas
- Understanding Relative and Absolute Cell References
- Understanding Named Ranges
- Defining Named Ranges
- Editing Named Ranges
- Deleting Named Ranges
- Using Named Ranges in Formulas
- Using Array Formulas
- Using 3D References
Module 2: Working with Functions (I)
- Concept of Functions
- Using Advanced Functions
- Using Logical Function: IF
- Using Logical Function: AND
- Using Logical Function: OR
- Using Logical Function: IFS, SWITCH
Module 3: Working with Functions (II)
- Using Statistical functions: AVERAGEIF, COUNTIF
- Using Lookup Functions: VLOOKUP ,HLOOKUP, LOOKUP
- Using Text functions: TEXTJOIN, CONCAT
- Using Statistical Functions: MAXIFS and MINIFS
Module 4: Auditing Worksheets
- Tracing Formula Precedents and Dependents
- Showing Formulas
- Evaluating Formulas
- Using Error Option Buttons
- Running an Error Check
Module 5: Working with Table
- Converting Data in range as a Table
- Total the Data in Table
- Removing Duplicates from Table
- Using Sort and Filter
Programme scope, delivery arrangements and suitability are confirmed during enquiry.