Country: Barbados
You are in the Barbados edition. Type your country to switch.
Nigeria
The Americas
Loading every country…
No country matches. Check the spelling, or choose "Any country" for the Global edition.
You are learning in the Barbados edition, with examples in dollars and from Barbados.
Your learning profile
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.
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: Start with a tidy table, not started
- Step 2: Fixing text: TRIM, PROPER and text to columns, not started
- Step 3: Dates stored as text, not started
- Step 4: Duplicates and numbers stored as text, not started
- Step 5: Tables, structured references and data validation, not started
- Step 6: Keep the raw data and a cleaning log, not started
About 12 min of reading
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.
Popular this week: Basics: BeginnerStock market: Intermediate