Course Icon Excel Training

Advanced Excel Formulas and Functions

Designed for Excel versions 2016,2013,2010,2007

0800 1337 401

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
See all of the available Seven Institute Excel Courses