top of page

MS Excel’s Dynamic Tables with Data Validation, Lookup and Logical Formulas

DURATION: 2 hours 

Prerequisites

This course requires Basic knowledge in Excel.

Course Description 

Course Description 

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:      

                             

  1. Create and Manage Name Ranges and Dynamic Table in MS Excel

  2. Utilize the VLOOKUP and HLOOKUP Functions

  3. Discover how to create Logical Test and to use IF, COUNTIFS and SUMIFS Function

  4. 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.

bottom of page