Welcome to TED Learning!

  • 12-07, Binjai 8 Premium SOHO, No. 2 Lorong Binjai, 50450 Kuala Lumpur
  • Phone: +603-2742 1828
  • training@ted.com.my

Microsoft Excel Tips & Tricks: Functions and Formulas

By tedadmin | February 11, 2018

Introduction

Formulas are critically important to every Excel users. They are what make a spreadsheet a spreadsheet. This one-of-a-kind course delivers all the tips and techniques one needs to maximize one of the most powerful spreadsheet tools: formulas. The more you know about formulas, the better your spreadsheets will be. Hence the course is to aim to get the most out of worksheet formulas.

Course Objectives

Upon completion of this 2-day Microsoft® Excel Functions & Formulas training course, participants should be able to:

  1. Have a deep understanding on how functions work and how functions complement each others.
  2. Develop mega formulas by combining various functions in a single formula hence creating powerful spreadsheet.

Course Outline

DAY 1

MODULE 1: DATE & TIME CATEGORY FUNCTIONS

  1. TODAY
  2. NOW
  3. DATE
  4. DATEDIF
  5. DAY
  6. MONTH
  7. YEAR
  8. YEARFRAC
  9. EDATE
  10. EOMONTH
  11. WEEKDAY
  12. WEEKNUM
  13. NETWORKDAYS.INTL
  14. WORKDAY.INTL

MODULE 2: LOGICAL CATEGORY FUNCTIONS

  1. AND
  2. OR
  3. IF
  4. Nested IF
  5. IFERROR

MODULE 3: FINANCIAL FUNCTIONS

  1. SYD
  2. FV
  3. PMT
  4. RATE

MODULE 4: WORKING WITH NAMES

  1. What’s in a Name?
  2. Creating Names Using the Define Name Dialog Box
  3. Creating Names Using the Name Box
  4. Rules for Naming Names
  5. Creating Names Automatically
  6. Naming Entire Rows and Columns
  7. Creating Multisheet Names
  8. Creating a List of Names
  9. Using Names in Formulas
  10. Applying Names to Existing Formulas
  11. Deleting Names
  12. Deleting Named Cells or Ranges
  13. Redefining Names
  14. Changing Names
  15. How Excel Maintains Cell and Range Names
  16. Name Problems when Deleting Sheets
  17. The Secret to Understanding Names
  18. Naming Constants
  19. Naming Text Constants

MODULE 5: INFORMATION CATEGORY FUNCTIONS

  1. CELL
  2. IS Functions
  3. ISEVEN
  4. ISODD
  5. TYPE
DAY 2

MODULE 6: LOOKUP & REFERENCE CATEGORY FUNCTIONS

  1. CHOOSE
  2. LOOKUP
  3. VLOOKUP
  4. HLOOKUP
  5. OFFSET
  6. INDEX
  7. MATCH
  8. INDEX & MATCH

MODULE 7: TEXT CATEGORY FUNCTIONS

  1. CLEAN
  2. CONCATENATE
  3. EXACT
  4. LEFT
  5. MID
  6. RIGHT
  7. LEN
  8. FIND
  9. SEARCH
  10. REPLACE
  11. SUBSTITUTE
  12. LOWER
  13. PROPER
  14. UPPER
  15. REPT
  16. TEXT
  17. TRIM

MODULE 8: MATH CATEGORY FUNCTIONS

  1. ABS
  2. POWER
  3. SQRT
  4. EVEN
  5. ODD
  6. INT
  7. TRUNC
  8. MOD
  9. PRODUCT
  10. CEILING
  11. FLOOR
  12. ROUND
  13. ROUNDDOWN
  14. ROUNDUP
  15. SUBTOTAL
  16. SUMPRODUCT
  17. SUMIF
  18. SUMIFS

MODULE 9: STATISTICAL CATEGORY FUNCTIONS

  1. COUNT
  2. COUNTA
  3. COUNTIF
  4. COUNTIFS
  5. COUNTBLANK
  6. AVERAGEA
  7. AVERAGEIF
  8. AVERAGEIFS
  9. MODE
  10. RANK
  11. CORREL
  12. TREND
 Register Now

.

.

.

.

.

FEEL FREE TO ENQUIRE ABOUT OUR RANGE OF TRAINING COURSES