Training Course

Excel Formulas and Functions Indepth

PDF
Course Duration: 1 Day

Course 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.

Course 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.

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.

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
    ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
    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
    ROUND, ROUNDUP, and ROUNDDOWN functions
    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

Included with this course

    • Lunch and refreshments when training is delivered at our training venues
    • Comprehensive course materials and exercises
    • Telephone and Email Support

 

Private Training

Private course
Flexible date and location
From £745 +VAT for up to 8 delegates

  • Private Courses

    Private course
    Flexible date and location
    From £745 +VAT for up to 8 delegates
  • Call us on
    0191 377 8377
    to discuss your requirements.
    Mon-Fri 9am-5pm.
    Ask a Question
    Keep me up to date with offers and news
    By submitting this form, you consent to IT Training Solutions Ltd storing your information in order to respond to your request.
  • Excel Formulas and Functions Indepth Course Options
    Private
    Private
    Flexible course content, flexible training date(s), flexible location(s), variable course 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.
    Virtual
    Virtual
    These courses can be attended from anywhere.
  • Virtual Training

 

Course Reviews
How would you rate the course materials overall Brilliant, showed me everything I need to know going forward

L. Esh Construction, Adobe InDesign Level 1

I enjoyed the course and it was very insightful.

M.B. Rowen Homes, Excel Essentials

Would definitely recommend

L.H. Hitachi, Excel Level 1

What were the most useful aspects of the course Explanation of planning, setting objectives

S.T. Tiplady Safety Services Ltd, Train the Trainer

Covered at an appropriate pace to accommodate all learners in the group

G.T. Public course, Microsoft Power BI Desktop

Which part of the course did you find particularly valuable The visualisations were really helpful and the whole process was very easy to follow

L. HMPPS, Microsoft Power BI Desktop

Clear and understandable

S.W. Ballast Phoenix, Conflict Management

Following this training what are you going to do differently Will be leading the effort to replace a large number of reports currently done in Excel with Power BI variants to better present the data, and make reproduction far faster.

T.W. Network Rail, Microsoft Power BI Desktop

 

Private Courses

Flexible dates, location and content. All prices exclude VAT.

Other Courses In This Training Series

Excel Business Intelligence using Excel Power Tools Course Course Overview: This course is split into 3 main areas:1. Get and Transform (Power Query)2. Power Pivot - Data Models and DAX calculations3. Dashboard Creation with Pivot Tables, Charts and SlicersIt is aimed at users who want to analyse data from multiple sources and want to create effective data dashboards.Thi Excel Advanced Macros Course Course Overview: This course is designed to introduce you to the Macro environment of Excel, it will provide you with the confidence to record macros in Excel and then be able to gain a better understanding of the code and make changes to it. We even teach you some commands that cannot be recorded, such as how to sh Excel Analysing and Presenting Data with Dashboards and Pivot Tables Course Course Overview: This outline below contains an overview of a wide range of features within Excel that when used together can be used to create interactive visual reports and dashboards. The course will show delegates how these features can be used individually to present data but can also be used in combination to Excel Level 1 Course Course Overview: Microsoft Excel is a spreadsheet program that can be used for storing, organising and manipulating data.This Excel introductory course will enable delegates to gain a good general understanding of the main features of Microsoft Excel, giving them the ability to produce standard spreadsheets, create Excel Level 2 Course Course Overview: In Microsoft Office Excel Level 1, delegates created, edited, formatted, and printed basic spreadsheets. This course will enable delegates to streamline repetitive tasks and display spreadsheet data in more visually effective ways. The target students for this course are students who desire to gai Excel Level 3 Course Course Overview: This course will benefit those who are already up to speed with Microsoft Excel but would now like to automate some common tasks and apply advanced analysis techniques to more complex data sets. This Excel course is aimed at anyone desiring to gain the skills necessary to create macros, audit and an VBA Essentials using Microsoft Excel Course Course Overview: This course is aimed at those people who find they have reached their limit with the menu items of Excel and need to modify Excel to be better suited to their environment, by creating their own functions and procedures.

 

 

Please contact us to discuss your Excel Formulas and Functions Indepth course, we have many solutions available to ensure you receive the best training possible.
Your Excel course can be delivered online or in-person.  You can choose your Excel path with introduction to advanced levels. Master Excel Business Intelligence and Power Tools, VBA and more.  Learn in-person or online with our virtual classrooms.  Your course can be delivered online, at your office or at one of our training venues, it's your choice. Small course sizes. Course materials included. Flexible Excel Formulas and Functions Indepth course content. Expert trainers and tried & tested training methods. Many course materials available online using your personal training account.  You can master Excel, please contact us to book a place or request a quote for your Excel Formulas and Functions Indepth course.
Microsoft and Microsoft Excel are Registered Trademarks Excel 2013 Training. Excel 2013 Training Courses throughout the North East of England including Newcastle, Durham, Darlington, Washington.Microsoft Excel 2013 Short Courses. Microsoft Excel 2013 Professional Short Courses for Advanced Users. Advanced Excel 2013 Training.
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:8/2/2025 3:00:51 PM