Course Outline
At the end of the course learners will have an advanced knowledge of Excel and be able to complete tasks such as VLookUp functions and Data Validation.
Learners should have an excellent knowledge of Computers and Excel having completed the Intermediate course or equivalent. Alternatively, they may have existing work experience.
Click the headings below to view topics.
Managing Excel
• Creating Custom Views
• Excel Viewing Options
Advanced Functions
• VLOOKUP Function
o Comparing two sheets
o IFERROR Function
o Combining IF Functions and VLOOKUP
• SUMIF and SUMIFS Functions
• COUNTIF and COUNTIFS Functions
• Text Functions
o Concatenate, Left, Right and Trim
• Date and Time Functions
Excel Protection
• Cell Range Protection
• Applying Worksheet Protection
• Password Protecting a Workbook
• Hiding Formula in cells
Auditing
• Excel Auditing Tools
o Tracing
o Evaluate Formula
o Error Checking
Data Validation
• Text, Numbers, decimals & dates
• Input, Error and Stop Messages
• Creating Dropdown List
• Circle Invalid Data
• INDIRECT Function
Advanced Conditional Formats
• Using Formulas with Absolute options
Advanced Pivot Tables
• Show Values As
o % of Grand Total
o Running Total in
o Ranking
• Slicer Connections
• Calculated Field
• Multiple Consolidation Ranges
• Dashboard display
Advanced Filters
• Filter and copy to another location
• Extract a list of unique records
Consolidation
• Worksheets in workbook
• From different workbooks
• Functions and Links
Advanced Chart Options
• Adding a secondary axis
• Creating a combined line and column Chart
• Adding Trendlines
• Adding Graphics
• Save Chart As Template
Custom Number Formats
Custom Date Formats
Importing/Exporting Data
• Text Files
• CSV Files
Input Tables
• One-input Data Tables
• Two-input Data Tables
