Excel Masterclass – effective formulas, charts and data analysis

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 work in Excel - You will use keyboard shortcuts, Find and Replace, Format Painter, and faster data editing methods, so you can handle everyday Excel tasks much more efficiently and with less effort.

Clear, polished worksheets - You will master cell formatting, value formatting, and conditional highlighting, so you can build worksheets that are easier to read, easier to analyze, and ready to share with others.

Quicker data entry - You will learn to use AutoFill for number series, dates, weekdays, and months, which will help you reduce manual typing, speed up repetitive work, and avoid common input mistakes.

More reliable calculations - You will build formulas, use functions such as IF, SUMIF, and COUNTIF, and spot common calculation errors, so your Excel results will be more accurate, consistent, and dependable.

Stronger data analysis - You will work with PivotTables, Excel Tables, filtering, and sorting, so you can summarize larger datasets faster, uncover key patterns, and make decisions based on clearer insights.

Better text handling - You will split text into columns, combine values, and use text functions and Flash Fill, which will make it much easier for you to clean up, standardize, and correct inconsistent data.

Charts that explain results - You will create and refine column, line, pie, and dual-axis charts, so you can present results in a way that is clear, visually appealing, and easier for your audience to understand.

Print-ready reports - You will set print areas, repeating headers, margins, page numbers, and PDF export options, so your worksheets will look professional and stay readable both on screen and in print.

Training programme

1. Microsoft Excel – introduction

program interface,
keyboard shortcuts,
Find/Replace tool,
entering and editing data,
Format Painter tool,
adding, deleting and copying worksheets,
comments – inserting and editing them,
using ready-made templates,
Subtotal tool,
consolidation.

2. Changing the appearance of data in Excel

formatting cells,
formatting values.

3. Autofill

creating a series from an ordinal number,
creating a list of calendar and working days,
creating a list of dates in monthly and yearly cycles,
creating a series from the names of months and days of the week.

4. Changing the appearance of data due to conditions – conditional formatting

highlighting data meeting criteria greater/smaller than or with specified text,
creating data bars, color scales,
adding symbols in a cell (e.g. arrows) for values meeting selected criteria,
highlighting the highest/lowest data with color,
highlighting data below/above the average,
creating conditional formats using formulas and functions,
editing and deleting conditional formats.

5. Calculations – formulas and functions

creating calculations using formulas,
discussion of errors occurring with incorrect calculations,
calculations using AutoSum: SUM, AVERAGE, COUNT , MAX, MIN,
working with functions: ROUND, IF, COUNTIF, SUMIF etc.,
calculations on dates using functions: WORKDAY, NETWORKDAYS, DAYS, DAY, MONTH, YEAR,
calculations based on data from different worksheets,
locking cells – relative and absolute references (symbol $).

6. Copying data

copying only values or only formats,
copying with swapping rows and columns,
copying while preserving column widths.

7. Pivot table introduction

creating a table and updating data,
performing basic calculations: sums, average, data counting,
formatting the pivot table,
filtering data.

8. Table Object

data formatting,
creating calculations,
filtering data using a slicer.

9. Working with texts

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

10. Sorting and filtering

sorting data by columns containing text, numbers, dates,
sorting by month names,
filtering numerical, text data and by data containing dates.

11. Charts

creating and modifying charts using Chart Style, Quick Layout and Change Colors,
changing the chart type,
creating column, bar, 3D, line, pie charts, etc.,
editing chart elements: legend, title, axis labels, units, etc.,
creating charts with two axes,
creating charts of changes over time and editing them.

12. Printing

defining the print area,
repeating the table header/first column on each page,
setting margins, page orientation, etc.,
page numbering,
printing to PDF.

What are the prerequisites for participating in the training?

Basic computer skills - You should be comfortable using a computer, keyboard, and mouse, opening programs, and saving files, so you can complete the course exercises without unnecessary difficulty.

Basic Excel familiarity - You should know how to open a workbook, move around a worksheet, and enter data into cells, because this course builds practical skills rather than teaching Excel from scratch.

Basic data awareness - You should understand rows, columns, cells, and simple tables, so you can work smoothly with tasks related to sorting, filtering, and analyzing information in Excel.

Readiness for hands-on work - You should be ready to complete tasks on your own and test solutions directly in worksheets, because the course is practice-based and develops your Excel skills step by step.