Microsoft Excel – finance and accounting

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

• 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

• 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

• 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 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

• 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)

• „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

• 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)

• 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)

• 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

• 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

• 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

• 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

• 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

• 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.