Course Outline - Microsoft Teams for End Users

    Microsoft Excel Intermediate

    Course Duration: One Day

    Target Audience:

    Aimed at users who have basic experience with Excel and want to build on their current knowledge to set up their own spreadsheets and manipulate existing ones.

    Course Objectives:

    To develop a good working knowledge of Excel to include features such as functions and formulae, sorting and filtering data, working with charts and pivot tables.

    Course Outline:

    Review Concepts

    • Absolute, Relative References
    • Standard calculations

    Excel Features

    • One click Forecasting
    • Smart Lookup
    • Tell me what you want to do…
    • Quick Analysis Tools

    Naming Cell Ranges

    • Concept And Purpose
    • Naming Individual Cells
    • Naming Range Of Cells
    • Deleting And Amending Named Ranges
    • Using Name Ranges in Formulae/Functions
    • Named Ranges As Navigation Aid

    Linking Sheets and Files

    • Changing the default number of workbook sheets
    • 3D Calculations
    • Linking sheets in the same file
    • Linking different Excel files
    • Using Edit, Links
    • Viewing Different Files on One Screen
    • Window Split / Freeze Panes
    • Viewing Different Parts of the Sheet at once
    • Custom Views

    Logical Functions

    • IF Statements
    • Nested If
    • SUMIF / SUMIFS
    • AVERAGEIF / AVERAGEIFS
    • COUNTIF / COUNTIFS
    • ISERROR, IFERROR, IFNA
    • Nesting IF with AND & OR

    Applying and Managing Conditional Formatting

    • Data Bars
    • Colour Scales
    • Icon Sets
    • Top/Bottom
    • Creating Formula based Conditional formatting

    Lookup Functions

    • Vertical Lookup (VLOOKUP)
    • Horizontal Lookup (HLOOKUP)

    Database / List Management

    • Sorting Data (By Values, By Cell Colour, By Font Colour, By Cell Icons)
    • Multi Column Sort
    • Filter (By Values, Cell Colour, Font Colour, Cell Icons)
    • Filter (By Values, Cell Colour, Font Colour, Cell Icons)
    • Multi Column Filter
    • Advanced Filter
    • Adding Subtotals
    • Freeze Panes
    • Group and Outline
    • Data Form
    • Format as Table Feature
    • Data Validation

    Formulae Auditing Formula View

    • Tracing Precedents
    • Tracing Dependents
    • Using Watch Window
    • Go To Special…

    Charts / Graphs Advanced Techniques

    • Using Recommended charts
    • Creating Chart Using Shortcut Keys
    • Setting Chart as Default
    • Area, legends, etc.
    • Using Sparklines (Line, Column, Win/Loss)
    • Creating a secondary axis

    Pivot Tables

    • Creating a Pivot Table
    • Updating the Table
    • Changing the Table Structure
    • Formatting the Pivot Table
    • AutoFormats
    • Creating Charts from the Data
    • Calculated Items
    • Using Get Pivot data

    Protection

    • Protection – Cells / Sheets / Files

    Discussion and Question Time

    View Schedule
    Contact Us