Microsoft Excel Advanced
Dates
This course is designed to give users an understanding of some of
the more advanced features and functions within Excel and the advantages
of using a powerful electronic spreadsheet. The course is entirely
'hands on', so those attending will get practice in using advanced
features of Excel to manage, automate and customize workbooks. This
Excel course also instructs users in Year 2000 best practice.
Excel 2000 Advanced
Year 2000 Best Practice • Review exercise
Custom formatting
Formatting numbers • Using custom formats • Custom number
format sections • Custom number format codes • Custom
date format codes • Formatting SpeedKeys • Using conditional
formats
Working with named ranges
Creating range names • Using the Go To command • Range
names in calculations
Importing and exporting data
Using Paste Special • Object linking and embedding • Sharing
data with other applications • Importing a text file into Excel
• Importing data from Web pages
Templates and Add-Ins
Creating workbook templates • Custom templates • Storing
templates • The default template • Loading Add-in programs
Working with multiple workbooks
Creating custom views • Creating a workspace file • Using
workspace files • Linking workbooks • Creating formulae
to link data
Printing workbooks and reports
Printing multiple worksheets • Printing multiple workbooks
• Using the Report Manager
Lookup and reference functions
Using the LOOKUP function • Using the VLOOKUP function •
Using the HLOOKUP function • Preventing LOOKUP errors
Working with Toolbars
Displaying hidden Toolbars • Floating and docking Toolbars
• Customising Toolbars • Creating a new Toolbar
Using Macros
Understanding Macros • Recording a basic Macro • Relative
references • Running a Macro • Assigning a Macro to a
Toolbar • Button Image Editor • Editing a Macro •
Macro virus protection
Creating map charts
Creating data map charts • Editing a map chart • Adding
features to a map
Auditing a worksheet
Using worksheet auditing tools • Data entry validation •
Validating the whole worksheet • Auditing data dependency •
Finding and replacing data
Working with data Lists
Setting up lists • Sorting lists • Filtering lists •
Using forms
Data management
Searching for data with advanced filters • Compiling consolidations
• Summarising data with outlines • Querying a database
• Data sources • Controlling formatting and layout •
Refreshing external data
PivotTables
Creating a PivotTable • Modifying a PivotTable • Creating
a PivotTable chart • Creating interactive PivotTables for the
Web
Using analysis tools
Goal Seeking • Using Solver • The Scenario Manager •
Scenario Summaries • Merging Scenarios
Collaborating with workgroups
Applying worksheet protection • Password protecting a file
• Changing workbook properties • Adding comments to the
worksheet • Shared workbooks • Tracking changes •
Resolving conflicts • Merging workbooks • e-mailing workbooks
for review
|
