Online Excel Intermediate Self-Paced Course

Getting Started

• How to get the most out of your course
• Download your resources
• Tips on how to get the most out of the videos
• Intro to our Learning Community

Module 1 – Working with large tables of data

• Tips on selecting and moving around data lists
• Freezing row and column panes
• Preparing the data
• Sorting data
• Using the Data Sort command
• Automatically insert subtotals in sorted data
• Removing Subtotals
• Multiple Subtotals
• Filtering data in a list
• Filter and remove rows containing blanks or errors
• Filtering using set criteria
• Filtering troubleshooting

Module 2 – When formulas don’t calculate as expected

• Understanding calculations
• Why brackets are important
• Copying calculations
• Why do formulas copy incorrectly?
• Creating an absolute reference
• Naming a range
• Creating a named range using the Name box
• Using named ranges in formulas
• Modifying and deleting named ranges
• Finding named ranges within the workbook

Module 3 – Using multiple worksheets

• Tips for working with multiple worksheets
• Navigating worksheets
• Selecting multiple sheets
• Ungrouping sheets
• Working with grouped worksheets
• Moving and copying worksheets between workbooks
• Linking data between worksheets and workbooks
• Linking a range of cells
• Identifying linked cells
• Open, modify, or break links to a source workbook
• Sharing workbooks containing links
• Replace link formulas with values

Module 4 – Time-saving functions

• Understanding functions
• Common Math functions
• Inserting a function
• Editing a Function
• IF function – check if something is true or false
• Creating an IF function
• IFERROR and ISERROR – ignore errors
• SUMIF and SUMIFS – summarise data
• COUNTIF – to summarise data
• COUNTIFS – to summarise data
• The EDATE function
• SUMPRODUCT – to summarise and total data
• The TODAY and NOW functions

Module 5 – Conditional formatting

• Conditional formatting
• Setting a condition with Highlight Cell Rules
• Identifying duplicate or unique rows of data
• The Quick Analysis Tool
• Setting a condition based on cell content
• Setting a new rule based on a formula
• Creating Data Bars and Colour Scales
• Applying Icon sets
• Managing Rules
• Clearing Rules

Module 6 – Intro to recording macros

• Using macros
• Opening a macro-enabled workbook
• Recording a macro
• Running a macro
• Saving macros
• Adding a macro button to the Quick Access toolbar
• Absolute or relative macro
• Assigning a macro to a macro button
• Copying macros
• Deleting a macro stored in the active workbook
• Deleting a macro stored in the Personal Macro workbook

Module 7 – Protecting

• Protecting worksheets and workbooks
• Unlocking cells
• Protecting a worksheet
• Protecting a workbook
• Setting a password to open or modify a worksheet

BONUS Module – New Microsoft 365 functions

• IFS function
• FILTER function
• FILTER with multiple criteria
• SORT function
• SORT and FILTER function
• SORTBY function
• UNIQUE function + drop-down menus
• Fixing SPILL and CALC errors

Passing the Finish Line
Claim your Certificate of Completion
A final message from Sharyn