# Excel Useful Formulas and Functions

### What's In It For Me

• AutoCalculate and Manual Calculation
• Using and Managing Defined Names
• Displaying and Tracing Formulas

### Course Overview

NTUC LearningHub Course Code: MSEUFF

Its a common need for working professionals to apply anything from simple calculations to complex formulas in their Excel spreadsheets to gather the results they need at their workplace daily.

In this one day lesson, students will also learn how to create numerical calculation and logical formulas they need to use and common useful functions such as define names to make more sense in formulas and VLOOKUP to find corresponding values faster. The course is for users at all levels, from beginners to intermediate.

### Who Should Attend

This course is intended for information workers such as decision makers, accountants, analysts and even sales managers who have frequent needs to analyze large amount of business performance information such as sales figures and volume of production.

1 Day / 7

### Course Outline

Formulas with Multiple Operators

Inserting and Editing a Function

• Insert Function dialog box
• Function Library
• Edit a function

AutoCalculate and Manual Calculation

• Use Auto Calculate and change options
• Manual formula calculation options

Defining Names

• Define a name for a cell range
• Define names with a dialog box

Using and Managing Defined Names

• Use and view defined names
• Edit and delete defined names

Displaying and Tracing Formulas

• Display formulas
• Trace precedents and dependents
• Use the Watch Window

Understanding Formula Errors

Using Logical Functions (IF)

• IF
• IFERROR

Using Database Functions (DSUM)

• DSUM
• DAVERAGE

Using Lookup Functions

• VLookup/HLookup
• Lookup
• Match
• Index

Using Text Functions

• Left
• Right
• Mid
• Search
• CONCANTENATE
• CODE

Date & Time Functions

• Today()
• Now()
• WEEKDAY
• WORKDAY
• NETWORKDAY

Math & Trig Functions

Statistical Functions

### Certification Obtained and Conferred by

Certificate of Attendance from NTUC LearningHub

### Pre-requisites

Students must be to use the keyboard/ mouse and be familiar with the Microsoft Windows Operating System. It is preferable that students also know the basics of Microsoft Excel.