Excel for Accounting Course

Lesson 1 • Navigating the Excel Environment

Covers the ribbon, quick access toolbar, and worksheet tabs. Establishes baseline navigation skills essential for all subsequent accounting tasks.

Lesson 2 • Workbook and Worksheet Management

Teaches creating, renaming, moving, and protecting worksheets. Directly supports organised multi-sheet accounting file structures.

Lesson 3 • Cell Referencing and Data Entry

Introduces absolute, relative, and mixed cell references alongside efficient data entry techniques. Forms the foundation for building accurate accounting formulas.

Lesson 1 • Cell and Table Styling

Covers borders, shading, font choices, and built-in cell styles for financial tables. Creates visually clear layouts that guide readers through financial data.

Lesson 2 • Page Layout and Print Settings

Configures headers, footers, print areas, and page breaks for printed financial reports. Ensures every printed page is labelled, scaled, and paginated correctly.

Lesson 3 • Number and Currency Formatting

Teaches accounting number format, currency symbols, decimal places, and negative number display. Ensures financial figures are presented accurately and consistently.

Lesson 1 • Conditional Aggregation Functions

Teaches SUMIF, SUMIFS, COUNTIF, and AVERAGEIF for category-based financial analysis. Enables segmented reporting by account, department, or date range.

Lesson 2 • Arithmetic and Aggregation Functions

Covers SUM, AVERAGE, MIN, MAX, and COUNT variants for financial totals. These functions underpin every profit and loss account, balance sheet, and budget model.

Lesson 3 • Lookup and Reference Functions

Covers VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP for retrieving account data. Automates cross-table data retrieval critical to reconciliations and reporting.

Lesson 4 • Logical and Error-Handling Functions

Introduces IF, nested IF, IFS, AND, OR, and IFERROR for decision-based calculations. Prevents formula errors from disrupting financial models and reports.

Lesson 1 • Data Validation and Integrity Controls

Applies dropdown lists, input restrictions, and error alerts to enforce data entry rules. Prevents invalid entries in shared accounting workbooks and templates.

Lesson 2 • Removing Duplicates and Auditing Data

Uses Remove Duplicates, COUNTIF checks, and formula auditing tools to verify data integrity. Catches duplicate transactions and broken references before reporting.

Lesson 3 • Importing and Connecting External Data

Covers importing CSV, text, and database files into Excel using Power Query. Establishes repeatable data pipelines from accounting systems into Excel.

Lesson 4 • Text and Data Cleaning Functions

Uses TRIM, CLEAN, LEFT, RIGHT, MID, FIND, and SUBSTITUTE to fix imported data. Resolves common data quality issues from exported accounting system reports.

Lesson 1 • Grouping and Summarising Financial Data

Groups dates by month, quarter, and year and applies SUM, COUNT, and AVERAGE summaries. Produces period-over-period financial comparisons directly in the PivotTable.

Lesson 2 • Calculated Fields and Items

Creates custom metrics such as gross margin and variance using calculated fields. Extends PivotTable analysis beyond raw data without modifying the source.

Lesson 3 • Building Your First PivotTable

Walks through source data requirements, inserting a PivotTable, and placing fields. Establishes the structural logic needed for all advanced PivotTable work.

Lesson 4 • PivotTable Formatting and Layout

Applies PivotTable styles, number formats, and layout options for professional output. Ensures PivotTable reports match organisational formatting standards.

Lesson 1 • Financial Functions for Accounting

Applies NPV, IRR, PMT, FV, PV, and depreciation functions to investment and asset analysis. Enables accountants to evaluate financing, leases, and capital expenditure in Excel.

Lesson 2 • Named Ranges and Dynamic References

Defines named ranges and dynamic named formulas to make models readable and maintainable. Reduces formula errors and simplifies auditing in complex workbooks.

Lesson 3 • Budget vs. Actual Variance Models

Builds variance analysis templates comparing budget, forecast, and actual figures. Automates favourable and unfavourable variance flags used in management reporting.

Lesson 4 • Model Auditing and Error Prevention

Applies formula auditing, circular reference detection, and model documentation practices. Ensures financial models are transparent, verifiable, and error-resistant.

Lesson 1 • Dynamic Charts with Named Ranges

Links charts to dynamic named ranges so visuals update automatically with new data. Eliminates manual chart updates in recurring monthly and quarterly reports.

Lesson 2 • Selecting the Right Chart Type

Maps financial data types to appropriate chart types including bar, line, waterfall, and combo. Prevents misleading visuals by matching chart choice to the analytical message.

Lesson 3 • Building and Formatting Charts

Covers chart insertion, axis formatting, data labels, and title configuration. Produces clean, professional charts aligned with organisational style guides.

Lesson 4 • Building a Financial Dashboard

Assembles charts, KPI cards, slicers, and PivotTables into a single interactive dashboard. Integrates skills from prior chapters into a complete management reporting tool.

Lesson 1 • Editing Macros in VBA

Reads and modifies recorded VBA code to fix errors and add flexibility. Enables accountants to customise automation without writing code from scratch.

Lesson 2 • Introduction to Macros and the VBA Editor

Explains what macros are, how they are stored, and how to access the VBA editor. Provides the conceptual foundation needed before recording or writing any automation.

Lesson 3 • Recording and Running Macros

Records macros for formatting, data entry, and report generation tasks. Demonstrates how recorded code translates manual steps into reusable automation.

Lesson 4 • Macro Security and Deployment

Covers digital signatures, trusted locations, and distributing macro-enabled files safely. Ensures automated workbooks meet organisational security and compliance requirements.