Overview
A practical, two-day course in building repeatable Excel reporting workflows. Attendees organise business data, calculate summaries, prepare and combine data with Power Query, and communicate findings through PivotTables and charts. Reusable query steps and refreshed reports replace repeated copying and manual cleaning. Automation uses Excel tables, formulas and Power Query; no programming is required.
What you’ll need
- Windows laptop with the current Microsoft 365 desktop version of Excel installed and activated
- Access to Power Query (Get & Transform Data), XLOOKUP and PivotTables
- Permission to download, save and open supplied Excel and CSV training files
- Reliable internet connection and headphones and microphone
Learning outcomes
By the end of the course, attendees will be able to:
- Organise data as an Excel table and identify problems with structure, data types and completeness
- Use structured references, conditional calculations and lookups to answer business questions
- Import Excel and CSV data with Power Query and apply reusable cleaning and reshaping steps
- Append similar datasets and merge a lookup table into a reporting dataset, checking the results
- Build PivotTables and charts to compare categories, explore trends and communicate findings
- Refresh a reporting workbook with new data and check record counts, totals and unexpected results
- Present a concise analysis summary with evidence, limitations and a recommended next action
Course topics
Working with Data in Excel
- Frame a business question; recognise clear headings, one row per record and consistent data types
- Create tables; sort, filter and use structured references; identify blanks, duplicates and inconsistent dates or numbers
Formulas for Practical Analysis
- Summarise with SUMIFS and COUNTIFS; combine IF with AND or OR to apply business rules
- Use XLOOKUP; check missing matches and duplicate keys; verify formulas against known examples and extend calculations through table columns
Importing and Cleaning Data with Power Query
- Import Excel or CSV files with Get Data; understand applied steps, set data types and clean text
- Handle missing values and errors; remove duplicates appropriately; create calculated columns such as line totals; split columns and unpivot a simple cross-tab
Combining Data for a Reusable Workflow
- Append matching files or tables; merge a reference table using a suitable key; check unmatched records and row counts for unintended duplication
- Load prepared data to an Excel table; distinguish source data, query steps and output
Summarising and Exploring Data
- Build PivotTables with rows, columns, values and filters; choose appropriate aggregations
- Compare categories and periods, calculate percentage contributions and use slicers; check totals and unusual values without confusing correlation with causation
Communicating Findings with Charts
- Choose column, bar or line charts and use PivotCharts where helpful; add meaningful titles, labels and number formats
- Avoid misleading axes and excessive decoration; combine useful summaries and charts in a clear reporting sheet
Refreshing and Checking a Recurring Report
- Replace or extend source data; rerun saved Power Query steps, refresh queries and PivotTables, and confirm updated outputs
- Validate row counts, totals, missing matches and source-column changes; document how a colleague should update the workbook
Practical Reporting Workshop
- Complete a guided workplace task: prepare, analyse and visualise supplied data in one workbook
- Test a second data batch and resolve an introduced quality issue; present findings, explain a limitation and propose an evidence-based next action.