Duration
1 day
Introduction
Get a head start in acquiring the knowledge of using Formulas in your daily work as well as expand your Microsoft Excel knowledge. This one day course will empower you to have answers to excel question at your fingertip. There a numerous and a varied number of new tips you will learn irrespective of the Excel version used. This course will serve as a refresher as well as a reference. The course is for users at all levels, from beginners through intermediate and advanced.
Course Objectives
- Apply Formula and Functions Basic
- Statistical and Logical Functions
- Lookup and Reference Formulas
Target Audience
Beneficial for any Microsoft Excel users along a broad spectrum of experience who desire picking up new tips to build advanced formulas in their spreadsheets for daily work.
LESSON 1 – FORMULAS AND FUNCTIONS BASIC
INTRODUCTION FORMULA BASICS USE CELL REFERENCES TRANSPOSE A FORMULA
COPY AND PASTE VALUES PERFORM A QUICK CALCULATION
LESSON 2 - STATISTICAL AND LOGICAL FUNCTIONS
USE COUNTIF TO CALCULATE SURVEY RESULTS
USE SUMIF TO ADD DATA THAT SATISFY CONDITION USE AVERAGEA TO INCLUDE ALL CASES
RETURN MINIMUM AND MAXIMUM VALUE USING MAXIFS AND MINIFS USE IF FUNCTION TO PREVENT DIVISION BY ZERO
IFS AND SWITCH FUNCTIONS HELP SPECIFY A SERIES OF CONDITIONS
USE ISERROR FUNCTION TO AVOID ERROR DISPLAY CREATE MULTIPLE CONDITIONS USING NESTED IF LESSON 3: LOOKUP & REFERENCE FORMULAS
USE VLOOKUP TO FIND SPECIFIC DATA USE HLOOKUP TO FIND VALUES IN ROWS USE INDEX & MATCH TO RETRIEVE DATA LESSON 4 - TEXT FORMULAS
CHANGE CASE OF TEXT
JOIN TEXT FROM MULTIPLE COLUMNS COMBINE TEXT AND FORMATTED NUMBERS
CONVERT IMPORTED TEXT FORMAT INTO NUMBERS BREAK IMPORTED DATE FIELD INTO INDIVIDUAL COLUMNS EXTRACT A NAME FIELD INTO THREE COLUMNS
COMBINE TEXT STRINGS USING TEXTJOIN AND CONCAT
LESSON 5: DATE & TIME FORMULAS
CALCULATE A PERIOD OF TIME BETWEEN TWO DATES PERFORM CALCULATION WITH TIME FIELDS
LESSON 6: ARRAY AND DATABASE FUNCTIONS
USE ARRAY FORMULAS
CALCULATE THE DIFFERENCE BETWEEN MAXIMUM AND MINIMUM VALUES IN AN ARRAY FIND VALUE IN LIST
USE FREQUENCY FUNCTION TO COUNT RESPONSES ANALYSE DATA IN A DATABASE
LESSON 7: EFFICIENCY TIPS
REDUCE FORMULA RECALCULATION TIME
ENTER FORMULAS QUICKLY BY SHORTENING SHEET NAMES SELECT & PROTECT CELLS CONTAINING FORMULAS REDUCE DATA ENTRY ERRORS BY USING DATA VALIDATION DISPLAY & PRINT FORMULA SYNTAX
USE AUDITING TOOLS TO CHECK FOR ERRORS CHECK DATA BY USING TRACE PRECEDENTS
USE COMMENTS TO ANNOTATE YOUR WORKSHEET
Programme scope, delivery arrangements and suitability are confirmed during enquiry.