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.