Microsoft Excel (Intermediate)

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: Candidates 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.

Duration: 1 Day

Learning Method: Online class with dedicated tutor

Course Objectives: 

On completion of this course candidates 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 of 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 Fill’

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