EXCEL · 1 Day

Microsoft Excel Intermediate 2

Building on the skills developed in Excel Intermediate 1, this course introduces more advanced formulas, workbook techniques and data analysis tools. Delegates will learn to use logical and lookup functions, work with named ranges and linked workbooks, apply advanced filtering techniques, enhance charts and gain an introduction to PivotTables.

Duration: 1 Day From £550/day (up to 8 learners) 1-to-1 or Groups of up to 8 Learners (Larger by Arrangement) M365 (Desktop & Web) · Office 2021/2024 Certificate · Manuals · 1-Year Support

Complete Course Syllabus

Advanced Formulas and Functions

  • Using the IF function in practical examples
  • Using IF to check whether a cell is blank
  • Creating nested IF statements
  • Using VLOOKUP and HLOOKUP
  • Working with text functions
  • Working with date functions

Using Named Ranges

  • Creating and managing named ranges
  • Using named ranges in formulas
  • Using named ranges to navigate through workbooks

Linking Worksheets and Workbooks

  • Creating references to other worksheets
  • Creating references to other workbooks
  • Editing workbook links
  • Troubleshooting links

Advanced Data Analysis

  • Using the Quick Analysis tool
  • Applying advanced filters
  • Sorting and filtering data using conditional formats

Advanced Charting Techniques

  • Analysing trends using Sparklines
  • Exploring different chart types
  • Applying chart styles and formatting chart elements
  • Filtering charts by series or categories
  • Adding trendlines to charts
  • Creating chart templates

Protecting Worksheets and Workbooks

  • Protecting individual cells
  • Protecting worksheet structure
  • Protecting workbooks with passwords

Introduction to PivotTables

  • Understanding the purpose and structure of PivotTables
  • Creating a basic PivotTable
  • Summarising and rearranging data
  • Using recommended PivotTables to analyse data
  • Understanding how PivotTables can support data analysis and reporting
Copied to Clipboard ✓

Ready to Share