Excel Intermediate

 

Learning Objectives

• Managing Worksheets
• Using Tables data structure
• Soring and Filtering data in Lists and tables
• Subtotalling data
• Format cells based on contents of cells or other cells
• Work across multiple sheets

Pre-Requisites

• Create a basic worksheet
• Modify a workbook
• Use simple formulas and functions
• Format an Excel worksheet
• Manage worksheets and workbooks
• Set up printing options

Course Content

Viewing and Managing Worksheets

• Hide or Show Worksheets or Workbooks
• Freezing Panes
• Page Views
• Working with Multiple Sheets

Tables

• Tables Overview
• Creating Tables
• Delete Tables
• Formatting Tables
• Changing Table Design
• Table Tools
• Using External Data

Sorting and Filtering Data

• AutoFilter
• AutoFilter - Sorting List Data on a Single Column
• Sorting Data based on Multiple Criteria
• AutoFilter - Filtering Data
• Slicers in Tables

Using Lists – Subtotalling Data

• Subtotalling Data
• Grouping and Ungrouping Data for Subtotals
• Summarising a Subtotalled List
• Outlining
• Remove Subtotals
• Adding Total to Filtered Lists

Conditional Formatting

• Format Based on Data Contained in Cells
• Managing Formatting Rules
• Format Cells using a Formula
• Colour Scale and Data Bar Formats

Working with Multiple Sheets

• Changing the Number of Worksheets Available at Start-up
• Working with Sheets
• Worksheet Tab Properties
• Move and Copy Worksheets
• Group Mode
• Fill Data across Worksheets
• 3‐D Formulae
• Protect Worksheet Data
• View Worksheets Side by Side
• Watch Window