Skip to content
Edition NigeriaGlobal
Country: Côte d'Ivoire

You are in the Côte d'Ivoire edition. Type your country to switch.

Set up your profile

You are learning in the Côte d'Ivoire edition, with examples in cfa francs and from Côte d'Ivoire.

Excel and spreadsheets with AI · Lesson 4 of 8

Cleaning data: text, dates, duplicates and tidy tables

Most wrong spreadsheet answers come from messy data, not bad formulas. This lesson shows how to lay out a tidy table, fix text with TRIM and PROPER, split columns, convert dates stored as text, decide what counts as a duplicate, and stop new mess with tables and data validation.

Beginner14 min read6 steps

This lesson is complete in writing. A video for it is on its way.

What you will learn

  • By the end you can lay out data as a tidy table with one row per record.
  • You can fix spacing, capitals and combined fields with TRIM, PROPER and text to columns.
  • You can spot dates and numbers stored as text and convert them.
  • You can decide what counts as a duplicate, flag it and remove it safely.
  • You can use tables, structured references and data validation to stop new mess.

Key terms in this lesson

Data cleaning
Preparing raw data for analysis by removing duplicates, correcting formats, handling missing values and recording every decision made along the way.
Tidy data
A table with one row per record, one column per field and no merged cells or subtotals mixed in.
Helper column
An extra column added next to the data to hold a cleaning or calculation step, so the original values stay visible and each step can be checked.
Text to columns
A spreadsheet tool that splits the contents of one column into several, using a separator such as a comma or a fixed width.
Date serial number
The number a spreadsheet stores behind a real date, counting days from a fixed starting date, which lets dates be sorted, subtracted and compared.
Structured reference
A way of referring to a formatted table by its table and column names instead of cell addresses, which grows automatically as rows are added.
Data validation rule
A rule that checks each value meets a condition, such as falling within a date range or appearing on an allowed list, so that bad entries are caught.
Drop-down list
A data validation setting that limits a cell to values chosen from a fixed list, which stops misspellings and inconsistent labels at the point of entry.

Step 1 of 6

Start with a tidy table

Opening this step…

Step 2 of 6

Fixing text: TRIM, PROPER and text to columns

This step opens when you finish the one above.

Step 3 of 6

Dates stored as text

This step opens when you finish the one above.

Step 4 of 6

Duplicates and numbers stored as text

This step opens when you finish the one above.

Step 5 of 6

Tables, structured references and data validation

This step opens when you finish the one above.

Step 6 of 6

Keep the raw data and a cleaning log

This step opens when you finish the one above.

Key takeaways

The key takeaways open when you finish every step.

Lesson quiz

6 questions. You need 85% to pass.

The quiz opens when you finish every step.

Read first

Stockertim Learn is education. It explains how investing works and never recommends buying or selling anything.

Up next

Skills for the AI agePivot tables, charts and a simpl…Read nowPivot tables, charts and a simple dashboardSkills for the AI age · Beginner · 15 minSkills for the AI ageA budget, variance analysis and …Read nowA budget, variance analysis and a simple business caseSkills for the AI age · Beginner · 15 minSkills for the AI agePayroll reconciliation and an in…Read nowPayroll reconciliation and an inventory countSkills for the AI age · Beginner · 15 minSkills for the AI ageUsing AI assistants in spreadshe…Read nowUsing AI assistants in spreadsheets safelySkills for the AI age · Beginner · 15 minAI at workCleaning data with AI helpRead nowCleaning data with AI helpAI at work · Intermediate · 15 minAI at workPrivacy and reproducible analysisRead nowPrivacy and reproducible analysisAI at work · Intermediate · 15 minSkills for the AI agedata is organised: tables, rows,…Read nowHow data is organised: tables, rows, columns and keysSkills for the AI age · Beginner · 15 minSkills for the AI ageIF, SUMIFS and COUNTIFSRead nowIF, SUMIFS and COUNTIFSSkills for the AI age · Beginner · 15 minSkills for the AI agePython basics: variables, lists,…Read nowPython basics: variables, lists, loops and functionsSkills for the AI age · Beginner · 15 minSkills for the AI ageJoining tables: SQL JOIN for beg…Read nowJoining tables: SQL JOIN for beginnersSkills for the AI age · Beginner · 15 minSkills for the AI ageSpreadsheet basics: cells, range…Read nowSpreadsheet basics: cells, ranges and formulasSkills for the AI age · Beginner · 15 minSkills for the AI ageLookups: XLOOKUP, VLOOKUP and IN…Read nowLookups: XLOOKUP, VLOOKUP and INDEX MATCHSkills for the AI age · Beginner · 15 min
English