Microsoft Excel intermediate – formulas, functions, charts and an introduction to pivot tables and macros

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 worksheet work - You will learn how to find blank cells, copy data while skipping hidden rows, and fill ranges efficiently, so you can clean up and organize even large spreadsheets much faster.

Less manual data entry - You will create number sequences, daily, monthly and yearly date series, and working-day lists, which will reduce repetitive typing and help you avoid common input mistakes.

More reliable calculations - You will master logical, lookup, statistical, math, date and time functions, along with absolute references, so you can build formulas that stay accurate and flexible.

Cleaner text and data - You will learn to split text into columns, use text functions, and apply Flash Fill, so you can clean raw data quickly and prepare it properly for further analysis and reporting.

Instant spotting of issues - You will use conditional formatting with rules and formulas to highlight outliers, extreme values, and records that need attention, making unusual data points visible right away.

Pivot table analysis - You will build pivot tables, calculate sums, averages and counts, group data, and sort results, so you can turn large datasets into clear insights much more efficiently.

First steps with macros - You will get started with the macro recorder and assign macros to graphic elements, allowing you to shorten repetitive tasks without having to write code from scratch.

Professional reports and prints - You will prepare charts, set print areas, margins, repeating headers, page numbers, and PDF output, so you can present results in a clear and professional format.

Training programme

1. Go To tool

searching for and selecting blank cells in a range,
formatting and filling in blank cells,
selecting cells while skipping hidden rows and, for example, copying them.

2. Go To… tool

copying a range in which there are hidden rows,
formatting only cells with data already entered,
filling in empty cells.

3. AutoFill

creating an ordinal number,
creating a list of calendar and working days,
creating a list of dates in monthly and yearly cycles.

4. Calculations – formulas and functions

types of errors occurring in calculations,
calculations using AutoSum: SUM, AVERAGE, MIN, MAX, COUNT,
logical functions: IF, OR, AND, IFNA,
lookup functions: VLOOKUP or XLOOKUP ( depending on the Excel version you have),
statistical and mathematical functions COUNTIF, SUMIF, AVERAGEIF, ROUND etc.,
date functions: NETWORKDAYS, WORKDAY, DATEDIF, DAY, MONTH, YEAR etc.,
time functions: TIME, HOUR, MINUTE, SECOND,
locking cells – relative and absolute references (symbol $),
nesting – creating calculations in one cell consisting of several functions.

5. Text processing

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

6. Changing the appearance of data depending on conditions – conditional formatting

highlighting data meeting criteria greater/less than, equal to, between,
creating data bars, color scales and icon sets,
highlighting the lowest/highest values,
creating conditional formats using formulas and functions.

7. Working with the Table object

formatting data,
creating calculations.

8. Charts

principles of creating charts, selecting a chart for the presented data, working with color,
simple and quick modification of charts using Chart Style, Quick Layout and Change Colors,
column, bar, line, pie, doughnut charts, etc.,
editing and adding chart elements,
creating charts with two axes,
creating time course charts and editing them.

9. Security and control of entered data

password protection of access to the file,
protection of sheets against changes,
protection of cells in the worksheet, hiding formulas,
validation of the correctness of data entry,
creating drop-down lists with a list of allowed values to be entered in a cell.

10. Sorting and filtering

simple sorting and by two criteria,
sorting data horizontally,
filtering numerical, text and date data,
advanced filtering by several criteria and using *.

11. Pivot table

pivot table as a basic analytical tool – preliminary information,
creating basic calculations: sums, average, data counting,
sorting data in a pivot table,
formatting data in a pivot table,
grouping data and performing calculations in the created groups,
discussion of the basic errors made by users.

12. Macro recorder

macro recorder – introduction,
assigning a macro to a graphic element.

13. Printing

defining the print area and adjusting the data range to the paper size,
setting margins, page orientation, etc.,
repeating headers / the first column of the table on each page,
printing charts,
creating page numbering,
printing to PDF.

What are the prerequisites for participating in the training?

Basic Excel navigation - You should be comfortable moving around a worksheet, entering and editing cell values, selecting ranges, and opening, saving, and closing Excel files on your own.

Simple formulas and references - You should understand how the formula bar works, know basic operators, and be able to reference cells and perform simple calculations in a worksheet independently.

Basic data formatting - You should know how to change number, date, and text formats, adjust column widths, and apply simple formatting so your data is ready for further work.

Working with data tables - You should have experience with simple Excel data lists, understand the layout of rows and columns, and know how to sort or filter data at a basic level.