Home > Services > Training > Microsoft Office training > Excel 2016 courses

Excel 2016 courses

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

Duration: 12 hours

Duration: 12 hours

Lesson 1: Overview 

  • Start Excel 

  • Work in the Excel window 

  • Change workbook and window views 

  • Work with an existing workbook 

 

Lesson 2: Work with Excel 2016 

  • Create workbooks 

  • Save workbooks 

  • Enter and edit basic data in a worksheet 

  • Use data types to populate a worksheet 

  • Cut, copy, and paste data 

  • Edit a workbook’s properties 

 

Lesson 3: Use Office Backstage 

  • Print with Backstage 

  • Change the Excel environment 

  • Access and use Excel templates 

 

Lesson 4: Use basic formulas 

  • Understand and display formulas 

  • Use cell references in formulas 

  • Use external cell references 

 

Lesson 5: Use functions 

  • Summarise data with functions 

 

Lesson 6: Format cells and ranges 

  • Insert and delete cells 

  • Manually format cell contents 

  • Copy cell formatting with format painter 

  • Understand paste special options 

  • Format cells with styles 

  • Work with hyperlinked data 

  • Apply conditional formatting to cells 

 

Lesson 7: Format worksheets 

  • Work with rows and columns 

  • Use themes 

  • Insert headers and footers 

  • Prepare a document for printing 

 

Lesson 8: Manage worksheets 

  • Organise worksheets 

  • Use Zoom and Freeze to change the onscreen view 

  • Find and replace data 

 

Lesson 9: Work with data 

  • Import data 

  • Sort data 

  • Filter data 

  • Outline and subtotal data 

  • Split data and flash fill 

  • Set up data in a table format 

 

 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

 

Lesson 11: Create charts 

  • Build charts 

  • Manually format parts of a chart

  • Modify a chart 

  • Use quick analysis tools 

  • Insert and format sparklines

 

Lesson 12: PivotTables and PivotCharts

  • Create and modify PivotTables

  • Create and modify PivotCharts

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

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

 

PivotTables

  • 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)