Introduction

Excel Data Tools are used to organize, clean, manage, validate, and analyze large amounts of data efficiently. These tools help users find required information, remove errors and duplicates, separate data into different columns, and maintain accurate records.

Data Tools are especially useful in business reports, student records, sales data, employee databases, inventory management, accounting, and data analysis.

---

1. Sort Data

Sorting means arranging data in a particular order.

Excel allows you to sort data:

• A to Z – Ascending order
• Z to A – Descending order
• Smallest to Largest
• Largest to Smallest
• Oldest to Newest
• Newest to Oldest
• Custom order

Example

Suppose you have:

You can sort Marks from Largest to Smallest:

Steps to Sort Data

1. Select the data.
2. Go to the Data tab.
3. Select Sort.
4. Choose the column you want to sort.
5. Select the sorting order.
6. Click OK.

Important: When sorting a table, make sure the complete data range is selected so that records do not become mismatched.

---

2. Filter Data

Filtering displays only the records that meet a particular condition while temporarily hiding other records.

Example

If a student database contains 100 students, you can filter it to display only students from the Excel course.

Steps to Apply Filter

1. Select the heading row.
2. Go to Data → Filter.
3. Drop-down arrows will appear in the headings.
4. Click the arrow of the required column.
5. Select the required values.
6. Click OK.

Types of Filters

Excel provides different filtering options such as:

• Text Filters
• Number Filters
• Date Filters
• Search Filters
• Custom Filters

---

3. Custom Filter

Custom filtering allows you to apply specific conditions to your data.

For example, you can display students whose marks are:

• Greater than 50
• Less than 40
• Equal to 75
• Between 50 and 80

Example

To display students scoring more than 60:

Number Filters → Greater Than → 60

Excel will display only the matching records.

---

4. Data Validation

Data Validation controls what type of information users can enter into a cell.

It helps prevent incorrect or unwanted data entry.

Example

For a Course column, instead of allowing users to type anything, you can provide a list:

• ADCA
• DCA
• Tally Prime
• Digital Marketing
• Advanced Excel

Users can select a course from a drop-down list.

Steps to Create a Drop-Down List

1. Select the cells.
2. Go to Data → Data Validation.
3. Select List under Allow.
4. Enter the required options.
5. Click OK.

Other Data Validation Options

Excel can restrict entries to:

• Whole Number
• Decimal
• List
• Date
• Time
• Text Length
• Custom Formula

Benefits

• Reduces typing errors
• Maintains consistency
• Controls data entry
• Creates professional forms
• Improves data accuracy

---

5. Remove Duplicates

Duplicate data means the same record appears more than once.

Excel provides the Remove Duplicates tool to identify and remove repeated records.

Example

The record for Roll No. 101 appears twice.

Steps

1. Select the data.
2. Go to Data → Remove Duplicates.
3. Select the columns to check.
4. Click OK.
5. Excel removes duplicate records and displays a result message.

Tip: Keep a backup or copy of the original data before removing duplicates.

---

6. Text to Columns

Text to Columns separates data from one column into multiple columns.

Example

Suppose a cell contains:

Rahul Sharma

You can separate it into:

Common Delimiters

• Comma ,
• Space
• Tab
• Semicolon ;
• Other custom characters

Steps

1. Select the column containing the data.
2. Go to Data → Text to Columns.
3. Choose Delimited or Fixed Width.
4. Select the delimiter.
5. Preview the result.
6. Choose the destination if required.
7. Click Finish.

Practical Uses

Text to Columns can be used to separate:

• First Name and Last Name
• City and State
• Product Code and Product Name
• Country and State
• Full addresses

---

7. Flash Fill

Flash Fill automatically recognizes a pattern in your data and fills the remaining values.

Example

Suppose Column A contains:

If you manually enter Rahul in the next column, Excel may recognize the pattern and automatically extract the first names for the remaining records.

Steps

1. Enter the desired result for the first record.
2. Start entering the next result.
3. Excel may display a suggested pattern.
4. Press Enter to accept it.

You can also use:

Data → Flash Fill

or the shortcut:

Ctrl + E

Uses of Flash Fill

• Separating names
• Combining names
• Changing text format
• Extracting specific information
• Creating customized data

---

8. Find and Replace

Find and Replace helps locate specific information and replace it with new information.

Example

If the word Delhi appears many times and you want to change it to New Delhi, you can use Find and Replace.

Steps

1. Go to Home → Find & Select → Replace.
2. Enter the existing text in Find what.
3. Enter the new text in Replace with.
4. Select Replace or Replace All.

Shortcuts

Important

Use Replace All carefully because it can change every matching entry in the selected worksheet.

---

9. Subtotal

The Subtotal feature automatically calculates summary values for grouped data.

It can calculate:

• Sum
• Average
• Count
• Maximum
• Minimum
• Product

Example

Suppose sales data is grouped by city:

Subtotal can calculate the total sales for each city.

Basic Steps

1. Sort the data by the column you want to group.
2. Select the data.
3. Go to Data → Subtotal.
4. Select the grouping column.
5. Select the required calculation.
6. Select the column to calculate.
7. Click OK.

The Subtotal command may not be available in the same way when working with an Excel Table; converting the table to a normal range may be required.

---

10. Group and Outline

Grouping allows related rows or columns to be collapsed and expanded.

It is useful when working with large worksheets.

Example

A sales report may contain data for:

• January
• February
• March
• April
• May
• June

You can group monthly data and collapse it to show only summary information.

Steps

1. Select the rows or columns.
2. Go to Data → Group.
3. Excel creates an outline.
4. Use the + / − buttons to expand or collapse the group.

---

11. Consolidate Data

The Consolidate tool combines information from multiple ranges or worksheets into one summary.

Example

A company may maintain separate sales sheets for:

• Delhi
• Mumbai
• Bangalore
• Chennai

Consolidate can combine the data into a summary report.

Common Functions

• Sum
• Average
• Count
• Maximum
• Minimum

Basic Steps

1. Select the destination cell.
2. Go to Data → Consolidate.
3. Select the required function.
4. Add the source ranges.
5. Click OK.

---

12. Sort and Filter with Multiple Conditions

Excel can sort data using multiple levels.

Example

You can sort:

Course → Student Name → Marks

This means Excel first sorts by Course, then Student Name, and finally Marks.

Steps

1. Select the data.
2. Go to Data → Sort.
3. Select the first sorting level.
4. Click Add Level.
5. Select the second condition.
6. Add additional levels if required.
7. Click OK.

This is useful for large student, employee, inventory, and sales databases.

---

13. Data Cleaning

Data cleaning means identifying and correcting inaccurate, incomplete, duplicated, or inconsistent information.

Common Data Cleaning Tasks

• Remove duplicates
• Correct spelling
• Remove unnecessary spaces
• Standardize formats
• Separate data into columns
• Find missing values
• Correct incorrect entries
• Apply consistent number/date formats

Example

Incorrect:

Rahul Sharma

Rahul Sharma

rahul sharma

These entries may represent the same person but have inconsistent formatting.

---

14. Practical Example – Student Database

Create the following worksheet:

Perform the following tasks:

1. Sort students according to Marks.
2. Apply a filter to show only ADCA students.
3. Filter students whose marks are greater than 60.
4. Create a drop-down list for Course.
5. Add a duplicate record and remove it using Remove Duplicates.
6. Use Text to Columns to separate Student Name.
7. Use Flash Fill to extract first names.
8. Use Find and Replace to change a city name.
9. Create a subtotal for student fees by course.
10. Group selected rows and practice collapsing and expanding them.

---

Practical Activity

Project: Excel Data Management

Create a workbook named:

ADCA_Excel_Data_Tools.xlsx

Create a worksheet named Student Database and enter at least 20 student records.

Perform these tasks:

• Sort the student data by marks.
• Apply filters for different courses.
• Apply number-based filters.
• Create a Course drop-down using Data Validation.
• Remove duplicate records.
• Separate full names using Text to Columns.
• Use Flash Fill.
• Use Find and Replace.
• Create subtotals for fees.
• Group selected rows.
• Format the final worksheet professionally.

Learning Outcome

After completing this practical activity, students should be able to manage and clean a large Excel dataset using built-in Data Tools.

---

Important Excel Data Tools Shortcuts