Microsoft Excel Advanced

Microsoft Excel Advanced 1 Day Course Outline and Content
Style of Training
A structured, hands-on, tutor-led session using practical examples and exercises.
Course Aims and Objectives
This course is designed to show delegates tools and techniques at the higher end of Excel's capabilities.
Who should attend?
1. Users who have previously attended our intermediate course (or a similar level course).
2. Self-taught users who are comfortable with the majority of topics covered on our intermediate course.
3. Advanced users who need refreshing on the Excel's advanced functions and techniques.
Prerequisites
Delegates attending this course should have considerable experience using the Excel's core functions and a good understanding of the topics covered on our Intermediate course.
Course Content
A typical course at this level would cover the following topics:

Review & Refresh Exercise		Formatting Options
Essential skills - best practice		Advanced formatting options
Essential formulae - best practice		Conditional formatting - multiple conditions
Manipulating Text		Date and Time Calculations
Convert text case to upper lower & proper		Calculating with dates and times
Merge text from separate cells into one cell		Inserting a dynamic date
Remove unwanted spaces		Using formulae to find day - month - year
Count the number of entries in a range		Custom formatting times
Useful Formulae and Functions		Data Manipulation
Using the SUMIF function		Setting up data tables - best practice
The COUNTIF function		Advanced sorting / Filtering techniques
Using ROUNDUP and ROUNDDOWN		Creating & customising Pivot Tables
		Subtotalling lists - subtotalling options
Linking Worksheets and Workbooks		Amending, refreshing, grouping Pivot Tables
Linking cells - paste links
Consolidating Data		Data Validation
		Controlling data entry in a specified range
Using If Statements		Creating input messages and error alerts
Simple if statements
Multiple if statements		Scenarios
Nested / AND if statements		Scenarios - changing circumstances
		Viewing a scenario - amending scenarios
		Creating a scenario summary
Lookup Tables		Protection and Passwords
Naming cell ranges		Hiding rows, columns and worksheets
What are lookup tables		Password protecting a workbook / sheet
Creating a lookup table using Vlookup		Password protecting cells and ranges
Absolute Cell Referencing		Macros
Formulae using Absolute cell referencing		What is a Macro? - Creating a Macro
		Running an existing Macro
Goal Seeking		Deleting / editing an existing Macro
What is goal seeking?
Using goal seeking to find results		Auditing a spreadsheet
		Viewing all formulae on a spreadsheet
		Tracing precedents - tracing dependants