Excel Training
Advanced Excel Formulas and Functions
Course Objectives
Welcome to Seven Institute’s course for Microsoft Office Excel, a popular spreadsheet application designed for organising and analysing data, performing calculations, and charting information.
This advanced Excel Formulas and Functions level is intended to help regular users of Excel become even more proficient by expanding their knowledge of Excel’s function library. This course will also help experienced users nest these functions to solve common problems.
Please note some functions may not be available in earlier versions of Excel.
Who Should Attend
Experienced Excel users who want to take their knowledge to the next level. This course is ideal for anyone that works with tables, financial data, or imports data from external sources such as databases or websites.
Course Syllabus
EXPLORING EXCEL FUNCTIONS
- The insert function box
- The function library
- Defining name ranges
FINANCIAL FUNCTIONS
- Net Present Value: =NPV()
- Future Value: =FV()
- Periodic payment required to amortise a loan over a number of periods: =PMT*()
- Interest part of an individual payment made to repay an amount over a time *period: =IPMT()
- The number of periods required to amortise a loan: =NPER()
- Straight-line depreciation for an asset: =SLN()
LOGICAL FUNCTIONS
- The IF function - checks whether a condition is met, and returns one value *if TRUE and another value if FALSE
- The AND function - returns TRUE if all conditions are true and returns FALSE if any of the conditions are false
- The OR function - returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false
- The NOT function - reverses a functions result
TEXT FUNCTIONS
- FIND, SEARCH, LEFT, RIGHT, MID
- UPPER, LOWER, PROPER
- REPLACE, SUBSTITUTE, TRIM
DATE & TIME FUNCTIONS
- TODAY, NOW, DAY, MONTH, YEAR
- NETWORKDAYS, WEEKDAY, DATEDIF
- Use date functions within a database
- Working with dates in Pivot Tables
LOOKUP & REFERENCE FUNCTIONS
- CHOOSE, COLUMNS, ROWS
- VLOOKUP, HLOOKUP, MAXTCH
- TRANSPOSE
MATH & OTHER FUNCTIONS
- SUMIF, COUNTIF, AVERAGEIF
- SUMIFS, COUNTIFS, AVERAGEIFS
- SUM, SUBTOTAL, AGGREGATE
- ROUND, ROUNDUP, ROUNDDOWN
- MEDIAN, MOD, RANK, LARGE, SMALL, MAX, MIN
IS & ERROR FUNCTIONS
- ISODD, ISEVEN, ISBLANK, ISNOTEXT, ISTEXT, ISNUMBER
- ISNA, IFERROR, ISERR, ISERROR
NESTING FUNCTIONS
- VLOOKUP with MATCH
- NESTED IF FUNCITONS
- IF with AND/OR
- INDEX with MATCH (alternative to a VLOOKUP)
- NEST TEXT FUNCTIONS