We offer Excel live online training!
Duration
2 and a half days (minimum 2 days)
Trainer
Excel advanced training
Objectives
Affirm fundamental knowledge and become familiar with advanced use of Excel. Participants will learn how to use advanced formulas and functions for data analysis and processing that will speed up their daily work.
Participants
The course is intended for employees that use Excel on a daily basis with intention to refresh and expand their knowledge for more efficient performing of everyday tasks.
Prerequisite
Prerequisite for attending this course is completed Excel – Basics training.
Contents
Brief demo of some Excel applications to get a feeling of Excel as a development platform
Affirm fundamental knowledge of Excel (data types, formats, cell references etc.)
Tricks to speed up work (mouse and keyboard shortcuts)
Naming ranges and cells with introduction of range operators
Functions
- Types of functions and ways of their input
- Conditional summing and counting functions
- Logical functions (recommendations and options while dealing with complex conditions)
- Information functions
- Text functions
- Date & Time functions
- Lookup functions (VLOOKUP, INDEX, MATCH etc.)
- New functions in Excel for Microsoft 365 – XLOOKUP, XMATCH, UNIQUE etc..
- Create complex formulas by combining various functions
Advanced Conditional Formatting and Data Validation using formulas
Working with data on multiple sheets (3D references, INDIRECT and HYPERLINK function)
Data import and data preparation
- Import data from text files
- Data cleansing and preparation with data type conversion
Data Lists (how to set up models for easier maintenance)
- Advanced Filter
- Subtotal functions on filtered data (SUBTOTAL function)
- Pivot Tables and Pivot Charts
Macros
- Introduction to macros and VBA code recorded
- Recording, saving and running macros with important notice on their limitations
Practical examples
How it is done
Discussion about topics, solving examples and additional clarifications and tips