Duration
2 days
Lesson 1 – Formulas And Functions Basic
Introduction ~ Formula Basics
Use Cell References
Copy Formula Without Changing Cell References
Transpose A Formula
Copy And Paste Values
Perform A Quick Calculation
Nesting Functions
Review 1
Practice 1
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
Use IF Function to Prevent Division By Zero
Use IsError Function to Avoid Error Display
Create Multiple Conditions Using Nested IF
Review 2
Practice 2
Lesson 3: Lookup & Reference Formulas
Use VLookup to Find Specific Data
Use HLookup To Find Values in Rows
Use Index & Match to Retrieve Data
Review 3
Practice 3
Lesson 4 - Text Formulas
Change Case of Text
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
Review 4
Practice 4
Lesson 5: Date & Time Formulas
Add a Number to Current Date to Get New Dates
Calculate a Period of Time Between Two Dates
Perform Calculation with Time Fields
Review 5
Practice 5
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
Review 6
Practice 6
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
Review 7
Practice 7
Programme scope, delivery arrangements and suitability are confirmed during enquiry.