Scribd
Upload
5 views3 pages

Filtering Advanced

The document outlines an experiment focused on sorting and filtering a business dataset containing 30 records. It provides detailed tasks for basic, multi-level, custom sorting, and various filtering techniques, including advanced filtering and top/bottom analysis. Specific formulas for generating dataset columns and step-by-step instructions for each task are included.

Uploaded by

ishaan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

Filtering Advanced

The document outlines an experiment focused on sorting and filtering a business dataset containing 30 records. It provides detailed tasks for basic, multi-level, custom sorting, and various filtering techniques, including advanced filtering and top/bottom analysis. Specific formulas for generating dataset columns and step-by-step instructions for each task are included.

Uploaded by

ishaan
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Experiment: Sorting and Filtering Data

Objective: Apply sorting (basic, multi-level, custom) and filtering (basic, multiple, advanced)
on a business dataset.

Dataset (30 records – use formulas)

Columns: Customer ID | Name | Region | Category | Sales | Discount | Priority | Delivery


Days

Formulas (enter in row 2 and drag):

 ID: ="C"&TEXT(ROW(A2),"000")

 Name: ="Cust"&ROW(A2)

 Region: =INDEX({"North","South","East","West"},RANDBETWEEN(1,4))

 Category:
=INDEX({"Electronics","Clothing","Furniture","Grocery"},RANDBETWEEN(1,4))

 Sales: =RANDBETWEEN(2000,50000)

 Discount: =RANDBETWEEN(0,30)

 Priority: =INDEX({"High","Medium","Low"},RANDBETWEEN(1,3))

 Delivery Days: =RANDBETWEEN(1,10)

Tasks

1. Basic Sorting

 Sort Sales (Smallest→Largest)

 Then Discount (Largest→Smallest)

Steps:
Select data → Data → Sort → choose column → apply order → OK

2. Multi-Level Sorting

Sort by:

1. Region (A→Z)

2. Category (A→Z)
3. Sales (Largest→Smallest)

Steps:
Data → Sort → Add Level → set order of columns carefully → OK

Note: Order of levels matters (first = highest priority)

3. Custom Sorting

Sort Priority as: High → Medium → Low

Steps:
Data → Sort → Order → Custom List → define sequence → OK

(Google Sheets: use helper column with MATCH)

4. Basic Filtering

 Sales > 20000

 Region = North

Steps:
Data → Filter → apply condition in dropdowns

5. Multiple Condition Filtering

Conditions:

 Region = West

 Sales > 10000

 Discount < 20

Note: All conditions work together (AND logic)

6. Advanced Filter (Excel)

Criteria:

 Category = Electronics
OR

 Delivery Days > 7


Steps:
Create criteria range → Data → Advanced Filter → Copy to another location

7. Top/Bottom Analysis

 Top 5 by Sales

 Bottom 5 by Delivery Days

Steps:
Use Number Filters → Top 10 → modify to 5

You might also like