Microsoft Excel 2016 Advanced Training Course Outline

Audience: This course is aimed at experienced Excel users wishing to further their Excel skills. Majoring on Pivot Tables and recorded macros we explore many other of Excel’s great productivity features like auditing, protecting and linking workbooks and What-If analysis.

Course Duration: 1 day 

Course Availability: Monday – Saturday

Course Types: Group bookings, public courses, 1-2-1 sessions, bespoke tailored courses and office migrations.

Course Location: This Microsoft Office Excel 2016 Advanced Training Course can be delivered either at our London training venue or at your offices. 

Tailored Course Content: Course content can be customised to meet your specific requirements, with scheduled dates to suit you.

At Course Completion

After completing this course, students will be able to:

  • Audit a worksheet
  • Link worksheets and workbooks
  • Perform What if Analysis
  • Protect and Outline Excel documents
  • Collaborate with other Excel users
  • Import and Export Data
  • Filter and summarise worksheet data
  • Analyse worksheet data with Pivot Tables
  • Record macros

Course Outline

Module 1: Auditing a worksheet

  • Tracing precedents and dependents
  • Tracing errors in the worksheet
  • Error checking
  • Watch window

Module 2: Linking worksheets and workbooks

  • Setting up related worksheets within a workbook
  • Entering a formula to link related worksheets and workbooks
  • Creating a workspace
  • Consolidate worksheets and workbooks

Module 3: What if Analysis

  • Goal seek
  • 1 way and 2 way Data Tables
  • Scenario Manager

Module 4: Protection and Outlining

  • Protecting worksheets and workbooks
  • Grouping and outlining a worksheet

     

Module 5: Working with other Excel users

  • Create a shared workbook
  • Tracking changes in a shared workbook
  • Responding to proposed changes and comments in a shared workbook
  • Working with version History (new)

Module 6: Importing and Exporting Data

  • Importing data from a text or web based file
  • Working with other applications such as Access
  • Get and Transform (new)
Module 7: Filtering and summarising worksheet data
  • Use the Quick analysis tool
  • Autofilter and advanced filter
  • Adding a criteria range with advanced filter
  • Database functions
  • Using subtotals and nested subtotals
  • One click forecasting
  • 3D Maps (new)
  • Ink Equations (new)
  •  Module 8: Pivot Tables
  • What is a Pivot Table?
  • Best practice on setting up Pivot Tables
  • Creating a Pivot Table
  • Resetting the summary function in a Pivot Table report
  • Grouping Pivot Table data
  • Search in the Pivot Table (new)
  • Formatting a Pivot Table
  • Charting a Pivot Table
  • Printing the Pivot Table
  • Normalising Pivot Table data sources
  • Convert Ranges to Tables
  • Using other Microsoft data sources with Pivot Tables
  • Using Slicers and TimeLines to filter Pivot Table data (new)
  • Power Pivot and Power View (new)

Module 9: Recording macros

  • Macro guidelines
  • Macro security settings
  • Recording and deleting macros
  • Running macros
  • Assign a macro to the QAT, ribbon or shortcut keys
  • Recording relative and absolute cell selection
  • Using AutoFill
  • Using absolute and relative references

Module 6: Printing your worksheet

  • Previewing a worksheet
  • Changing the Page setup of a worksheet
  • Setting the print area
  • Printing certain rows or columns on each page
  • Printing selected worksheet entries

Module 7: Creating a chart

  • Plotting a chart
  • Use Recommended Charts (new)
  • Previewing and printing a chart
  • Adding new data to an existing chart
  • Including multiple data ranges in a chart
  • Uses of the different chart types
  • Creating Sparklines

Module 8: Controlling the worksheet display

  • Splitting the workbook window into panes by freezing panes
  • Hiding rows and columns
  • Changing the zoom level for a worksheet
  • Sorting and filtering worksheet dat
, Microsoft Excel Advanced 2016 Training Course Outline
, Microsoft Excel Advanced 2016 Training Course Outline