Excel Masterclass – using the advanced functions of the program and macro commands

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Faster data cleanup - You will quickly find blank cells, fill missing values, copy ranges while skipping hidden rows, and format only the cells that actually need attention in your worksheets.

Quicker report building - You will speed up everyday work with autofill, date and workday series, and rapid creation of repeated sequences in large spreadsheets without typing everything manually.

Confident formulas and functions - You will build reliable calculations with logical, statistical, text, and date functions, and you will learn how to spot and fix common spreadsheet errors on your own.

Clean analysis-ready data - You will master text-to-columns, cleanup of messy source data, Flash Fill, and text functions, so you can prepare structured data for further analysis much faster.

Clear analysis and visuals - You will choose the right chart for each data set, highlight key values with conditional formatting, and create reports that are easy for others to read and interpret.

Better work with large datasets - You will use tables, sorting, filtering, and pivot tables to analyze large volumes of data quickly, without manually reviewing hundreds of rows one by one.

Practical Excel automation - You will learn the macro recorder and VBA basics, allowing you to automate repetitive tasks, run your own macros, and build simple tools that streamline daily work.

Print and PDF-ready reports - You will prepare worksheets and charts for professional output, set print areas, repeating headers, and page numbers, then export polished reports to PDF files.

Training programme

1. Go To… tool and working with blank cells

searching for and selecting blank cells in a range,
formatting and filling blank cells,
selecting cells while skipping hidden rows,
copying ranges containing hidden rows,
formatting only cells with entered data.

2. AutoFill and quick creation of data series

creating an ordinal number,
creating a list of calendar and working days,
creating a list of dates in monthly and yearly cycles,
using AutoFill in work with large datasets.

3. Calculations – formulas and functions

types of errors occurring in calculations,
AutoSum: SUM, AVERAGE, MIN, MAX, COUNT,
logical functions: IF, OR, AND, IFNA,
lookup functions: VLOOKUP or XLOOKUP,
statistical and mathematical functions: COUNTIF, SUMIF, AVERAGEIF, ROUND,
date and time functions: NETWORKDAYS, WORKDAY, DATEDIF, DAY, MONTH, YEAR, TIME, HOUR, MINUTE, SECOND,
relative and absolute references,
nesting functions,
basic comparison of VLOOKUP / XLOOKUP / INDEX + MATCH.

4. Text processing and cleaning

splitting text using the Text to Columns tool,
text functions: LEFT, RIGHT, MID, PROPER, LOWER, LEN,
flash fill,
cleaning unstructured data.

5. Conditional formatting

highlighting data meeting specific criteria,
data bars, color scales and icon sets,
highlighting the lowest and highest values,
creating conditional formats using formulas.

6. Working with the Table object

formatting data as a table,
creating calculations in a table,
sorting and filtering data,
working with large data sets.

7. Charts and data visualization

principles of creating charts,
selection of the chart to the data,
column, bar, line, pie and doughnut charts,
editing chart elements,
charts with two axes,
charts of changes over time.

8. Data security and control

protecting the file with a password,
protecting sheets and cells,
hiding formulas,
data validation,
creating drop-down lists.

9. Sorting and filtering

simple and multi-level sorting,
sorting data horizontally,
filtering numerical, text, and date data,
advanced filtering by several criteria.

10. Pivot Tables

creating pivot tables,
basic calculations: sum, average, counting,
sorting and formatting data,
grouping data,
basic mistakes made by users,
introduction to slicers and timeline.

11. Macro recorder

macro recorder – introduction,
recording simple actions,
assigning a macro to a graphic element.

12. Printing and preparing reports for PDF

defining the print area,
adjusting the data range to the paper size,
setting margins and page orientation,
repeating headers,
printing charts,
page numbering,
export to PDF.

13. Day 3 – advanced extension

14. Advanced techniques for working with data

XLOOKUP, VLOOKUP and INDEX + MATCH – comparison of applications,
dynamic and traditional array formulas,
optimization of work on large data sets,
best practices when building analytical worksheets.

15. Expert formulas and their combinations

complex conditional formulas: IF, IFS, SWITCH,
advanced text and date/time functions,
logical and array functions in „what-if” analyses,
creating your own solutions using named ranges.

16. Expert Pivot Tables

advanced pivot table settings,
grouping and custom calculations,
calculated fields and calculated items,
combining multiple data sources in one analysis,
working with slicers and the timeline.

17. Automation of work with VBA

basics of working with the VBA editor,
writing and running macros,
automatic reporting,
building simple tools in Excel,
handling events and user interaction.

What are the prerequisites for participating in the training?

Basic Excel handling - You should move around a worksheet with ease, enter and edit data, select cells, rows, and columns, and save files without needing extra step-by-step assistance.

Simple formula basics - You should be able to enter a simple formula, use basic operators, and understand that results depend on cell references, even if you do not use advanced functions yet.

Working with data tables - You should have experience with simple datasets, understand how rows and columns are organized, and prepare data in a way that makes it suitable for further analysis.

Independent Windows use - You should be comfortable using Windows, opening files, working with the clipboard, and switching between windows, as the training assumes smooth computer work.