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.

Experience Required

This course presumes that the user has a basic knowledge of Windows, including concepts such as using the mouse and keyboard. With respect to Excel, we also presume that the user has completed the Intermediate level of this course (or has equivalent knowledge) and is familiar with concepts such as:

  • Using right-click menus
  • Working with dialog boxes
  • Worksheet organisation (rows, columns, labels)
  • Cutting, copying, pasting, selecting, and moving Excel data
  • Basic cell data formatting tasks
  • Working with cell references
  • Manipulating multiple open worksheets and/or workbooks
  • Creating and manipulating standard Excel data tables
  • Using Excel functions (in particular the IF function)
  • Building formulas in Excel
  • Creating and manipulating standard Excel charts
  • Understand of Absolute and Relative referencing

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