Courses

Excel Essentials

Excel Intermediate

Excel Advanced

Prerequisite: No previous experience with Excel is required, but a good working knowledge of Windows is essential.

Prerequisite: Completion of Excel Essential skills (Basic), or a very good working knowledge of Excel.

Prerequisite: Completion of Excel Intermediate, or a very good working knowledge of Excel.

Duration: 12 hours (4 x 1/2 days)

Duration: 12 hours (4 x 1/2 days)

Duration: 12 hours

Overview 

  • Start Excel 

  • Excel window basics 

  • Workbook and window views

 

Basic data entry 

  • Create workbooks 

  • Save workbooks 

  • Enter and edit basic data in a worksheet  

 

Basic formatting

  • Insert and delete cells 

  • Manual formatting 

  • Use the format painter 

 

Basic formulas 

 

    • Structure calculations

    • Understand the difference between functions and formulas 

     

    Introduction to functions 

    • Summarise data with functions:

      • SUM

      • AVERAGE

      • MIN

      • MAX

    • Absolute referencing

     

    Work with rows and columns 

    • Manage rows and columns 

    • Use themes 

     

    Printing & Backstage 

    • Print with Backstage 

    • Change the Excel environment 

     

    Workbook management 

    • Organise worksheets 

    • Use Zoom 

    • Find and replace data 

     

     

    Advanced formatting

    • Conditional formatting
    • Work with styles
    • Hyperlinks

     

    Data management 

    • Import data 

    • Sort data 

    • Filter data 

    • Outline and subtotal data 

    • Flash fill 

    • Tables

     

     Lesson 10: Use logic and lookup functions

    • Name ranges and objects

    • Use formulas to conditionally summarise data 

      • SUMIF

      • COUNTIF

      • AVERAGEIF

    • Add conditional logic functions to formulas 

      • IF

      • AND

      • OR

    • Use formulas to lookup data

      • XLOOKUP*

      • Exact match*

      • Multiple match*

      • Two-way lookup*

     

    Lesson 11: Create charts 

    • Build charts 

    • Manually format parts of a chart

    • Modify a chart 

    • Use quick analysis tools 

    • Insert and format sparklines

    • Add funnel charts*

     

    Lesson 12: Pivot Tables and PivotCharts

    • Create and modify PivotTables

    • Create and modify PivotCharts

    • View recommended PivotTables*

    • Use Ideas*

    Lesson 13: Manage and Protect workbooks 

    • Manage workbooks 

    • Review and protect workbooks 

     

    Lesson 14: Apply custom formatting and layouts 

    • Apply custom formats and validate data 

    • Apply conditional formatting and filtering

     

    Lesson 15: Use advanced functions and analyse data 

    • Create advanced logic functions

      • Nested IF

      • IFS

      • SUMIFS

      • COUNTIFS

      • AVERAGEIFS 

    • Troubleshoot formulas and functions 

    • Use data analysis and business intelligence tools 

     

    Lesson 16: Work with advanced charts and PivotTables 

    • Create advanced charts 

    • Take an indepth look at PivotTables

    * new features added

    Workshops

    We recommend that you bring along your workbooks and questions for an interactive session.

    Excel Formula Magic 

    Excel PivotTable 

    Excel Marks processing 

    Excel Data Analysis 

    Prerequisite: Completion of Excel Advanced course 

    Prerequisite: Completion of Excel Advanced course 

    Prerequisite: Completion of Excel Essentials skills  

    Prerequisite: Completion of Excel Intermediate course or a very good working knowledge of Excel 
    Duration: 3 hours   Duration: 3 hours   Duration: 3 hours 

    Duration: 3 hours 

    Names ranges 

    • Recap using named ranges 

    • Change a named range's size  

     

    Introduction to array functions 

    • Perform calculations using array functions 

    • Examine the advantages and disadvantages of array functions 

    • Create single and multi-cell array formulas 

     

    Text and date formulas 

    • Use the date function to calculate duration 

    • Manipulate text using text functions 

    Organise worksheet and table data

    • Create and modify tables

    •  Format tables

    • Sort/filter data

     

    Pivot Tables

    • Create a PivotTable to analyse worksheet data

    • Filter data using Slicers

    • Analyse data using Pivot Charts

    • Adjust the layout

    • Manage subtotals and grand totals

    • Add calculated columns & fields

    • Apply conditional formatting

    Names ranges 

    • Sort data

    •  Apply filters to data

    • Manage tables

    • Work with functions and formulas

      • Specialised functions (IF Function, VLOOKUP)

    • Maintain data with conditional formatting

    • Use advanced paste options

    • Save worksheets as different file types

    • Clean up your data

    • Sort and filter data

    • Use data analysis formulas 

    • Create and manipulate PivotTables

    • Format and conditional formatting in PivotTables

    • Introduce PowerPivot and data model and Data Analysis Expressions (DAX)