Microsoft Excel in data analysis – advanced filtering options, creating a pivot table, macros and working with charts

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 cleaning - You will quickly find blank cells, fill missing values, and work only with visible records, so you can prepare cleaner datasets for analysis without wasting time on manual fixes.

Quicker data setup - You will use AutoFill, data series, and range handling tools to build numbering, calendars, and consistent worksheet structures without repetitive manual editing.

Reliable analytical formulas - You will create formulas with logical, conditional, and lookup functions to calculate KPIs, connect datasets, and reduce common spreadsheet errors in your reports.

Efficient text cleanup - You will split text strings, extract key fragments, standardize formatting, and use Flash Fill to clean CRM exports, CSV files, and raw system data much more efficiently.

Clear data highlighting - You will apply conditional formatting to flag exceptions, statuses, and critical values, helping you spot anomalies, priorities, and important records at a glance.

Better work with tables - You will turn ranges into structured tables, use calculated columns, and sort or filter data more effectively, making everyday work with growing datasets much easier.

Reports that support decisions - You will build charts, sparklines, and pivot tables that present trends, comparisons, and data structure in a way that is clear and useful for managers or clients.

Secure export and control - You will learn sheet protection, data validation, macro basics, and print or PDF setup, so you can secure your workbook and deliver polished final reports faster.

Training programme

1. The “Go To – Special…” tool in cleaning and inspection of databases

searching for and selecting blank cells in a range: quick identification of information gaps in contact databases, sales registers or logistics registers,
formatting and filling blank cells: bulk filling of missing data (e.g. inserting the value „No data” or duplicating the parent value for blank records),
selecting cells while skipping hidden rows: safe isolation and copying of filtered records (e.g. a list of active clients) without the risk of copying hidden data.

2. Advanced techniques for working with the “Go To…” tool

copying a range in which rows are hidden: transferring only visible data structures to new analytical summaries,
formatting only cells with already entered data: quickly distinguishing constant values (entered manually) in order to differentiate them from cells containing analytical formulas,
filling empty cells with data series: effective preparation of „fragmented” system tables for further processing and analytics.

3. Autofill and generation of data series

creating an ordinal number: automatic indexing of databases, unique identifiers (ID) and record lists,
creating a list of calendar and working days: generating timelines for continuous analyses and project calendars excluding weekends.

4. Calculations – formulas, logical functions and lookup

calculations using AutoSum: quick aggregation of large volumes of data – application of SUM, AVERAGE, MIN, MAX, COUNT to determine basic descriptive statistics,
logical functions (IF, OR, AND): automation of data segmentation (e.g. qualifying a customer for the “VIP” group, assigning priorities to orders, checking whether KPI indicators are within the norm),
handling relationship errors (IFNA): masking missing values in databases when joining tables, so that errors do not disturb the aesthetics of the report,
lookup functions (VLOOKUP or XLOOKUP): mapping data from different databases (e.g. assigning a product category to an SKU code, combining demographic data with a customer’s purchase history),
conditional statistical and mathematical functions: use of COUNTIF, SUMIF, AVERAGEIF for selective calculations (e.g. average sales only for a selected region, number of orders from a given supplier),
calculation precision (ROUND): standardization of numerical results and elimination of rounding errors in final percentage indicators,
locking cells (Relative and absolute references $): working with locked cells to recalculate market shares, target achievement, and structure analysis (%),
nesting functions: creating advanced multi-step formulas in order to solve complex analytical problems in a single cell.

5. Text Processing and Cleansing (Data Cleansing)

text splitting using the „Text to Columns” tool: breaking down raw character strings (e.g. separating first and last names, separating product codes from their names, cleaning exports from CRM systems into CSV format),
text functions (LEFT, RIGHT, MID, LEN): extracting unique prefixes, postal codes, production batches or transaction identifiers,
standardization of character case: unifying databases (PROPER, LOWER) to eliminate duplicates resulting from different spelling,
Flash Fill: intelligent automatic extraction of text patterns without using formulas (e.g. cleaning phone numbers, merging addresses).

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

highlighting data meeting criteria: automatic marking of records (greater/less than, equal to, between) – e.g. highlighting transactions with the highest margin or products with critically low stock levels,
visual dashboards (Data Bars, color scales and icon sets): creating heat maps for tables (e.g. product turnover levels) and graphically signaling project completion statuses,
highlighting extreme values: quick identification of anomalies (lowest/highest values in the database, results drastically deviating from the average),
conditional formatting using formulas and functions:highlighting entire rows in a table based on the value in one cell (e.g. highlighting the entire row of an order that has the status „Cancelled”).

7. Working with the Table object (Structured databases)

data formatting: quick conversion of an ordinary range into a professional database with automatically matched formatting,
creating calculations: use of calculated columns (structured formulas), which automatically copy themselves to new rows after adding new data (e.g. new transactions from the next day).

8. Charts and Data Visualization (Data Visualization)

principles of creating charts and selecting them for data: choosing the appropriate form of presentation – structure (pie/doughnut chart), comparisons (column/bar chart), trends and correlations (line chart),
modification of charts: quick change of aesthetics using ready-made Chart Styles, Quick Layout, and intelligent use of the color palette to maintain readability,
editing and configuration of elements: adding and formatting axes, data labels, gridlines, legend, and titles,
creating charts with two axes: presenting two different units on one chart (e.g. bars as sales volume in units on the primary axis, line as sales value in PLN on the secondary axis),
time-series charts (Sparklines): placing micro-charts inside individual cells to present a trend (e.g. the sales behavior of a given product over 12 months).

9. Security and control of entered data (Data Quality)

password protection for file access: protection of confidential customer databases or strategic reports against unauthorized opening,
worksheet protection against changes: blocking the report structure against accidental deletion of formulas by other users,
cell protection and hiding formulas: securing key analytical algorithms while leaving the possibility to edit input cells,
data entry validation: enforcing the correctness of entries (e.g. accepting only integers, dates from a specified range, or avoiding the entry of negative values),
creating drop-down lists (Data Validation Lists):nudowanie closed selection dictionaries (e.g. a list of regions, order statuses, product categories) in order to maintain database cleanliness.

10. Advanced sorting and filtering

simple and multi-level sorting: organizing data by one or several criteria simultaneously (e.g. segmenting the database from the most important market, and within the market by the highest turnover),
horizontal data sorting: changing the arrangement of columns (e.g. arranging months or stages of the process from left to right),
advanced filtering: precise filtering of numbers (e.g. top 10% of results), texts and specific date filters (e.g. „previous quarter”). Using wildcard characters (e.g. *) to filter incomplete phrases.

11. Pivot table as a basic analytical tool

preliminary information and creating structures: aggregating tens of thousands of rows of raw data into clear summary tables,
creating basic calculations: quickly switching views between summing values, calculating the average, or counting unique occurrences (frequency),
sorting and formatting data: organizing results in a pivot table (e.g. from the best-selling products) and applying clear number formats,
grouping data: dynamically collapsing dates into months, quarters and years, as well as grouping numbers into ranges (e.g. analysis of the age structure of customers or shopping baskets),
discussion of user errors: solving problems with data not refreshing, empty fields, or shifting of the source range.

12. Macro Recorder – Automation of repetitive reports

macro recorder – introduction: automating routine, daily analytical activities without the need to know VBA programming,
assigning a macro to a graphic element: creating intuitive action buttons on the worksheet (e.g. the „Format raw data” button), which, when clicked, perform the entire sequence of preparatory steps for the user.

13. Preparing a report for printing and export (Managerial Export)

defining the print area: adjusting large analytical summaries to the paper size (scaling to the page width to prevent columns from being cut off),
page layout settings: managing margins, orientation (landscape for wide tables) and sheet size,
repeating headers on each page: ensuring readability of printed multi-page reports (repeating the first row/column),
printing graphic objects: correct positioning of charts and dashboards in the print area,
creating page numbering and printing to PDF: professional generation of final, secured analytical reports for management staff in PDF format.

What are the prerequisites for participating in the training?

Basic Excel skills - You should be comfortable moving around a worksheet, entering data into cells, selecting ranges, and saving files so you can focus on analysis instead of basic navigation.

Simple formulas and references - You should understand how to enter a simple formula, use basic operators, and refer to cells, because the course expands these skills into more practical analytical tasks.

Working with data tables - You should have some experience using a simple table with headers and multiple rows of data, since the exercises rely on sorting, filtering, and organizing datasets.

Readiness for hands-on data work - You should be willing to work with larger datasets and complete practical exercises, because the training is focused on doing the analysis yourself step by step.