Remove Duplicates, Text to Columns & Flash Fill

Microsoft Excel provides several powerful tools for cleaning and organizing data. Three commonly used tools are Remove Duplicates, Text to Columns, and Flash Fill. These tools help users manage large datasets quickly and accurately.

Remove Duplicates

Remove Duplicates is used to find and delete repeated records from a dataset while keeping only unique values.

Why Use Remove Duplicates?

Example Data

After removing duplicates:

Steps to Remove Duplicates

1. Select the data range.
2. Go to Data Tab.
3. Click Remove Duplicates.
4. Select the columns to check.
5. Click OK.
6. Excel removes duplicate records automatically.

Shortcut Key

Alt + A + M

Text to Columns

Text to Columns is used to split data from one column into multiple columns based on a separator such as space, comma, tab, hyphen, or other characters.

Why Use Text to Columns?

Example

Steps to Use Text to Columns

1. Select the column containing combined data.
2. Go to Data Tab.
3. Click Text to Columns.
4. Select Delimited.
5. Click Next.
6. Select the delimiter (Space, Comma, Tab, etc.).
7. Click Next.
8. Choose destination if required.
9. Click Finish.

Common Delimiters

Flash Fill

Flash Fill automatically recognizes patterns in your data and fills the remaining cells without using formulas.

Example 1: Extract First Name

Type “Rahul” manually in the first row and use Flash Fill. Excel automatically extracts the remaining first names.

Example 2: Combine Names

After typing one example, Flash Fill completes the remaining records automatically.

Steps to Use Flash Fill

1. Type the expected result for the first record.
2. Select the next cell.
3. Go to Data Tab.
4. Click Flash Fill.
5. Excel detects the pattern and fills remaining cells.

Shortcut Key

Ctrl + E

Comparison of All Three Tools