Duration
1 day
Audience
This course is intended for users of Microsoft Office Excel who want to learn about useful formulas and functions.
Prerequisites
Before attending this course, students should have experience:
- A basic working knowledge of Microsoft Office Excel.
At Course Completion
After completing this course, you will be able to:
- Apply Formula and Functions Basic
- Statistical and Logical Functions
- Lookup and Reference Formulas
- Text Formulas
- Date and Time Formulas
- Array and Database Functions
- Efficiency Tips
Module 1: Making Data Work For You
This module explains how to understand and apply Excel basic formulas and functions.
Lessons
- Formula basics
- Using cell references
- Copy formula without changing cell reference
- Transpose formula
- Using nested functions
After completing this module, students will be able to:
- Understand and apply formula basics
- Using cell references
- Copying formula without changing cell reference
- Transpose formula using paste special
- Using nested functions
Module 2: Statistical and Logical Functions
This module explains how to use logical functions including CountIf, Sumif, If, IsError.
Lessons
- Perform calculation using CountIF
- Perform calculation using SumIF
- Perform calculation using AverageA
- Using IF function to prevent division by zero
- Using IsError function to avoid error display
- Creating multiple conditions using nested IF
- Using logical function OR, And
After completing this module, students will be able to:
- Perform calculation using CountIf, SumIf, AverageA
- Using If function to prevent division by zero
- Using IsError function to avoid error display
- Create multiple conditions using nested IF
- Using logical function OR, AND
Module 3: Lookup and Reference Formulas
This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.
Lessons
- Using Vlookup to find specific data
- Using Hlookup to find values in rows
- Using Match and Index to retrieve data
After completing this module, students will be able to:
- Using Vlookup to find specific data
- Using Hlookup to find values in rows
- Using Match and Index to retrieve data
Module 4: Text Formulas
This module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.
Lessons
- Changing case of text
- Append text and numerical value
- Convert imported text format into numbers
- Break imported date field into individual columns
After completing this module, students will be able to:
- Changing case of text using Upper, Lower or Proper formula
- Append text and numerical value
- Convert imported text format into numbers
- Break imported date field into individual columns
Module 5: Date and Time Formulas
This module explains how to make use of calculate the difference of two given Date fields and to perform calculation with Time fields.
Lessons
- Perform addition to Date fields
- Calculate difference between two Dates
- Perform calculations with Time fields
After completing this module, students will be able to:
- Perform addition and calculate difference between two dates
- Perform calculations with Time fields
Module 6: Array and Database Functions
This module explains how to apply and use advance formula including Array, Frequency and Database functions.
Lessons
- Using Array Formulas
- Calculate the difference between Maximum and Minimum values
- Using Frequency function to Count responses
- Using Database functions DSum and DCount
After completing this module, students will be able to:
- Using Array Formulas
- Calculating the difference between Maximum and Minimum values in an Array
- Using Frequency function to Count responses in tabulated data
- Using Database functions DSum and DCount
Module 7: Efficiency Tips
This module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.
Lessons
- Shortening worksheets names
- Protecting cells containing formulas
- Using Data Validation
- Displaying Formula syntax
- Using Auditing Tools for errors checking
- Tracing precedent and dependent
- Adding comments to worksheet
After completing this module, students will be able to:
- Understand the advantages of shortening worksheet names
- Protecting cells from amendments by others
- Using Data validation to improve data entries
- Using Auditing Tools for checking errors
- Adding useful notes by commenting worksheet
Programme scope, delivery arrangements and suitability are confirmed during enquiry.