Data cleaning

Fix duplicates, extra spaces, numbers and dates stored as text, joined columns and blank cells in imported data.

10 lessons · about 1 h 45 min · 1 free · Confident to Advanced

Get the full course The same skill in Google Sheets

Lessons

In the order the Roadmap teaches them. The free one needs no sign-up.

Free · Confident · 9 min

Remove duplicates

Delete repeated rows from a merged list, and choose which columns decide what counts as a duplicate.

Start the lesson

In the full course. Confident · 8 min

Split one column into several

Split text that arrived in one column into separate columns at a comma, semicolon or any other separator, without losing data to the right.

See the lesson

In the full course. Confident · 9 min

Fix numbers stored as text

Spot numbers that are stored as text, turn them into real numbers with VALUE, and paste the fixed values over the originals.

See the lesson

In the full course. Confident · 12 min

Fix dates stored as text

Spot dates stored as text, turn them into real dates with DATEVALUE, and rebuild any other layout with DATE, LEFT, MID and RIGHT.

See the lesson

In the full course. Confident · 10 min

Remove hidden characters and extra spaces

Find the characters TRIM can't remove, such as non-breaking spaces and line breaks, and clean them with SUBSTITUTE, CLEAN and TRIM in one formula.

See the lesson

In the full course. Confident · 10 min

Fill blank cells with the value above

Fill the empty cells in a list so every row shows its group's name. You will use a helper column and paste values.

See the lesson

In the full course. Confident · 11 min

Standardize inconsistent entries ("NY" vs "New York")

Make every version of a value, like NY, N.Y. and New York, read the same with Find and replace or a mapping table, so totals and counts add up.

See the lesson

In the full course. Confident · 12 min

Clean an imported CSV

Take a messy export from another app and fix its usual problems in order, with Remove duplicates, Split text, TRIM, VALUE and DATEVALUE.

See the lesson

In the full course. Confident · 14 min

Project: clean a messy customer list

Turn a customer list merged from two sources into one clean row per customer, with tidy names and emails, standard state codes and no repeats.

See the lesson

In the full course. Advanced · 12 min

Reshape data from wide to long

Turn a grid with months across the top into a list with one row per item and month, using TOCOL, SEQUENCE and INDEX.

See the lesson

Lessons being written

Planned for this skill. They open in the course as they're finished.

Find duplicates with highlighting
Flash Fill

Paths with these lessons

A path is a short list of lessons from the course, picked for one goal.

Clean up messy data

For exports, CSVs and form responses that need tidying before anyone can use them.

10 lessons · about 1 h 30 min · 1 free · Beginner to Confident

See the path

Excel test prep

For an Excel test or skills check for an office, admin or analyst job.

15 lessons · about 2 h 30 min · 4 free · Beginner to Confident

See the path

Get the full course

Every lesson in the Excel course, with new lessons added as they're written.

All 217 Excel lessons open today, and every new one as it's written.
The Roadmap, Skills and all eight paths.
A cheat sheet for every lesson you finish.
At checkout, add the Google Sheets course for US$49.

US$149

Tax included · Lifetime access

Get the full course

30-day money-back guarantee if you've opened fewer than 10 paid lessons.

No account needed to pay. Payments are handled by Link, Stripe's checkout. Your card statement shows LINK.COM.