Excel advanced course

Duration

2 and a half days (minimum 2 days)

Trainer

Dražen Hanžić

Excel advanced course

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 course.

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.)
  • 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