Microsoft Excel Effective Workbooks

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 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