Data Management and Cleaning: practice exercises
Real data is rarely clean, and this set trains you to fix it. Sort and filter tables, remove duplicates, split text into columns, limit input with data validation, build dropdown and dependent lists, and tidy numbers, dates and names that arrived from a CSV export. Each exercise gives you a messy sample range and checks the cleaned result. Useful for analysts, accountants and anyone who spends more time preparing data than analyzing it.
38 exercises · level: Confident User
Start with the first task Study the course: Data Management and Cleaning →
Showing 38 of 38
Order in Your Data
Sort the table A2:B9 by sales from largest to smallest. The rows must stay intact.
Turn on the filter and keep only the Chicago deals visible.
In C13, add up only the visible deals.
In C14, use the ordinary SUM and compare: it counts the hidden rows too.
Remove the repeated clients from the table A1:B9.
Split the lines in D2:D5 into the name (column D) and the city (column E).
Make a dropdown list of statuses in C2:C7, using F2:F4 as the source.
Allow only whole numbers from 1 to 5 in D2:D7.
For order #301 (C2), pick the status "Paid" with the ▾ arrow.
Some orders in the export A1:D12 are recorded twice. Remove the repeats.
Split "courier, district" in C2:C10: the courier stays in C and the district moves to D.
Make a dropdown list of statuses in E2:E10, using H2:H4 as the source.
Turn on the filter and keep only the orders from the "Northside" district visible.
In B14, add up only the visible orders, and in B15 count how many orders are visible now.
Lists and Messy Data
Name the model lists: "Laptops" is E2:E4, "Phones" is F2:F4, "Headphones" is G2:G3.
Make a dropdown list of categories in A2:A6, using E1:G1 as the source.
Make a dependent list of models in B2:B6 with the source =INDIRECT(A2).
In each row of B2:B6, pick a model from the list, and in C2:C6 look up its price in the table I2:J9.
In D2:D6, extract the first name: the text before the first space.
In E2:E6, extract the middle name: the text between the first and second spaces.
In F2:F6, build the initials with the last name: "E. R. Johnson".
In G2:G6, bring the phone numbers to the form +12125550148: no spaces or hyphens, the last 10 digits after +1.
In H2:H6, get the login: the part of the email before the "@", in lowercase.
In G2:G6, turn the text in column A into numbers (some have spaces at the edges).
In G7, add up G2:G6. Now SUM sees the numbers.
In H2:H6, get numbers out of "$1,250": remove the $ sign and the commas.
In I2:I6, convert the European numbers "1.250,75" (the comma is the decimal part, the period separates thousands).
In J2:J6, build dates from the text "2026-03-15" (year-month-day) and apply the Date format.
In K2:K6, build dates from the European "15/03/2026" (day/month/year) and apply the Date format.
In B2:B6, extract the city: the text before the first semicolon.
In C2:C6, get the amount as a number: the second field, where the period separates thousands and the comma is the decimal part.
In C7, add up C2:C6 to check that you got real numbers.
In D2:D6, build a date from the third field, "YYYYMMDD", and apply the Date format.
In E2:E6, extract the branch code, the last field, so that the leading zeros survive (keep it as text).
Advanced Filtering
In F5, list all the deals (all 4 columns) from the region in F2 with an amount of at least G2: the conditions are joined by AND.
In K5, list the reps whose region is the one in F2 OR whose product is the one in H2.
In G12, count how many deals pass the AND condition, using COUNTIFS and the criteria cells.
In G13, add up the amounts of those deals with SUMIFS.
No exercises match these filters.