Microsoft Excel Intermediate

Microsoft Excel Intermediate Course Content

Customise Quick Access Toolbar

• Move the Quick Access Toolbar
• Add common buttons to QAT
• Add other buttons to QAT

Customise the Ribbon

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

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
• Modify a 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

Data Validation

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

Use Functions

• Statistical: AVERAGE, MAX, MIN, COUNT, COUNTA
• Math: SUM, ROUND, ROUNDUP, ROUNDDOWN, RAND, RANDBETWEEN
• Date: TODAY, NOW
• Text: LOWER, UPPER, TRIM

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 or range
• Create multiple names from labels

Linking

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

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
• Name a table
• Reference a table

Protect your Data

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

Sorting

• Sort by single column
• Sort multiple columns
• Sort by colour
• Sort by custom list

Subtotals

• Subtotal a single column
• Create a nested subtotal
• Remove subtotals

Group Data

• Manually group rows and columns
• Automatically group data
• Clear grouping

Filtering

• Filter items
• Use the filter search tool
• Text filters
• Number filters
• Date filters
• Custom filters

Create a Pivot Table

• Insert a pivot table
• Select data to add to pivot table
• Change pivot table layout

Analyse data in Pivot Tables

• Filter data
• Subtotal data

Protect your Data

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

Templates

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

Printable Course Information

View 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