icon icon

Microsoft Excel – finance and accounting

icon

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?

icon

Faster data cleanup - You will quickly spot blank cells, hardcoded amounts and hidden issues in worksheets, so you can clean up accounting data faster and prepare reliable files for further analysis.

icon

Less manual work - You will automate repetitive tasks such as numbering registers, building calendars and filling time series, which will shorten the time needed to prepare records and summaries.

icon

Stronger accounting checks - You will create formulas that verify balancing entries, amount thresholds and missing data, helping you catch issues earlier before month-end closing or an internal review.

icon

Efficient data matching - You will master lookup functions and data matching across reports, so you can assign accounts, vendors and rates automatically instead of retyping values between sheets.

icon

Clear reports and insights - You will build tables, charts and pivot tables that let you present cost structure, revenue trends and budget variances in a format that is easy to read and explain.

icon

Better ERP data handling - You will learn how to split, clean and standardize text exports from ERP systems, so you can turn raw downloads into structured data ready for accounting and reporting work.

icon

More control over files - You will learn how to protect sheets, lock formula cells and apply data validation rules, reducing the risk of errors and unauthorized changes in important finance workbooks.

icon

Reports ready to share - You will practice preparing print layouts and PDF exports so financial reports fit the page, keep repeating headers and are ready to send to management or clients.

Training programme

1. The “Go To – Special…” tool in the audit and cleaning of books

For audit and worksheet cleanup, Go To Special helps isolate exactly the cells that require action. The section covers finding blanks in large statements, copying filtered data without hidden rows, and selecting constants or formulas to identify hardcoded amounts that should be replaced with proper calculations.

  • searching for and managing blank cells: quickly supplementing missing general ledger accounts or contractor names in bulk turnover statements,
  • copying while skipping hidden rows: safely exporting filtered financial data (e.g. only approved business trips) without the risk of copying hidden items,
  • formatting only cells with data (constants or formulas): quickly locating “hardcoded” values in spreadsheets (replacing manually entered amounts with correct formulas).

2. Auto-fill data and time series

Generating orderly sequences saves time in repetitive accounting preparation tasks. This part focuses on autofill for serial numbering in VAT registers and payroll lists, and on creating calendar and working-day series used later in time settlement, travel accounting, or interest calculations.

  • list automation: creating unique ordinal numbers (No.) for VAT registers and payroll lists,
  • accounting calendars: generating lists of working and calendar days for the purposes of settling working time, business trips or interest.

3. Financial calculations – Formulas, functions and error handling

Building reliable financial formulas starts with combining calculations, verification logic, and error handling in one worksheet model. The work includes SUM and AVERAGE for balance checks, IF with AND or OR for control rules, IFNA and IFERROR, absolute references, and nested formulas for taxes or bonuses.

  • data aggregation (AutoSum): Practical use of the SUM, AVERAGE, MIN, MAX, COUNT functions for quick balance control,
  • verification logic and account reconciliation: Application of the IF, OR, AND functions for automatic control (e.g. „Debit = Credit”, detection of transactions above the cash limit of PLN 15,000),
  • handling missing data: Application of the IFNA and IFERROR functions,
  • cell locking (Absolute references $): Correct use of locking for tax calculations (e.g. fixed CIT/VAT rate) and cost allocation according to an allocation key,
  • nesting functions: Building advanced multi-stage formulas in one cell (e.g. calculating progressive tax or a bonus).

4. Advanced mathematical, statistical and lookup functions

Lookup and aggregation functions connect dispersed financial data into usable reporting logic. The section includes VLOOKUP and XLOOKUP for mapping accounts, contractors, or rates, SUMIF-family functions for conditional summaries, and ROUND to eliminate minor discrepancies in tax returns and statements.

  • lookup functions (VLOOKUP vs XLOOKUP): combining the chart of accounts with the turnover statement, assigning contractor names to NIP numbers, retrieving depreciation rates,
  • conditional aggregation: using SUMIF, SUMIFS, COUNTIF and AVERAGEIF for project reporting,
  • financial precision (ROUND): avoiding penny differences in tax declarations and reports through correct rounding of formula results.

5. Working with dates and time in financial analyses

Calculating reporting periods, due dates, and working time depends on correct use of Excel date and time functions. Participants extract month and year from posting dates, compute settlement aging or payment deadlines with WORKDAY and DATEDIF, and handle hours, overtime, and machine time costs.

  • date functions (WORKDAY, WORKDAY.INTL, DATEDIF): calculating invoice payment deadlines, aging of settlements, calculating late payment interest,
  • period segmentation: using DAY, MONTH, YEAR to extract reporting periods from the full posting date,
  • time functions (TIME, HOUR, MINUTE): accounting for employees' working time, overtime, and machine labor costs.

6. Text Processing and Cleaning (Import from ERP Systems)

Imported ERP text often arrives merged, inconsistent, or difficult to analyze in its raw form. This section covers Text to Columns, functions such as LEFT, RIGHT, MID, and LEN, case standardization with PROPER or LOWER, and Flash Fill for fast cleanup or recombination without formulas.

  • „Text to Columns” tool: splitting merged data from systems such as SAP, Comarch, Symfonia (e.g., separating the invoice number from the contractor name),
  • text functions (LEFT, RIGHT, MID, LEN): extracting fragments of journal entry descriptions, synthetic and analytical account numbers,
  • standardization of entries: unifying letter case (PROPER, LOWER) in company names,
  • Flash Fill: intelligent, automatic cleaning and merging of text data without using formulas.

7. Conditional formatting in audit and internal control

Spotting anomalies directly in a worksheet is handled through conditional formatting rules. The focus is on highlighting budget overruns, overdue invoices, and risky transactions, as well as using data bars, icon sets, and formula-based rules to mark full records such as unbalanced Dr/Cr entries.

  • highlighting deviations: automatic coloring of amounts exceeding the budget, overdue invoices, or transactions marked as risky,
  • data bars and icon sets: visual presentation of the cost structure and liquidity indicators directly in tables,
  • formula-based conditional formatting: highlighting entire rows (e.g. highlighting in red an entire document that does not balance in the Dr/Cr accounts).

8. Working with the Table object (Official table format)

A standard range becomes more reliable once it is converted into an Excel Table. The section shows how this object supports structured growth of datasets, copies formulas automatically into newly imported rows, and provides a total row that can switch between sum, average, and count on filtered data.

  • structure automation: converting an ordinary range into a dynamic Excel Table,
  • dynamic formulas: automatic copying of formulas to new rows after importing another accounting month,
  • total row: quick switching between sum, average and counting items for filtered financial data.

9. Data visualization and managerial reporting (Charts)

Charts support managerial reporting by matching the visual form to the financial message. Participants work with pie, line, and column charts, build combinations with a secondary axis, use Sparklines inside cells, and apply corporate colors and layouts to keep reports clear for decision-makers.

  • selection of a chart for financial data: presentation of the cost structure (pie/doughnut chart), revenue dynamics (line chart) and budget execution comparisons (column/bar chart),
  • advanced presentation techniques: creating charts with two axes (e.g. bars as revenues on the primary axis, line as % profitability on the secondary axis),
  • charts of progress over time (Sparklines): placing miniature charts inside individual cells to present the expenditure trend of a given department,
  • report aesthetics: working with the corporate color, chart style and quick layout in order to obtain a clear report for the Management Board.

10. Sorting and advanced data filtering

When entries need to be reviewed across several dimensions, sorting and filtering become the main control tools. The section addresses multi-level sorting by account and amount, left-to-right ordering of columns, and advanced criteria using quarters, thresholds, and wildcard patterns such as 401*.

  • multi-level sorting: sorting accounting entries first by account number, and then by descending amount,
  • horizontal sorting: non-standard arrangement of columns (e.g. ordering months in the fiscal year from left to right),
  • advanced filtering: filtering settlements according to complex criteria (e.g. dates from a given quarter AND amounts above the limit). Use of wildcard characters (e.g. *) to filter specific groups of accounts (e.g. 401*).

11. Pivot table as the heart of financial analysis

Large volumes of transactions are turned into analytical summaries through pivot tables. The work includes aggregating bank or ledger data, switching measures between sums, counts, and averages, grouping posting dates by reporting periods, and avoiding common refresh or source-range errors.

  • aggregation of mass data: creating turnover and balance summaries from thousands of rows of bank statements,
  • flexible summaries: quickly changing the view from the sum of costs to the number of transactions or the average invoice value,
  • data grouping: automatic collapsing of individual posting dates into months, quarters and years for trend analysis,
  • cost structure: creating summaries by departments (MPK), types of costs and key contractors,
  • error analysis: how to avoid recurring errors when refreshing data and modifying the source of the pivot table.

12. Security, control and validation of accounting data

Protecting accounting files requires both access control and safeguards against incorrect input. This section covers passwords, locking sheets and formula cells, hiding sensitive calculations, and using Data Validation to enforce closed lists, valid periods, and acceptable numeric ranges.

  • protection of data integrity: securing files with a password against unauthorized access,
  • locking sheets and cells: securing cells with formulas calculating taxes and salaries while allowing data entry in variable cells. Hiding secret formulas,
  • data validity (Data Validation): creating drop-down lists (e.g. a closed selection list of payment methods, project codes or VAT rates),
  • system limitations: introducing rules preventing the entry of a date from a closed accounting period or negative values where only positive numbers are required.

13. Macro Recorder – Automation of routine tasks

Routine cleanup of ERP exports can be reduced to a single click with Macro Recorder. Participants record actions such as removing unnecessary columns, adjusting widths, and applying currency formats, then assign the macro to a worksheet button for repeated use in accounting work.

  • introduction to automation: how the recorder works and when it is worth using in the accounting department,
  • automation of report cleaning: recording a macro that formats a raw data dump from the ERP system with one click (removing unnecessary columns, setting widths, applying currency format),
  • running macros: assigning the recorded code to a graphic element (e.g. creating your own „Format report” button on the worksheet).

14. Preparation for printing and export of financial reports

Excel print settings and PDF export are used here to prepare financial reports for distribution without layout issues. The section covers print areas, fitting wide sheets to a page, repeating headers on multi-page statements, and setting margins, orientation, and footer page numbering.

  • control over the workspace: defining the print area, fitting large spreadsheets to one page wide (cleaning the view before sending),
  • readability of multi-page statements: printing table headers (e.g. column name, account number) on each subsequent page of the paper printout,
  • professional presentation: setting margins, landscape orientation for wide balance sheets, creating automatic page numbering (e.g. "Page 1 of 5") in the footer,
  • export: safe generation of reports to PDF format without cutting off columns and charts.

What are the prerequisites for participating in the training?

icon

Basic Excel skills - You should be comfortable moving around a worksheet, selecting ranges, copying data and using basic formatting options, so you can focus on finance-related tasks.

icon

Working with tabular data - You should know how to work with simple row-and-column datasets and understand table structure, because the training is based on analyzing and organizing data.

icon

Basic formulas and cell references - You should know how to enter simple formulas and understand cell references, so you can move more easily into conditional functions, lookups and absolute references.

icon

Financial context awareness - You should understand basic concepts from finance, accounting or controlling, so you can immediately relate the exercises to invoices, accounts, taxes and reports.