Course Code: XL19L2
Duration: 1 Day
COURSE AIMS:
This Microsoft Excel Intermediate course is aimed at people who want to expand their knowledge of Microsoft Excel to work with larger spreadsheets, create formulas across multiple worksheets and workbooks, manage tables of data, and use advanced formatting techniques.
COURSE PRE-REQUISITES:
Students who wish to attend this course must have knowledge of Excel and feel comfortable with creating, formatting, and editing spreadsheets. They should be able to write formulas using multiplication, addition, subtraction, division and use the basic functions, sum, average, max, min and count.
COURSE OBJECTIVES:
On completion of this course delegates will be able to:
- Create formulas across multiple worksheets
- Copy formats and values across multiple worksheets
- Copy and link information across multiple workbooks
- Apply conditional formatting and creating custom format
- Manipulate text and use Text functions
- Understand the concepts an Excel database and create and modify data tables
- Use criteria ranges to analyse data using the Advanced Filter
- Create sub totals and group & ungroup data
COURSE CONTENT:
Introduction and Objectives
Working with Multiple Worksheets
- Selecting multiple worksheets
- Creating formulas across worksheets
- Using grouped worksheets
- Referencing cells across sheets
- Hyperlinking between sheets
- Consolidating worksheets
Using Multiple Workbooks
- Opening, activating & tiling workbooks
- Copying data between workbooks
- Creating formulas across workbooks
- Editing workbook links
Using Paste Special Flash Fill
- Working with Paste Special
- Copying values & formulas between worksheets
- Creating data sequences using 'Flash Fil'
Custom Views
- Creating, displaying and deleting custom views
Using Conditional & Custom Formatting
- Applying a comparative conditional formatting rule
- Managing conditional formatting rules (editing, formatting and clearing)
- Applying built-in data bars, colour scales and icons sets to numeric data
- Creating codes to format numbers and dates
Manipulating Text
- Use Text to columns to split a single column of data into multiple columns
- Using Text Functions (Upper, Lower, Proper, Trim, Left, Right and Concatenate)
- Removing duplicates
Working with Excel Data Tables
- Rules for creating tables and lists in Excel
- Creating an Excel table
- Adding automatic totals to an Excel table
- Converting an Excel table back to a range
- Using the 'Quick Analysis button' to add totals to a table
Working with Advanced Filters
- Creating a criteria range
- Using the Advanced Filter
Creating Subtotals in a List
- Creating subtotals for groups of data
- Creating multiple subtotals
- Copying visible data to a new worksheet
- Removing subtotals
Working with Outlines
- Applying, expanding & collapsing an outline
- Modifying and clearing outline
- Using Auto Outline
Action Planning
Review of Programme
Excel Frequently Asked Questions
