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:

• 23rd June  9.30-12.30pm via Zoom

Cost:

• €55 for Members of ISME
• €65 for Non Members of ISME

Please note: 2 screens are preferable for this training as it leads to a more productive session.