AI & Data Career Foundations · Beginner · ~8 hours

Data Analytics, Excel & SQL Fundamentals

Build foundational data literacy through spreadsheets, simple SQL and business reporting.

Status: Catalogue ready. This lesson version is available in review mode while subject-matter, accessibility and assessment checks are completed.

Turn clean data into careful decisions

Clean and structure a trustworthy dataset

45 min

course progress0 of 3 lessons complete

By the end of this lesson

Identify common spreadsheet data-quality problems and their consequences.
Create a simple data dictionary and cleaning log.
Preserve source meaning while making a fictional dataset easier to analyse.

Data work starts before the formula

A spreadsheet becomes useful when its structure and meaning are clear. Before calculating anything, identify what one row represents, what each column means, which values are required, which values may be missing and how dates, currencies and categories are written. If a row sometimes means an opportunity and sometimes means a contact, a total can be mathematically correct while answering no useful question. Common quality problems include duplicate records, inconsistent spelling, mixed date formats, numbers stored as text, blank values, impossible dates, hidden rows and categories that overlap. A cleaning decision should be documented rather than silently overwriting the source. Keep an untouched source copy, create a working copy, record each transformation and note who made the change. If a value cannot be resolved, mark it as unknown or exclude it with a reason. A data dictionary gives the dataset a shared vocabulary. It can include field name, business meaning, data type, allowed values, source, sensitivity, owner and quality rule. The dictionary is also an accessibility tool: plain-language definitions help a colleague understand a chart without reverse-engineering a formula. Good analysis is reproducible because another person can see how the table was prepared.

Clean for a question, not for appearance

Cleaning is not the same as making a table look tidy. The right treatment depends on the question. A missing close date might be a quality issue for forecasting but irrelevant to a count of product categories. A duplicate could be a true repeat order, a renewal line or an accidental copy. Do not delete it simply because it makes the total look inconvenient. Investigate the meaning and record the decision. Use validation checks such as row counts before and after cleaning, unique identifiers, totals by category, date ranges and a sample review. If the cleaned dataset changes a headline result, explain why. Preserve lineage by keeping source fields or a transformation log where appropriate. Sensitive fields should be minimised or replaced with fictional identifiers, and access should match the purpose of the analysis. The goal is a dataset that can support a defined decision with known limits. A clean-looking chart built on incomplete records can mislead more effectively than a messy table that admits uncertainty. When teaching or practising, use fictional data so learners can focus on reasoning without exposing real customers, candidates or colleagues.

Evidence, judgement and a safe next step

The professional habit in this lesson is to make the decision process visible. Start by naming the dataset structure and cleaning decision in plain language, then identify the evidence that supports the current view and the evidence that is still missing. Evidence can be a buyer statement, a dated observation, a documented calculation, a permitted source or a clearly labelled hypothesis. It should be possible for another colleague to understand where a conclusion came from without relying on confidence or personal reputation. When the evidence is weak, reduce the strength of the claim rather than decorating it with persuasive language. The next step should be proportionate to what is known. Define the row grain, test quality conditions and record transformations before calculating a business result. A small, reversible action is often more useful than a grand promise: ask one clarifying question, test one calculation, invite the right stakeholder, or record one assumption for review. Give the other person a genuine opportunity to agree, decline or change the sequence. This matters in commercial work because a fast process is not automatically a good process, and it matters in career development because a polished artefact is not automatically truthful evidence. Keep the boundary explicit. Data exercises must not expose private customer, candidate or colleague information or imply that a clean dataset guarantees a business result. Do not put private information, speculative outcomes or unsupported performance claims into a learner portfolio, CRM, dashboard or application. If a decision affects another person, explain who owns it and how they can challenge or correct it. A good submission therefore shows both capability and restraint: it demonstrates what was done, states what was not tested, and identifies a sensible follow-up. Reviewers can reward that clarity even when the proposed answer is incomplete, because responsible professional judgement is part of the skill being developed.

Workplace example

A fictional pipeline export contains duplicated opportunity IDs and two date formats. The analyst keeps the source copy, creates a cleaning log, checks whether duplicates represent renewals, standardises dates and reports the rows excluded from the forecast with a reason.

Try this walkthrough

1.Define the row grain, field meanings, types, required values and sensitivity.
2.Run duplicate, missing-value, format and range checks on the fictional table.
3.Record each cleaning decision and keep a source version unchanged.
4.Compare totals before and after cleaning and explain any material difference.

Watch for these traps

Deleting duplicates or missing values without understanding their meaning.
Changing the source file directly and losing provenance.
Using a formula before defining what one row represents.
Treating a tidy appearance as proof of data quality.

Practice studio

Make a decision. Test a response. Leave evidence.

Use the case from this lesson to practise judgement without creating a score or changing your course record.

Scenario decision

A fictional pipeline export contains duplicated opportunity IDs and two date formats. The analyst keeps the source copy, creates a cleaning log, checks whether duplicates repres…

Choose a response

Branching roleplay

A colleague asks you to move this situation forward. What do you say next?

Evidence workspace

Draft a short known / assumed / next note from the fictional case. Use fictional details only.

KnownAssumedNext step

This is a session-only practice draft. It is not saved, graded or added to your learning record. Never enter real personal or confidential information.

Practical assignment

Create a data dictionary and cleaning log for a fictional twenty-row pipeline export. Include row grain, four quality checks, three transformations, two exclusions and a before-and-after total.

Pass this lesson's knowledge check before recording it as complete.

Portfolio evidence

Practical portfolio project

Build a small sales-performance dashboard from fictional data and explain the decisions supported by its metrics.

Turn the practical project into a bounded evidence record. Save a draft while you work, then submit it for human review when it is ready.