Microsoft Excel Effective Workbooks

Course Overview

The Microsoft Excel Intermediate course is aimed at those who want to improve the quality of data and efficiency & automation of their spreadsheets using various Excel tools and functions.

Microsoft Excel Effective Workbooks Course Content

Custom Lists

  • Create a new custom list
  • Import a new custom list
  • Edit a custom list
  • Use a custom list to fill text

Custom Formatting

  • Create a custom date format
  • Create a custom number format
  • Use Format Painter

Cell Styles

  • Create a new cell style
  • Apply a cell style

Conditional Formatting

  • Highlight cells based on a rule
  • Apply graphical conditional format
  • Create a custom rule

Themes

  • Change workbook theme
  • Change colour palette
  • Create custom colour palette

Templates

  • Save a workbook as a template
  • Create a new workbook from a template

Absolute Referencing

  • Reference vs absolute cell referencing
  • Set absolute cell reference
  • Set absolute row reference
  • Set absolute column reference

Names

  • Naming conventions
  • Name a cell
  • Name a range
  • Name multiple cells

Linking

  • Link data within a worksheet
  • Link data within a workbook
  • Link data between workbooks

Protect your Data

  • Protect a worksheet
  • Protect a workbook
  • Allow editing in specific cells only

Data Validation

  • Types of Data Validation
  • Create Drop Down Lists
  • Number Validation
  • Text Validation
  • Date Validation
  • Data Validation Settings
  • Input Messages and Error Alerts
  • Copy Data Validation Settings
  • Find Invalid Data

Populate Data using IF

  • Use an IF function to populate data
  • Use a nested IF function to populate data

Populate Data using VLOOKUP

  • Set up a lookup table
  • Use a VLOOKUP function to populate data

Tables

  • Convert a range to a table
  • Table features
  • Name a table
  • Reference a table

Macros

  • Record a macro to apply formatting
  • Run a macro
  • Edit a macro
  • Add macro button to QAT

Customise the Ribbon

  • Create a custom tab
  • Add a custom group
  • Add a command
  • Add a macro

Printable Course Information
View Course Calendar

Pre-requisites for the Microsoft Excel Intermediate Course

  • Basic experience in using Microsoft Excel
  • Knowledge of simple formulas and functions
  • Alternatively, completion of Excel Essentials training course