Overview
A practical introduction to preparing and interpreting data at work. In the standalone first day, attendees use Power Query in Excel, PivotTables and PivotCharts to turn everyday spreadsheets into reliable, reusable analysis and refresh it with new data. An optional second day develops statistical understanding and critical thinking, helping attendees interpret patterns, question claims and make better-informed decisions.
What you’ll need
- Windows laptop with Excel desktop (Microsoft 365 or Excel 2021 or later) and Power Query (Get & Transform)
- Power BI Desktop (optional)
- Reliable internet connection and headphones and microphone
- Training materials are supplied; no organisational or personal data is required
Learning outcomes
By the end of the relevant day, attendees will be able to:
Part 1 — Data at work (Day 1; standalone)
- Frame a workplace question and identify the data needed
- Distinguish analysis-ready tables from reports and recognise quality problems
- Improve collection layouts to reduce missing and inconsistent entries
- Import, reshape and clean data using Power Query in Excel
- Check counts, totals and missing values before interpreting results
- Investigate and explain findings and limitations using PivotTables and PivotCharts
- Refresh saved preparation steps and verify the updated analysis
Part 2 — Thinking clearly with data (Optional Day 2)
- Group continuous values into bins and explain their effect on a histogram
- Recognise symmetric and skewed distributions and investigate outliers
- Choose averages and interpret spread using quartiles, box plots and standard deviation
- Interpret probabilities and explain the importance of sampling and base rates
- Frame a null hypothesis and distinguish apparent differences from convincing evidence
- Recognise heuristics and biases, and seek evidence that challenges an initial judgement
- Assess charts and claims, and communicate findings, limitations and justified next actions
Course topics
Day 1 — Introduction: Data at work (standalone)
- Workplace questions, data quality and context; data types, structured/unstructured data and the three Vs. Excel, Power Query and Power BI; OSEMN: obtain, scrub, explore and interpret.
Obtain: Sources and better collection
- Source, purpose and row meaning; reports versus analysis-ready tables; headers, consistent records, collection layouts, validation and Microsoft Forms. Import and inspect the supplied Excel workbook.
Scrub: Prepare data with Power Query
- Remove report furniture; set data types; distinguish missing values, zero and cereal ‘trace’ amounts; standardise categories, split fields and unpivot monthly layouts. Review Applied Steps; preserve source data.
Explore: Check and investigate
- Reconcile counts and totals; check missing values, duplicates and unusual results. Investigate the opening question by comparing categories and periods using counts, percentages and averages with suitable denominators, including survey non-response.
Interpret: Visualise and communicate
- PivotTables, PivotCharts and where Power Pivot fits; bar/line charts, titles, labels and units. Spot misleading scales and missing context; explain a finding, limitation and proportionate next action.
From this month to next month: Refresh and reuse
- Add records; refresh the query, PivotTable and PivotChart; reconcile results. Trainer demonstration: prepared data and report refresh in Power BI. Finish with a reusable workbook and workplace improvement.
Optional Day 2 — See the pattern: Distributions
- Continuous and categorical data; bins and histogram widths; normality, symmetry, skew and clusters. Use the illustrative bakery scenario to explore selective sampling; investigate outliers rather than automatically removing them.
Describe it fairly: Typical values and variation
- Mean, median and mode; hotel prices and room-type mix; quartiles, interquartile range, box plots and standard deviation. Compare distributions, not averages alone, and consider the effect of extreme values.
Question the apparent result: Probability and evidence
- Natural frequencies; independent and dependent events; conditional probability, base rates and Bayes’ theorem; gambler’s fallacy. Sampling variation, size and representativeness; null hypotheses, chance and association versus causation; absence of evidence is not proof of no effect.
Question ourselves: Heuristics, bias and framing
- Anchoring, availability, confirmation bias, loss aversion and gain/loss framing. Revisit a judgement after new evidence; seek contradictory evidence, alternative explanations and missing perspectives.
Communicate fairly: Charts and comparisons
- Bars for comparison; pies for parts of a whole; histograms, box plots and time-series lines. Zero baselines for bars and clearly labelled line-chart scales; check units, denominators, periods, selected categories and context.
Final challenge: Would you approve this recommendation?
- Assess a hotel-booking policy claim using charts, distributions, room types, samples and comparable periods. Produce and defend a decision brief: supported findings, alternative explanations, uncertainty, further evidence and recommended action.