Miracles of Excel Formulas and Functions

Previous 2 / 8 Next

Miracles of Excel Formulas and Functions MICROSOFT OFFICE PRO Microsoft Office Application Training


Miracles of Excel Formulas and Functions

Course Duration: 2 Days

Who is this course for?

This program targets audience who would like to explore the possibilities of Excel formulas and functions.  And if you are those who wish to gain the skills and understand the formulas and functions syntax. Learn how do they apply in a nested formulas environment?
 

Prerequisites

Knowledge of Microsoft Excel Intermediate is essential with the follow pre-requisites:
  • Able to switch between task applications
  • Able to “Create”, “Save”, “Open”, and “Print” spreadsheet
  • Able to use the “Undo” function
  • Insert and delete Row & Column
  • Print a spreadsheet with headers and footers applied
  • Insert, Delete and Rename a worksheet
  • Able to create basic formulas like addition, subtraction, multiplication and division
  • Able to use basic functions like Sum, Average, Count, Max and Min function
 

Methodology

  • This program will be conducted with interactive lectures and hands-on application for each participant.
  • The Instructor will demonstrate all techniques using simple but realistic examples that enable participants apply what they learnt into their work.
  • Sessions will include discussions, practical class work and Q & A session.
  • Each participant will be assigned to a PC.

Course Syllabus

Module 1: Cell and Range References

  • Creating an Absolute Reference

Module 2: Statistical Functions

  • COUNTIF Function
  • COUNTBLANK Function
  • RANK Function

Module 3:  Math and Trig Functions

  • SUMIF To Add Data That Satisfy Condition
  • ROUND Function
  • ROUNDUP Function
  • ROUNDDOWN Function

Module 4: The wonders of Text Functions

  • Changing the Case of Text
  • Combining Text from Multiple Columns
  • Breaking Date Field into Individual Day, Month and Year
  • Trimming Off Excess Spaces in A Cell
  • Replacing existing data with new value in a cell using SUBSTITUTE Function
  • Converts value to text with TEXT Function

Module 5: Logical Functions

  • Hiding formula error with IFERROR function
  • Using If Function to make decision
  • AND function
  • Create Multiple Conditions Using Nested If
  • Nested IF & AND Function

Module 6 - Lookup & Reference Formulas

  • Use VLOOKUP To Find Specific Data
  • VLOOKUP Array Formula
  • Use MATCH to Match Data
  • Use INDEX to Retrieve Data
  • Nested INDEX & MATCH

Module 7: Date & Time Formulas

  • Date Calculation
  • Add A Number to Current Date To Get New Dates
  • Calculate a duration Between Two Dates



Please leave your enquiry here, we will reply as soon as possible.
Name*  
Company Name  
Product Interested  
Quantity  
Email*  
Contact No.*  
Attachment  
*only support gif, jpeg, jpg, png, pdf
Messages*  

Switch to Mobile Version
available in
Subscribe Newsletter