Learning outcomes
On completion of this course, delegates will be able to use a range of advanced functions including:
Use Range Naming to understand and apply Lookup Functionality Use complex Functions including Nesting Apply Data Validation to existing and data to be Inputted Conditional Formatting including RAG Status identification Cell / Sheet / Workbook Protection Pivot Table Reports & Charting Dashboards for Management Reporting Macros and VBA for improved efficiency PowerPivot data Analysis
Course Content:
Working with Named Ranges
Creating Range Names Range Names in Calculations
Lookup and Reference Functions
Understanding how and when to use the VLOOKUP Function
Complex Functions and Nesting
If, SumIfs and IfError Functions
AND / OR Functions
Subtotal and Rank Functions
What if analysis
Data Validation
Setting-up Data Validation Rules Create Drop Down Lists How to Circle Invalid Data Formula Auditing
Custom Formatting
Using Custom Formats Using Conditional Formatting
Cell Protection
Protecting Formulas / Worksheet / Workbook
Pivot Table & Charts
Creating Pivot Tables Editing Pivot Tables Using Functions and Formulae within Pivot Tables Printing Pivot Tables Pivot Charts
Charts
Creating Charts Changing Chart Elements Changing Chart Data Printing Charts Inserting Pictures Manipulating Objects
Using Dashboards for Management Reports
Analyse Design Create Test
Introduction to Macros and Basic VBA
Recording a Basic Macro Running a Macro Editing a Macro
PowerPivot Add-In
Establish Relationships between several Tables Create Pivot tables using more than one Excel Spreadsheet Table Source Create Pivot tables using more than one External Table Source
