Logo Loading

MIM1006_A – Microsoft Excel Basic Useful Formulas & Functions (2 Days)

OVERVIEW

These 2 days course provides students with the knowledge and skills to the usage of useful formulas and functions in Microsoft Excel 2019.

**Before attending this course, students should have experience:

A basic working knowledge of Microsoft Office Excel.

Objectives

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
  • 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
  • Understand and apply formula basics.
  • Using cell references
  • Copying formula without changing cell reference
  • Transpose formula using paste special.
  • Using nested functions
  • Understand the advantages of shortening worksheet names
  • Using Vlookup to find specific data
  • Using Hlookup to find values in rows
  • Using Match and Index to retrieve data
  • Protecting cells from amendments by others
  • Perform addition and calculate difference between two dates
  • Perform calculations with Time fields
  • Using Data validation to improve data entries
  • Using Auditing Tools for checking errors
  • Adding useful notes by commenting worksheet
  • 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

Duration

  • Typically spans 2 days (9am to 5pm).
  • Nonetheless, we can customize both the program’s duration and schedule to cater to unique client requirements (e.g., compact 1-day workshops or extended sessions beyond 3 days).

Target Audience

  • Intended for users of Microsoft Office Excel who want to learn about useful formulas and functions
  • Entry-Level Data Analysts
  • Administrative Professionals
  • Finance and Accounting Professionals
  • Sales and Marketing Analysts

PROGRAM OUTLINE

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

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

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

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

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

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

PROGRAM METHODOLOGY

  • Hands-on Activities: Practical exercises to reinforce theoretical concepts
  • Group Discussions: Opportunities for peer-to-peer learning and exchange of ideas
  • Feedback Sessions: Reviews and reflections to encourage improvement
  • Problem-solving Exercises: Develop critical thinking and decision-making skills
  • Experiential Learning: Learning by doing, promoting active involvement
  • Interactive Lectures: Engaging presentations by experts in the field
  • Case Studies: Real-world scenarios for learners to apply their knowledge

CONTACT US

Our Experts Are Here to Help