MS Excel - advanced level

Remote and stationary training options

View the training programme

What will you gain from the training?

Training objectives

Participants will recap the intermediate and learn the advanced functions of Microsoft Excel and know their use in practice.
After completing the course, participants will know how to:

perform intermediate operations on copying and pasting data
protect cells, sheets and workbooks
use data filter and subtotals
use advanced filter
perform operations on dates and hours
use text conversion functions
use logical functions with multiplied conditions
use nested functions
insert data from other applications
analyse data using goal seek and solver add-in
analyse data using pivot tables and charts
consolidate data from different source files
record macro and review the VBA code

Is this training for you?

Audience profile and Requirements

This course is for people who wants to gain the efficiency in working with MS Excel on advanced level.

Duration

Duration:

3 days, 24 training hours

Detailed training program

Detailed training program

Data management

Efficient data entering
Efficient selecting
Go To and Go To Special tools
Sorting data
Custom lists
Autofilter and Advanced filter
Advanced operations on copying and pasting data

Adjustment of the working environment

Hotkeys, facilities
Create and modify toolbars
Excel Options (autosave, input, calculations, etc.)

Formatting

Cell styles
Format painter
Number formats

Functions

Subtotals function and subtotal tool
Statistical functions (average, maximum, minimum and quantity)
Text conversion functions
Date and time functions
Sheet inspection

Tables and list management

Using names of cells and ranges
Format as Table

Conditional functions

IF
Logical functions
Informational functions
Avoiding errors
Other conditional functions (countif, sumif and others)
Creation of complex / multiple formulas

Data consolidation

Getting external data (CSV, TXT, Access)
Data consolidation functions and tools
Search functions (lookup, vlookup, hlookup, index, match)

Review

Collaboration
Tracking changes
Comments

Protection

Protect worksheet and workbook
Secure and encrypt files

Pivot Tables and Pivot Charts

How to effectively create a pivot table
Changing style, layout, totals, subtotals
Formatting pivot table fields
Sorting and filtering data
Grouping the data
Using calculated fields and calculated items
Getting data from Pivot Table
Advanced Pivot Table options
Pivot Charts

General Analysis Tools

Scenarios
Custom Views
Goal Seek
Solver
Data Tables
One Input
Two Input

Cooperation with databases

Microsoft Query
Getting data from Oracle, SQLServer, MS Access

Macros

Displaying the Developer tab
Review and purpose of macros
Where to save macros
Absolute and relative record
Running macros: assigning to quick access toolbar, shapes, pictures and keyboard shortcuts

Training delivery method

The training is carried out in the form of theoretical part in the form of mini lectures and the practical part in the form of computer exercises.

Development path

VBA language in MS Excel