Course Overview
Learning Options
Target audiences
- Aimed at information workers who need to learn more in-depth on the techniques of creating time saving Microsoft Excel® macros.
Schedule Dates
Curriculum
-
Module 6: Text Formulas
This module explains how to apply Text formula to help change casing of text, append text and numerical value in excel spreadsheet.
-
Module 7: Text Formulas
After completing this module, students will be able to:
-
Module 8: 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.
-
Module 9: Exporting and Importing Data
After completing this module, students will be able to:
-
Module 10: Array and Database Functions
This module explains how to apply and use advance formula including Array, Frequency and Database functions.
- Using Array Formulas
- Calculate the difference between Maximum and Minimum values
- Using Frequency function to Count responses
- Using Database functions DSum and DCount
- Lab 1: Array and Database Functions
- Using Array Formulas
- Calculate the difference between Maximum and Minimum values
- Using Frequency function to Count responses
- Using Database functions DSum and DCount
-
Module 13: Efficiency Tips
This module discusses some useful Excel Tips including application of Data Validations and Auditing Tools.
- 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
- Lab 1: Efficiency Tips
- 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
-
Module 1: Making Data Work For You
This module explains how to understand and apply Excel basic formulas and functions.
-
Module 2: Making Data Work For You
After completing this module, students will be able to:
-
Module 3: Statistical and Logical Functions
This module explains how to use logical functions including Count If, Sum if, If, Is Error.
- 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
- Lab 1: Statistical and Logical Functions
- 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 4: Statistical and Logical Functions
After completing this module, students will be able to:
-
Module 5: Lookup and Reference Formulas
This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.
-
Module 11: Array and Database Functions Module
-
12: Array and Database Functions