Prerequisites
This course requires basic knowledge in Excel.
Course Description
Course Description
Lookup functions are essential to get related records from other sources. Unlike Databases, spreadsheets don't have data models that connect information. This allows you to pull up related information from another sheet (source) for faster and more precise data instead of going to a tedious manual process which also proves less accurate.
This Virtual Class course on Microsoft Excel is intended for participants who has basic knowledge in Excel and want to go beyond the spreadsheet capabilities. The “Table” feature of Excel allows you to have a dynamic range which automatically expands as you add data and provides flexibility to formula ranges. In addition, this course also introduces lookup and logical functions such as the popular VLOOKUP, IF, COUNTIF AND SUMIF. Data Validation will also be part of the discussion to restrict data entry inside a cell making sure that accurate information will be placed.
Course Objectives
At the end of this course, the participant should be able to:
-
Create and Manage Name Ranges and Dynamic Table in MS Excel
-
Utilize the VLOOKUP and HLOOKUP Functions
-
Discover how to create Logical Test and to use IF, COUNTIFS and SUMIFS Function
-
Use Data Validation feature
Course Content
Lesson 1: Organizing Worksheet and Table Data
-
Create and Modify Name Ranges
-
Create and Modify Tables
-
Format Tables
-
Calculate Data in a Table or Worksheet
Lesson 2: LOOKUP FUNCTIONS
-
What are LOOKUP Functions
-
Setting up Table Array
-
VLOOKUP and HLOOKUP
Lesson 3: DATA VALIDATION
-
Setting up Data Validation
-
Using INDIRECT Function for flexible Dynamic Data Validation
Lesson 4: LOGICAL FUNCTIONS
-
Formulate a Logical Test
-
Using IF Function as an alternative for the TRUE/FALSE result
-
Utilize COUNTIFS and SUMIFS Functions
Who Should Join
Experienced users who want to maximize their learning and further improve their ability in Formulas and Analysis in MS Excel. This also targets people who really work heavily in MS Excel and want to discover a faster and reliable way of dealing with huge amount of records and reports.