File Name
Data_Cleaning_Practice.xlsx
Raw Dataset
|
Order ID |
Customer |
City |
Product |
Category |
Qty |
Sales |
|
O101 |
Amit |
sirsa |
Keyboard |
accessories |
5 |
4000 |
|
O102 |
Neha |
Hisar |
Mouse |
Accessories |
10 |
5000 |
|
O103 |
Rahul |
SIRSA |
Monitor |
hardware |
3 |
25500 |
|
O102 |
Neha |
Hisar |
Mouse |
Accessories |
10 |
5000 |
|
O104 |
Priya |
Fatehabad |
Printer |
Hardware |
2 |
24000 |
|
O105 |
Simran |
hisar |
Webcam |
accessories |
4 |
10000 |
|
O106 |
Amit |
Sirsa |
Keyboard |
Accessories |
5 |
4000 |
|
O107 |
Rahul |
Sirsa |
Monitor |
Hardware |
3 |
25500 |
Tasks
Task 1 — Identify Duplicates
- Highlight duplicate Order IDs.
- Highlight duplicate records where appropriate.
Task 2 — Remove Duplicates
Remove repeated Order IDs after verifying them.
Task 3 — Clean Spaces
Use:
=TRIM(B2)
Task 4 — Standardize Names
Use:
=PROPER(TRIM(B2))
Task 5 — Standardize Cities
Convert:
- sirsa
- SIRSA
- hisar
into consistent formatting.
Task 6 — Standardize Categories
Convert:
- accessories
- Accessories
- hardware
- Hardware
into consistent formatting.
Task 7 — Final Clean Table
Create:
|
Order ID |
Customer |
City |
Product |
Category |
Qty |
Sales |
Final Challenge
Prepare a Clean Sales Master Dataset that contains:
- No unwanted duplicates
- No unnecessary spaces
- Consistent capitalization
- Consistent city names
- Consistent categories
- Correct numeric values
- Verified records