Date: TBC
Duration: 1 Day
Programme overview
This one-day course is designed to give users an understanding of and practical experience of some of the more advanced features and functions within MS Excel including: Lookup tables, Data validation, Pivot Tables, dashboards and macros.
The course is ‘hands on’, so those attending will get practice in using advanced features of MS Excel to manage, automate and customise workbooks.
Please contact info@ifsskillnet.ie for more information on course dates and course fees.
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
Who is the course for?
This course is suitable for those people who want to expand and improve their knowledge of MS Excel’s more advanced functions, features and Add-Ins.
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
- Goal Seek
- Data Tables
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
