Learning & Development

Home / Learning & Development / Microsoft Excel Advanced 2016
Microsoft Excel Advanced 2016

Microsoft Excel Advanced 2016

This course is aimed at experienced Excel users who need to create and manipulate more complex models using the advanced features of Excel

Course Objectives:

To create complex models involving multiple linked spreadsheets and files, analysing data using what ifs and scenarios, validating and auditing data and automating common processes

Course Outline:

What’s new in Excel 2016

  • One click forecasting Smart lookup
  • Tell me what you want to do… Quick analysis tools

Review of Intermediate level

  • If Statement / Nested If Absolute cell referencing Named ranges
  • SUMIF / SUMIFS

Lookup and Information Functions

  • Vertical Lookup (VLOOKUP) Horizontal Lookup (HLOOKUP) INDEX , MATCH & OFFSET
  • ISTEXT, ISVALUE, ISNULL, ISERROR ISNA, ISDATE, IFERROR

Summarising Data with Pivot Tables

  • Inserting calculated fields Changing value field settings Using report filter
  • Changing the scope of the data source Pivot table options
  • Using slicers for effective filtering Using timelines
  • Pivot charts

What If Analysis tools

  • Scenarios Custom views Goal seek Solver
  • Scenario manager Data tables

Advanced Filtering and sorting

  • Text filters
  • Date filters
  • Numeric filters
  • Advanced subtotals

Formulae Auditing Formula View

  • Tracing precedents
  • Tracing dependents
  • Using watch window
  • Go To Special

Protecting and Sharing

  • Sharing a file
  • Tracking changes
  • Applying data validation rules
  • Protecting cells, sheets, files
  • Password protecting a file

Working with Tables

  • Advantages of excel tables Techniques
  • Structured references

Advanced Charts

  • Saving custom charts as templates
  • Applying trend lines
  • Formatting and editing series, plot area, data points
  • New Charts in Excel 2016

Introduction to Macros

  • Displaying the Developer Tab
  • Overview and Purpose of Macros
  • Where to save macros
  • Absolute and relative recording
  • Running macros
  • Assigning to the Quick access toolbar
  • Assigning to shapes or pictures
  • Keyboard shortcuts for macros

Course Summary and Review

  • Recap of topics covered
  • Questions

Time and Date:

  • Excel Advanced – Session 1 29th November 2021 8am – 11am
  • Excel Advanced – Session 2 3rd December 2021 8am – 11am

Cost:

  • €45 for Members of ISME
  • €50 for Non Members of ISME

Members please log in to avail of your discount

45

ISME Skillnet
X
Course Form