Excel for Advanced Users

If you have prior experience and have worked with Excel before but wish to enhance your skills, then this training is for you! The Advanced Excel Training teaches everything from cell formatting to creating summaries in a Pivot table through practical exercises. Additionally, you will learn useful techniques to reduce manual work, make your work more automated, minimize errors, and save time (and a lot of nerves). The time invested in this training will be returned multiple times later.

Training duration: 16 academic hours (2 days)

Target audience: The training is designed for Excel users who work with Excel data tables, use basic formulas and functions, and wish to enhance their skills in data processing, using various formulas and functions, creating summaries, and making charts.

The prerequisites for participating in the training are as follows: It is recommended that the participant has acquired basic Excel skills through work experience or has completed a basic Excel course.

Training description

The training focuses on making your work in Excel more efficient and easier, how to better automate work processes, and use the right techniques. We will create more complex formulas and use various functions. We will use Excel data processing tools, create various charts, and make summaries with Pivot tables.

From the Advanced Excel Training, each participant will receive instructional material and exercise files that show the work done and the functions/formulas used.

Training Topics

Introduction to Excel. Basic knowledge and tricks-tips that will help you speed up your daily work, navigating data, using keyboard shortcuts, and menus.

Working with cells and worksheets. Different data types, cell formatting, and defining new data formats. Using named ranges. Highlighting data with conditional formatting. Working with multiple data tables: splitting worksheets, side-by-side viewing, and synchronous scrolling.

Data protection. Locking cells and formulas, protecting worksheets and workbooks.

Using automatic tables. Simple lists and defined data table (or automatic table). Creating and formatting a new table, advantages over simple lists, additional techniques and options, dynamic tables.

Sorting and filtering. Sorting a data table on one or multiple levels. AutoFilter and custom filter. Using Advanced Filter. Filtering based on text patterns (wildcards).

Calculations in Excel. Using formulas and functions. Absolute and relative references. Formulas across multiple worksheets. Pasting the value of a formula. Counting functions (COUNT, COUNTA, COUNTIF) and finding the average (AVERAGE, MEDIAN, and MODE). Using the IF conditional function (IF, OR, AND, NOT) and summing based on conditions (SUMIF). Rounding functions (ROUND functions, CEILING, FLOOR, and MROUND). Operations with dates and times. Date and time functions (TODAY, NOW, DATE). Search and reference functions. Matching (VLOOKUP, HLOOKUP, LOOKUP, MATCH). Comparing lists. Text functions. Concatenating and splitting texts into columns.

Creating and modifying charts. Creating a new chart, using different types of charts, configuring and formatting the chart, secondary axis, and using two types of charts simultaneously, trendlines.

Summaries with Pivot tables. Creating a Pivot table, introducing the workspace and rules for using fields. Grouping data. Configuring and formatting a value field. Changing the display format of results.

Import and export. Using Excel tables and charts in other MS Office programs. Converting a text file to an Excel table.

Introduction to macros. Using macros in Excel. Recording and running a new macro.

Training objective

The objective of the Excel Advanced training is to improve participants’ Excel skills, make their daily use more efficient and clearer, and introduce new and exciting techniques, useful tools, functions, and various tips and tricks to simplify everyday work.

Learning Outcomes

After successfully completing the training, participants can:

• Create tables using different data types and change data formatting.
• Use automatic tables and associated additional features.
• Sort and filter tables.
• Create formulas within one worksheet and across multiple worksheets.
• Use absolute and relative references in formulas.
• Use common functions.
• Create charts and adjust initial settings.
• Use Pivot tables for summaries.
• Understand the principles of macros.

Training completion requirements: The achievement of learning outcomes will be assessed through independent practical work. At the end of the training, the training center issues a certificate or a document of participation to those who have completed the training. A certificate will be issued if the participant has completed at least 80% of the training curriculum and met other requirements specified in the training plan (e.g., exam, assessment, practical work, etc.). A document of participation will be issued to individuals who have attended the supplementary training or if the achievement of learning outcomes was not assessed during the training, or if the individual did not achieve all the required learning outcomes to complete the curriculum.

Training price includes:

• the training center provides a prepared workstation with the necessary hardware and software in the classroom. For participants attending the training in the online environment, remote access to the required software is provided if needed;
• study materials, including an MS Excel manual in PDF format, are also made available to participants;
• the training is delivered by an instructor who holds at least level 5 of an adult educator qualification and has relevant work experience in the respective field;
• opportunity to receive post-training consultation from the instructor via email regarding the topics covered during the training.

We also offer:

• free repeat training if you feel that any skill needs further improvement or if you had to interrupt the training due to reasons beyond your control;
• hot beverages with cookies;
• lunch on each training day;
• free parking (please register your parking with our office manager upon arrival for the training on each training day).

Supplementary training curriculum group: computer skills