Excel Advanced

Duration:

One Day

Aims:

This course is designed to provide delegates with the knowledge and skills needed to handle the advanced features of Excel. In particular, delegates will learn how to use advanced If functions and customise Excel using data validation, protection and templates, how to automate their spreadsheets with macros, and how to manipulate, consolidate and chart data using the various techniques, functions and facilities available with Excel.

Prerequisites:

Delegates must have a good working knowledge of Excel. They must be regular users who are able to create, edit, format and print Excel spreadsheets. They must be able to use basic calculations, functions and charts. It is an advantage to have attended our Excel Introduction and Intermediate courses.

Revision
  • Excel Intermediate
IF’s
  • Count IF
  • Conditional IF
  • Nested IF
  • Sum IF
  • Conditional Sum Wizard
Data Validation & Protection
  • Data validation
  • Protecting ranges & files
Toolbars
  • Creating custom toolbars
  • Toolbar configuration files
Macros
  • Storing macros
  • Recording a macro
  • Running a macro
  • Assigning macros to custom tools, buttons & menus
  • Editing a macro
Goal Seek
  • Changing a single cell
  • Applying a global change
Scenario Manager
  • Creating scenarios
  • Editing scenarios
  • Showing scenarios
Auditing
  • Auditing Features
  • Precedents and Dependants
  • Tracing Errors & Invalid Data
Consolidating Data
  • Working with multiple sheets
  • Linking files with formulas
  • Paste special
  • Data consolidation
Data Manipulation
  • Sorting data
  • Using subtotals
  • AutoFilter
  • Advanced filter
Pivot Tables
  • Using the pivot table wizard
  • Editing the pivot table
  • Drilling down in a pivot table
Advanced Calculations
  • Lookup functions
  • Conditional Sum Wizard
  • Conditional calculations
Advanced Charting
  • Creating combination charts
  • Adding & removing data
  • Adding trend lines
  • Forecasting forward
Advanced Functions
  • Mail Merge – Letters
  • Mail Merge – Envelopes
  • Mail Merge Labels
  • Linking to Word Documents