We use cookies to provide you with the best possible experience. Please carry on browsing if you're happy with this or click here to find out more about how we use cookies and how to manage your preferences.

Contact us online or call us on 0191 377 8377

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields
Course Excel Formulas and Functions Indepth
  • Public Scheduled Price£195 +VAT
  • Course Duration1 Day
  • Course Options
    Public Scheduled
    Public Scheduled
    Fixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!
    Private
    Private
    Flexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
    Bespoke
    Bespoke
    Bespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.
Overview
This course is designed to help users get the most from the advanced functions in Excel. Advanced Lookup and Reference functions to help you gather data, Text functions to organise and validate the data, Logical functions to analyse the data. You will use the advanced features of Microsoft Excel to attain a high degree of proficiency as an Excel power user within the area of writing functions.
Prerequisites
    • Delegates should have advanced knowledge of Microsoft Excel to get the most from this course. We recommend that delegates attend our Excel Level 1, 2 and 3 courses or have equivalent knowledge.
Objectives
    • Understand and apply complex logical functions such as nested IF's and incorporate other logical functions such as AND and OR.
    • Create a formula that will only add up or count records that match specific criteria.
    • Confidently use and manipulate VLOOKUPs and understand the advantages of the MATCH and INDEX lookups.
    • Manipulate Text Functions to organise, parse, join data.
    • Use Arrays within formulas and functions to perform multiple calculations on one or more of the items.
What's included
    • Laptops are available for training delivered at client's premises
    • Lunch and refreshments when training is delivered at our training venues
    • Comprehensive course materials and exercises
    • Telephone and Email Support
Course Content
    • Advanced Logical Functions
      IF logical tests
      Expanding nested IF statements
      AND, OR, and NOT functions with IF
      Using IFS for multiple conditions
    • Text Functions
      Locating and extracting data with the FIND, SEARCH, and MID functions
      Extracting specific data with the LEFT and RIGHT functions
      Using the TRIM function to remove unwanted spaces in a cell
      Using ampersands and CONCATENATE to combine data from different cells
      Adjusting character content with the REPLACE and SUBSTITUTE functions
    • Information Functions
      Extracting information with the CELL and INFO functions
      Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
      Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
      Tracking and highlighting formula cells with the ISFORMULA function
    • Advanced Lookup and Reference Functions
      Looking up information with VLOOKUP and HLOOKUP
      Finding approximate matches with VLOOKUP and HLOOKUP
      Finding exact matches with VLOOKUP
      Nesting lookup functions
      Using VLOOKUP with large tables
      Finding table-like information within a function using the CHOOSE function
      Using the SWITCH function for formula-embedded selection
      Locating data with the MATCH function
      Using MATCH and INDEX functions together
    • Statistical Functions
      Using SUBTOTAL
      Finding the middle value with MEDIAN and most common value with MODE
      Ranking data without sorting with RANK and RANK.EQ
      Finding the largest and smallest values with the LARGE and SMALL functions
      Tabulating blank cells with the COUNTBLANK function
      Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
      Working with MROUND, CEILING, and FLOOR for specialised rounding
      Using the powerful AGGREGATE function to bypass errors and hidden data
    • Array Formulas and Functions
      Extending formula capabilities with array formulas
      Counting unique entries in a range with an array formula
      Determining frequency distributions with the FREQUENCY function
    • Reference Functions and Advanced Data Validation
      Getting data from remote cells with the OFFSET function
      Returning references with the INDIRECT function
      Using INDIRECT with Data Validation for two-tiered pick lists
    • Formula and Function Tips and Shortcuts
      Displaying and highlighting formulas
      Using Auditing tools
      Converting formulas to values with a drag
      Using Named Ranges and Tables

 

 

 

 

 

Contact us

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields