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.
Course Duration
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
- Reviewing function basics
- Using and extending AutoSum
- Absolute and relative references
- Mixed references
- IF logical tests and using operators
- Nested IF statements
- Using the AND, OR, and NOT functions with IF
- Tabulating information on a single criterion – COUNTIF, SUMIF, and AVERAGEIF
- Tabulating information on multiple criteria – COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding the middle value – MEDIAN
- Ranking data without sorting – RANK
- Finding the magnitude data – LARGE and SMALL
- Tabulating blank cells – COUNTBLANK
- 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
- Working with rounding functions
- Random number generation – RAND and RANDBETWEEN
- Converting a value between systems – CONVERT
- Using the powerful new AGGREGATE function to bypass errors and hidden data
- 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
About Us
Elev8 Asia Sdn Bhd is a HRDF Certified Training Provider in Malaysia which founded in year 2015.
Services
- Corporate Training
- Competency Assessment
- e-Learning
- Platform Development
Help
- Privacy Policy
- Contact Us
- News