 # Microsoft Excel – Formulas & Function

## About Microsoft Excel – Formulas & Function

This training demystifies formulas and some of the most challenging of the nearly 400 functions in Excel and shows how to put them to their best use. The course reviews the building-block functions, along with a few critical keyboard shortcuts that will speed up working with Excel data. It also covers on how to tabulate data with counting, statistical, and math functions, reformat data with text functions, and work with financial data using advanced formulas.

1 Day

## Who Should Attend This Course?

This course assumes a sound knowledge of the basic and intermediate features of Excel.

## What Will You Learn?

By the end of this course participants should be able to:
• Create Formulas
• Apply formulas to perform basic calculation
• Use Functions to carry out complex calculation
• Work with IF and Related Functions, Power Functions, Statistical Functions, Date Functions, Math Functions, and Lookup Functions

## Programme Outline

FORMULA AND FUNCTION TIPS AND SHORTCUTS
• Using the entire row/column references
• Copying column formulas instantly
• Converting formulas to values with a simple drag
• 3D formulas to gather data from multiple sheets
• Updating values without formulas
• Displaying and highlighting formulas
• Simplifying debugging formulas
• Enhancing readability with range names
FORMULA AND FUNCTION TOOLS
• Reviewing function basics
• Using and extending AutoSum
• Absolute and relative references
• Mixed references
IF AND RELATED FUNCTIONS
• IF logical tests and using operators
• Nested IF statements
• Using the AND, OR, and NOT functions with IF
POWER FUNCTIONS
• Tabulating information on a single criterion – COUNTIF, SUMIF, and AVERAGEIF
• Tabulating information on multiple criteria – COUNTIFS, SUMIFS, and AVERAGEIFS
STATISTICAL FUNCTIONS
• Finding the middle value – MEDIAN
• Ranking data without sorting – RANK
• Finding the magnitude data – LARGE and SMALL
• Tabulating blank cells – COUNTBLANK
DATE FUNCTIONS
• Excel date/time capabilities in formulas
• Identifying the day of the week – WEEKDAY
• Counting working days – NETWORKDAYS
• Determining a completion date – WORKDAY
• Tabulating date/time differences – DATEDIF
MATH FUNCTIONS
• Working with rounding functions
MOD
• Random number generation – RAND and RANDBETWEEN
• Converting a value between systems – CONVERT
• Using the powerful new AGGREGATE function to bypass errors and hidden data
LOOKUP AND REFERENCE FUNCTIONS (OPTIONAL)
• Looking up information – VLOOKUP and HLOOKUP
• Finding approximate or exact matches – VLOOKUP
• Nesting LOOKUP functions
• Finding table-like information within a function – CHOOSE
• Locating data – MATCH
• Retrieving information by location – INDEX
• Using MATCH and INDEX together

## Trainer

### SENTHIVALU KRISHNASAMY

Certified Assessor for Skills Training under “Jabatan Pembangunan Kemahiran” (JPK), Ministry of Human Resources, Malaysia