Tips & Tricks in Microsoft^® Excel Functions & Formulas

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.

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

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

Module 1: Date & Time Category Functions

• TODAY
• NOW
• DATE
• DATEDIF
• DAY
• MONTH
• YEAR
• YEARFRAC
• EDATE
• EOMONTH
• WEEKDAY
• WEEKNUM
• NETWORKDAYS.INTL
• WORKDAY.INTL

Module 2: Logical Category Functions

• AND
• OR
• IF
• Nested IF
• IFERROR

Module 3: Financial Functions

• SYD
• FV
• PMT
• RATE

Module 4: Working with Names

• What's in a Name?
• Creating Names Using the Define Name Dialog Box
• Creating Names Using the Name Box
• Rules for Naming Names
• Creating Names Automatically
• Naming Entire Rows and Columns
• Creating Multisheet Names
• Creating a List of Names
• Using Names in Formulas
• Applying Names to Existing Formulas
• Deleting Names
• Deleting Named Cells or Ranges
• Redefining Names
• Changing Names
• How Excel Maintains Cell and Range Names
• Name Problems when Deleting Sheets
• The Secret to Understanding Names
• Naming Constants
• Naming Text Constants

Module 5: Information Category Functions

• CELL
• IS Functions
• ISEVEN
• ISODD
• TYPE

Module 6: LookUp & Reference Category Functions

• CHOOSE
• LOOKUP
• VLOOKUP
• HLOOKUP
• OFFSET
• INDEX
• MATCH
• INDEX & MATCH

Module 7: Text Category Functions

• CLEAN
• CONCATENATE
• EXACT
• LEFT
• MID
• RIGHT
• LEN
• FIND
• SEARCH
• REPLACE
• SUBSTITUTE
• LOWER
• PROPER
• UPPER
• REPT
• TEXT
• TRIM

Module 8: Math Category Functions

• ABS
• POWER
• SQRT
• EVEN
• ODD
• INT
• TRUNC
• MOD
• PRODUCT
• CEILING
• FLOOR
• ROUND
• ROUNDDOWN
• ROUNDUP
• SUBTOTAL
• SUMPRODUCT
• SUMIF
• SUMIFS

Module 9: Statistical Category Functions

• COUNT
• COUNTA
• COUNTIF
• COUNTIFS
• COUNTBLANK
• AVERAGEA
• AVERAGEIF
• AVERAGEIFS
• MODE
• RANK
• CORREL
• TREND

• Using Formulas & Functions in Microsoft® Excel 2007/2010
• Maximizing Microsoft® Excel Tools for Fast & Accurate Business Reporting
• Maximizing Pivot Table in Microsoft® Excel 2007/2010
• Microsoft® Excel VBA for Non-Programmers
• Microsoft® Excel for Intermediate/Advance User
• More other Information Technology related courses