50449-B: Useful Formulas & Functions (Microsoft Excel 2007 & 2010)

Course Modality

Instructor-led (classroom)

Course Level

Intermediate

Course Time

1 day

Course Language

English

Course Overview

This 1 day course provides students with the knowledge and skills to the usage of useful formulas and functions in Microsoft Excel 2007 and Microsoft Excel 2010.

Prerequisites

Before attending this course, students must have:

  • A basic working knowledge of Microsoft Office Excel

Intended Audience

This course is intended for users of Microsoft Office Excel who want to learn about useful formulas and functions.

Why The DataTech Labs ?

Self-Paced Online Video

A 360-degree learning approach that you can adapt to your learning style

A 360-degree learning

Engage and learn more with these live and highly-interactive classes alongside your peers

24/7 Teaching Assistance

24/7 Teaching Assistance Keep engaged with integrated teaching

Online Practice Labs

Projects provide you with sample work to show prospective employers.

Applied Projects

Real-world projects relevant to what you’re learning throughout the program

Learner Social Forums

A support team focused on helping you succeed alongside a peer community

Skill Covered

After completing this course, students 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

Get In Touch

Course Curriculum

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
  • Use cell references
  • Copy formula without changing cell reference
  • Transpose formula using paste special
  • Use nested functions

 

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
  • Use If function to prevent division by zero
  • Use IsError function to avoid error display
  • Create multiple conditions using nested IF
  • Use logical function OR, AND

: Lookup and Reference Formulas

This module explains how to apply and use lookup formulas including vlookup, hlookup, match and index.

Lessons

  • Use Vlookup to find specific data
  • Use Hlookup to find values in rows
  • Use Match and Index to retrieve data

After completing this module, students will be able to:

  • Use Vlookup to find specific data
  • Use Hlookup to find values in rows
  • Use Match and Index to retrieve data

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:

  • Change 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

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

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:

  • Use Array Formulas
  • Calculate the difference between Maximum and Minimum values in an Array
  • Use Frequency function to Count responses in tabulated data
  • Use Database functions DSum and DCount

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
  • Protect cells from amendments by others
  • Use Data validation to improve data entries
  • Use Auditing Tools for checking errors

Add useful notes by commenting worksheet

Reviews

Get in touch, enquire now!


By submitting this form, I consent to the processing of the personal data that I provide The Data Tech Labs Inc. in accordance with and as described in the Privacy Policy.

© 2020 The Data Tech Labs Inc. All rights reserved.

TDTL-arrow
[glt language="Arabic" label="Arabic" image="yes" text="yes" image_size="24"]
[glt language="English" label="English" image="yes" text="yes" image_size="24"]
[glt language="French" label="French" image="yes" text="yes" image_size="24"]
[glt language="German" label="German" image="yes" text="yes" image_size="24"]
[glt language="Hindi" label="Hindi" image="yes" text="yes" image_size="24"]
[glt language="Marathi" label="Marathi" image="yes" text="yes" image_size="24"]
[glt language="Spanish" label="Spanish" image="yes" text="yes" image_size="24"]

Microsoft Power Platform App Maker

Designing & Implementing Azure AI Solution

Microsoft Azure Administrator

Developing Solutions For Microsoft Azure

Microsoft Azure Architect Design Exam

Implementing Azure Data Solution

Administering Relational Databases On Microsoft Azure