Excel 2003 Advanced
Course Information
This one day course is designed to enhance existing spreadsheet skills and is particularly valuable to those handling data storage in Excel.
Delegates will learn to manage and summarise data files, perform multi sheet and cross-book calculations and apply security mechanisms to protect data
Pre-requisites
Delegates should have attended the ‘Essential Skills’ and the ‘Intermediate’ courses or have the equivalent knowledge through their own usage of the product.
Course Objectives
By the end of this course delegates will be able to:
- Group worksheets together to perform multi-sheet editing.
- Calculate using multi-sheet and cross-book references in formulae.
- Summarise information using data consolidation.
- Sort data in alphabetical, numerical or customised order.
- Search for data using auto filtering and advanced filtering techniques.
- Apply automatic subtotalling to the worksheet.
- Apply outlining to collapse and expand levels of detail on the worksheet.
- Summarise data using Pivot Tables.
- Share workbooks to enable multi-user simultaneous editing.
- Track and manage changes on shared workbooks.
- Save the worksheet and components as HTML Documents for use on the web.
- Apply protection to secure formulae, graphics and worksheets.
What you will learn:
Lesson 1 – Sorting and Filtering
Sorting by icon and by menu
Sorting using custom lists
Auto Filtering
Advanced Filtering
Lesson 2 – Outlining and Subtotals
Applying and removing the auto outline
Applying and removing automatic subtotals
Lesson 3 – Pivot Table Reports
Create and manipulate a pivot table
Pivot Charts
Lesson 4 – Linking and Grouping
Group Worksheets
Create formula links to cells on different worksheets
Create formula links to cells on different workbooks
Lesson 5 – Data Consolidation
Consolidate data in the same workbook
Consolidate data from different workbooks
Lesson 6 – File Handling
Apply / remove a password to open
Apply / remove a password to modify
Protecting cells within a workbook
Data validation
Finding files.
Lesson 7 – Shared Workbooks
Sharing the workbook
Checking user status
Tracking changes
Resolving conflicts
History Worksheet
Lesson 8 – Saving in HTML Format
Whole workbook
Single worksheet
Viewing web page
