MIRACLES OF EXCEL FORMULAS AND FUNCTIONS

WHO IS THIS COURSE FOR?

PRE-REQUISITES

• Able to switch between task applications
• Able to Create, Save, Open and Print spreadsheet
• Able to use the Undo function
• Insert and delete Row & Column
• Print a spreadsheet with headers and footer
• Insert, Delete and Rename a worksheet
• Able to create basic formulas like addition, substraction, multiplication and division
• Able to use basic functions like Sum, Average, Count, Max and Min functions

KEY CONTENTS

Unit 1: Cell and Range References

• Creating an Absolute Reference

Unit 2: Statistical Functions

• COUNTIF Function
• COUNTBLANK Function
• RANK Function

Unit 3: Math and Trig Functions

• SUMIF to add data that satisfy condition
• ROUND Function
• ROUNDUP Function
• ROUNDDOWN Function

Unit 4: The Wonders of Text Functions

• Changing the case of Text
• Combining Text from Multiple Columns
• Breaking Date Field into individual day, month and year
• Trimming off excess spaces in a cell
• Replacing existing data with new value in a cell using SUBSTITUTE Function
• Convert value to text with TEXT Function

Unit 5: Logical Functions

• Hiding formula error with IFERROR Function
• Using IF Function to make decision
• AND Function
• Create multiple condition using Nested IF
• Nested IF & AND Function

Unit 6: Lookup & Reference Formulas

• Use VLOOKUP to find specific data
• VLOOKUP array formula
• Use MATCH to match data
• Use INDEX to retrieve data
• Nested INDEX & MATCH

Unit 7: Date & Time Formulas

• Date calculation
• Add a number to current date to get new dates
• Calculate duration between two dates

METHODOLOGY