Explanation
The Data Tools section contains several important tools for cleaning, transforming and controlling data.
- Text to Columns
Splits information stored in one column into multiple columns.
Before
|
Customer Data |
|
Amit,Kumar,Sirsa |
|
Neha,Sharma,Hisar |
After
|
First Name |
Last Name |
City |
|
Amit |
Kumar |
Sirsa |
|
Neha |
Sharma |
Hisar |
Use:
Data → Text to Columns
- Flash Fill
Flash Fill recognizes patterns from examples.
Shortcut:
Ctrl + E
Example:
|
Full Name |
First Name |
|
Amit Kumar |
Amit |
|
Neha Sharma |
Neha |
|
Rahul Singh |
After giving Excel a pattern, Flash Fill can fill Rahul.
- Remove Duplicates
Removes duplicate records.
Before
|
Customer ID |
|
C101 |
|
C102 |
|
C101 |
|
C103 |
After
|
Customer ID |
|
C101 |
|
C102 |
|
C103 |
- Data Validation
Controls what users can enter into cells.
Examples:
- Numbers between 0–100
- Dates within a range
- Values from a list
- Consolidate
Combines information from multiple ranges into a summary.
Example:
January + February + March → Quarterly Sales Summary
Practice
Create a worksheet and perform:
Split Data → Flash Fill → Remove Duplicates → Apply Validation → Review Consolidate