Excel 2007 Power User Advanced

Excel Advanced training
Course Code: 
MSEXC2007PUADV
Duration: 
Duration: 1 day
Overview: 
Overview This course looks at complex calculations and advanced methods of data management. These advanced features allow users to automate spreadsheets and manipulate large amounts of data. Throughout the course there will be a focus on the practical application of the topics within a business context. At the end of the course the delegates will be able to manage large workbooks, use information from other workbooks or applications and create macros to automate repetitive tasks.
Prerequisites: 
Pre-requisites Delegates must have attended an intermediate level course or have gained similar knowledge in a business environment. It is essential that delegates can competently create formulae, use basic functions =SUM, =AVERAGE and perform basic formatting and editing operations.
Course Content: 

Delegates will learn how to:
Use shortcuts to move work more efficiently
Customise the worksheet and user interface
Import and consolidate data from other applications or workbooks
Manipulate and analyse data
Record simple macros to automate repetitive tasks
 
COURSE CONTENT
 
Recap of essential shortcuts
Navigation and selection shortcuts
Moving and copying shortcuts
Creating and using range names, editing range names and printing range tables
 
Using Excel functions
Advanced =IF functions (multiple conditions, and combined with lookups)
Useful functions, =ROUND, =INT, =COUNTIFS, =SUMIFS
Using date functions
Using string functions
Finding and understanding help on built in functions
Creating and using a function procedure
 
Working with data
Outlining to handle large worksheets
Using custom views
Auditing a worksheet
Using data validation
Importing data from other applications
Querying data from external sources
Consolidating data by position and category
 
Analysing data
Making use of scenario
Pivot Tables
 
Macros
Recording a simple macro
Using relative and absolute referencing in macros
Creating buttons on the worksheet
Adding macro buttons to the toolbar
Editing and creating menus