Microsoft Excel Advanced
Course Duration: 1 Day
 Printer-friendly
version
Course Objective
By the end of the course, delegates will be able to:
- Create and use
named ranges
- Make more use of inbuilt and custom functions
- Apply worksheet security
- Create scenarios and views for use in reporting
- Generate reports automatically
- Use improved techniques to speed up
data input and manipulate data
- Utilise the select special function
- Control data input by editing and
creating custom number formats and provide data validation
- Produce
data analysis by 3 or more categories easily using pivot tables
Delegate
Profile
Existing users of Excel who have attended our Excel
Intermediate course
or have equivalent knowledge.
Pre-Requisites
A good working knowledge of the principle
features of Excel
is assumed.
Structure
Each delegate has the exclusive use of a
PC and training includes extensive hands-on activity.
Course Content
Review of intermediate skills
Consolidation exercise
Creating and using named ranges
The name box
Defining, creating and selecting named ranges
Using named ranges in formulas
Advanced formulae
More Excel functions using lookup
tables
IF functions
String and error checking
Joining data via concatenation
Nesting functions
Using the paste function feature
Managing external data sources
When to import tables and when to attach
Using OLE to view data in other applications
Using hyperlinks to navigate between Microsoft Office documents
Worksheet
security
Protecting areas of the worksheet
Protecting file access with passwords
Scenarios, views and reports
Using scenarios and creating views to
use in reporting
Using the Report Manager to create reports that can be generated
automatically
Working with data
Using various techniques to speed
up data input and manipulate existing data
Transposing, pasting only formats and values
Data validation
Select special
Automated selection of specific types of data on the worksheet (errors,
text,comments)
Advanced number formatting
Controlling input by editing
and creating number formats, including conditional number formats
Excel
styles
Data analysis
Analysing and summarising data from
a list via a pivot table
Question & Answer Session
 Back
to the top
|
