MS Excel - Intermediate
One Day Training Course / £195 plus VAT
Introduction
This one-day instructor led course is designed to develop the practical skills of Excel users. By the end of the course delegates will be able to use to full advantage a greater range of available Excel functions in order to produce more effective spreadsheets.
Course Objectives
- Use advanced formulas and functions
- Define names for individual cells and ranges
- Use the database tools in Excel
- Work effectively with large spreadsheets
Pre-requisites
This course is designed for delegates who have completed the Excel Level 1 course or have equivalent knowledge. Delegates should already be able to create formulas, use basic functions such as SUM, MIN, MAX and COUNT and also be aware of the difference between relative and absolute references.
Course Content
|
Review of Excel Basics
|
|
|
|
Functions
|
Database tools
|
|
|
- IF functions
|
- Sorting data – ascending, descending, left to right
|
|
|
- VLOOKUP and HLOOKUP
|
- Sorting data in custom sequences
|
|
|
- Function to combine data from multiple cells
|
- Filtering data with AutoFilter
|
|
|
- Date functions
|
- Creating customised and advanced filters
|
|
|
- Nested functions
|
- Using data forms to enter, delete and search for data
|
|
|
|
- Using Subtotals to calculate sub sets of data
|
|
|
Tools
|
|
|
|
- Naming cells and ranges
|
|
|
|
- Protecting cells
|
|
|
|
- Linking workbooks
|
|
|
|
- Linking applications (Excel with Word or PowerPoint)
|
|
|
|
|
|
|
|
Formatting
|
|
|
|
- Hiding columns, rows and formulas
|
|
|
|
- Grouping columns and rows
|
|
|
|
- Applying conditional number formatting
|
|
|