Chapter 13 | Data View and Extraction

Advanced Sorting and Filtering

Turn large datasets into clear working views without disturbing their structure. Learn multi-level sorting, custom business orders, colour-based sorting, powerful filters, Advanced Filter criteria and formula-driven report extraction.

Start Learning Practical Assignment

Multi-Level SortingArrange records using several business priorities.

Focused AnalysisDisplay only records that match meaningful conditions.

Advanced ExtractionCopy qualifying and unique records to a report area.

Dynamic ReportsBuild formula-driven outputs that update automatically.

Choose Language

Practical, business-oriented learning

Learning Objectives

What You Will Master

By the end of this chapter, you will be able to sort and filter business data accurately, extract decision-ready records and choose the safest method for each reporting requirement.

Sort Correctly

Apply single-level and multi-level sorting without separating related records.

Use Business Order

Create custom lists for priorities, regions, months, grades and workflow stages.

Filter Deeply

Use text, number, date, colour and multi-condition filters for focused analysis.

Extract Reports

Use Advanced Filter and dynamic-array formulas to create reusable report outputs.

Lesson 1

Prepare Data Before Sorting or Filtering

Sorting and filtering are reliable only when the dataset is rectangular, consistent and treated as one connected record system.

1 Understand What Changes—and What Does Not

Sorting changes the visible order of records. Filtering temporarily hides records that do not match the selected conditions. Neither method should break the relationship between columns in the same row.

Record Integrity: Every cell belonging to one transaction, employee, student or customer must move together as a complete row whenever the data is sorted.

One Header RowUse unique, descriptive column headings without blank header cells.

Continuous RangeAvoid fully blank rows or columns inside the dataset.

Consistent TypesDo not mix numbers with text numbers or real dates with date-like text.

Excel TableConvert the range into a Table for safer expansion and visible filter controls.

Pre-Operation Checklist

Save the workbook or create a backup before reorganizing critical data.
Remove merged cells from the working dataset because they can block reliable operations.
Confirm formulas are filled consistently and do not reference the wrong row.
Identify subtotals, section titles and notes that should not be included in the main sort range.
Verify that hidden rows are understood; sorting can still reorganize records that are hidden manually.

1Identify

Define the business question and required result.

2Inspect

Check headers, blanks, types and merged cells.

3Select

Work inside the full dataset or Excel Table.

4Apply

Choose the correct sort or filter method.

5Verify

Reconcile record count and sample key rows.

Practical Experiment 1: Dataset Readiness Audit

Use a sales or employee dataset with at least 30 records.

Step 1: Inspect

Find blank headers, merged cells, totals inside the range and inconsistent data types.

Step 2: Correct

Convert the cleaned range into an Excel Table and assign a meaningful Table name.

Step 3: Validate

Record the row count and confirm that each record has a unique identifier.

Learning Output: A sort-ready and filter-ready professional dataset.

Lesson 2

Apply Multi-Level Sorting

A multi-level sort creates a business hierarchy: first by the most important field, then by secondary fields used to organize ties.

2 Build a Logical Sort Hierarchy

Use Data → Sort when one condition is not enough. Each level is evaluated from top to bottom. The first level creates the main groups; later levels arrange records inside those groups.

Business Requirement	Level 1	Level 2	Level 3
Regional sales review	Region A–Z	Salesperson A–Z	Amount Largest to Smallest
Order dispatch priority	Status Custom Order	Required Date Oldest to Newest	Order Value Largest to Smallest
Employee list	Department A–Z	Designation Custom Order	Employee Name A–Z
Outstanding collection	Overdue Days Largest to Smallest	Balance Largest to Smallest	Customer A–Z

Important Sort Dialog Controls

My data has headers: Keep this selected so headings are not mixed into the records.
Sort On: Usually Cell Values, but Font Colour, Cell Colour and Conditional Formatting Icon are also available.
Order: A–Z, Z–A, Smallest to Largest, Oldest to Newest or a Custom List.
Add Level: Adds a secondary or tertiary condition.
Copy Level: Duplicates a similar condition for faster setup.
Options: Controls case sensitivity and left-to-right sorting when specially required.

Expand Selection: When Excel detects adjacent data, choose Expand the selection unless you intentionally want to sort only one independent column. Sorting one column inside a connected dataset can permanently misalign records.

Practical Experiment 2: Three-Level Sales Sort

Step 1: Main Group

Sort the dataset by Region from A to Z.

Step 2: Internal Order

Add Salesperson A to Z, then Amount Largest to Smallest.

Step 3: Verify

Confirm transaction IDs remain matched with the original customers and values.

Learning Output: A correctly prioritized regional sales listing.

Lesson 3

Use Custom Lists, Colours and Icons

Alphabetical order is not always business order. Advanced sorting allows workflow stages, weekdays, months, priorities and visual indicators to follow a meaningful sequence.

3 Create Business-Specific Sort Order

Custom ListCritical, High, Medium, Low

Use when priorities must appear in operational order rather than alphabetical order.

Workflow StageNew, In Review, Approved, Closed

Useful for leads, applications, orders and service tickets.

Designation LevelDirector, Manager, Executive, Trainee

Creates a clear hierarchy inside department-wise employee reports.

Sort by Colour or Icon

Excel can move cells with a selected fill colour, font colour or conditional-formatting icon to the top or bottom. This is useful for temporary operational review, but colours should not be the only source of meaning.

Professional Tip: Store the true business status in a dedicated column such as Priority or Risk Level. Use colour as a visual aid, then sort primarily by the status field. This produces a more auditable workbook.

Case-Sensitive and Left-to-Right Sorting

Case-sensitive sort: Available through Sort Options when uppercase and lowercase values must be treated differently.
Left-to-right sort: Reorders columns based on values in a selected row. Use it only for specially structured horizontal reports—not normal databases.

Practical Experiment 3: Priority and Status Sort

Step 1: Define Order

Create a custom list: Critical, High, Medium, Low.

Step 2: Add Levels

Sort by Priority using the custom list, then Required Date oldest to newest.

Step 3: Compare

Compare the result with alphabetical sorting and explain which supports action better.

Learning Output: An action-oriented operational report arranged by true priority.

Lesson 4

Master AutoFilter and Column Filters

AutoFilter supports fast exploration of data through selections, search, condition filters, colour filters and combined criteria across several columns.

4 Build Focused Views Without Deleting Records

Text Filters

Equals, Does Not Equal, Begins With, Ends With, Contains and Custom Filter.

Number Filters

Greater Than, Between, Top 10, Above Average and Below Average.

Date Filters

Today, This Week, Last Month, Year to Date, Between and date hierarchy.

How Multiple Filters Work

Filters applied to different columns generally work with AND logic. For example, Region = West and Status = Pending and Amount > $5,000 displays only records satisfying all three conditions.

Use the search box to locate exact or partial items inside a long dropdown list.
Use Filter by Colour only when colour has been applied consistently.
Choose Clear Filter From… to remove one column’s condition while preserving others.
Choose Clear on the Data tab to remove all active filters without removing filter arrows.
Use the funnel icon on the heading to identify columns with active conditions.

Region = "West" AND Status = "Pending"

Apply one filter on Region and another on Status. Both conditions must be true.

Amount >= 5000 AND Amount <= 20000

Use Number Filters → Between to define an inclusive value band.

Top 10 Items → Top 5 by Amount

The dialog can return a selected count or percentage—not only ten records.

Date Filters → This Month

Excel uses real date values to generate relative period filters.

Practical Experiment 4: Multi-Column Filter Review

Step 1: Apply

Filter one region, pending status and amounts above $5,000.

Step 2: Count

Use the status bar or SUBTOTAL to confirm the number of visible records.

Step 3: Clear Carefully

Remove only the amount filter, observe the change, then clear all conditions.

Learning Output: A controlled multi-condition view and a clear understanding of AND logic.

Lesson 5

Use Advanced Filter and Criteria Ranges

Advanced Filter is valuable when conditions are complex, OR logic is needed, qualifying records must be copied elsewhere or unique values must be extracted.

5 Design a Correct Criteria Range

A criteria range is a small worksheet area containing headings copied exactly from the dataset and the conditions to apply beneath them.

Criteria Layout	Logic	Meaning
Region = West and Status = Pending on the same row	AND	Both conditions must be true for one record.
Region = West on one row; Region = North on another	OR	Either region is accepted.
Amount headings repeated twice on the same row with >=5000 and <=20000	AND range	Amount must fall inside the selected band.
Formula criterion using a blank or unique heading	Calculated condition	A TRUE/FALSE formula evaluates the first data row and is applied to all records.

Same Row = ANDWest | Pending | >5000

All criteria entered across one criteria row must be satisfied.

Different Rows = OR

West
North

Each criteria row represents an alternative accepted combination.

Extract UniqueUnique records only

Copy distinct records or a distinct list to another worksheet area.

Advanced Filter Procedure

Create the criteria range away from the dataset and copy headings exactly.
Click inside the dataset and choose Data → Advanced.
Choose Filter the list, in-place or Copy to another location.
Confirm the List range, Criteria range and Copy to range.
Select Unique records only when distinct output is required.
Validate the extracted record count and sample results.

Exact Heading Rule: A misspelled or altered criteria heading may be treated as a formula criterion or may fail to filter as expected. Copy headings from the source instead of retyping them.

Practical Experiment 5: AND/OR Criteria Extraction

Step 1: Build Criteria

Request West-Pending records OR North-Approved records above the chosen amount.

Step 2: Extract

Copy qualifying records to a separate report area using Advanced Filter.

Step 3: Audit

Manually test at least five source records against the criteria.

Learning Output: A standalone extracted report created from complex conditions.

Practical Experiment 6: Unique Customer List

Step 1: Select Field

Create a one-column list containing repeated customer names or customer IDs.

Step 2: Advanced Filter

Copy the list elsewhere and select Unique records only.

Step 3: Compare

Compare the unique count with a UNIQUE formula where available.

Learning Output: A deduplicated master list and understanding of static versus dynamic extraction.

Lesson 6

Create Dynamic Sorted and Filtered Reports

Modern Excel formulas can return a separate report without changing the source order. The result updates whenever the source data or criteria change.

6 Combine SORT, SORTBY, FILTER and UNIQUE

=SORT(A2:F500,6,-1)

Returns the source rows sorted by the sixth column in descending order.

=SORTBY(A2:F500,F2:F500,-1,B2:B500,1)

Sorts by Amount descending and then Customer ascending without manually rearranging the source.

=FILTER(A2:F500,(C2:C500=H2)*(F2:F500>=H3),"No records")

Returns records matching a selected region and minimum amount.

=SORT(UNIQUE(C2:C500))

Creates an automatically updating, alphabetically arranged unique list.

AND and OR Logic in FILTER

AND: Multiply conditions: (Region=H2)*(Status=H3).
OR: Add conditions and test greater than zero: ((Region=H2)+(Region=H3))>0.
Exclude: Use not equal: Status<>"Cancelled".
Partial text: Use SEARCH with ISNUMBER for contains-style conditions.

Version Note: FILTER, SORTBY and UNIQUE require Microsoft 365, Excel 2021 or another compatible modern version. For older versions, use AutoFilter, Advanced Filter, PivotTables or helper formulas.

Practical Experiment 7: Dynamic Regional Report

Step 1: Criteria Cells

Create dropdowns for Region and Status plus a minimum Amount cell.

Step 2: Build Formula

Use FILTER to return qualifying records and SORTBY to rank them by Amount.

Step 3: Refresh Test

Change criteria and add a new source record to confirm automatic updating.

Learning Output: A reusable, criteria-driven management report.

Static View versus Dynamic Output

Method	Changes Source Order?	Copies Output?	Updates Automatically?	Best Use
Sort	Yes	No	Not after new entries unless repeated	Reorganizing working data
AutoFilter	No	No	Conditions remain, but newly changed data may need reapplication	Quick analysis
Advanced Filter	No	Optional	No; rerun after source changes	Complex or unique extraction
Dynamic arrays	No	Returns a separate spill result	Yes	Live reports and dashboards

Interactive Skill Lab

Choose the Right Sorting or Filtering Method

Select a requirement and receive a recommended Excel method, setup approach and quality check.

Business Requirement Primary Field Secondary Field or Status Criteria Cell / Output Start

Recommendation: Select a requirement and click the button.

Real-Time Practical Assignment

Build an Order Review and Extraction System

Create a professional workbook that allows a manager to arrange orders, inspect critical records and generate a separate action report.

Project Requirements

Use at least 100 order records with the fields: Order ID, Order Date, Customer, Region, Product, Salesperson, Status, Priority, Required Date, Amount and Outstanding Balance.

1Prepare the Source

Convert data into a named Excel Table, verify types and preserve a Raw Data sheet.

2Create Custom Order

Define Critical, High, Medium and Low as the business priority sequence.

3Build Sort View

Sort by Priority, Required Date and Outstanding Balance.

4Build Filter View

Display overdue, pending and high-value orders for a chosen region.

5Extract Action List

Use Advanced Filter or FILTER to generate a separate follow-up report.

6Audit and Present

Reconcile counts, test criteria and write three management observations.

Practical Experiment 8: Visible Record Summary

Step 1: Filter

Display only Pending and Partially Paid orders for the selected month.

Step 2: Calculate

Use SUBTOTAL to calculate visible order count and visible outstanding balance.

Step 3: Reconcile

Clear the filter and confirm the totals return to the full dataset values.

Learning Output: A filter-aware summary that responds to the visible records.

Unique IDsNo record ambiguity

Excel TableExpandable source

Criteria TestedBoundaries checked

Counts ReconciledSource versus output

Report ReadyClear and usable

AICPE Quality Learning Commitment: AICPE Gurukul focuses on practical, career-oriented and skill-based learning that can support office productivity, freelancing, employment and business reporting. Learn more at aicpeindia.org and aicpe.online.

Practice Worksheet

Complete These Skill Tasks

Save each output in a separate worksheet and document the method used.

Task 1: Multi-Level Sort

Arrange employee data by department, designation hierarchy and joining date.

Task 2: Custom Priority

Create a custom list and sort service tickets from Critical to Low priority.

Task 3: Value Filter

Display top five customers by sales and compare with Above Average filtering.

Task 4: Date Filter

Show records from the current month and then create a custom date range.

Task 5: Advanced Criteria

Extract two alternative region-status combinations to another worksheet area.

Task 6: Unique List

Create unique customer and product lists using Advanced Filter and UNIQUE.

Task 7: Dynamic Report

Combine FILTER and SORTBY with user-controlled criteria cells.

Task 8: Visible Summary

Use SUBTOTAL to calculate visible record count and total after filtering.

Common Mistakes

Mistakes Students Should Avoid

Most sorting and filtering errors come from weak data structure, unclear criteria or failure to verify the output.

Wrong Practices

Sorting only one column inside a connected dataset.
Including totals, headings or notes in the main sort range.
Using colours as the only business status information.
Forgetting that different filter columns combine with AND logic.
Retyping Advanced Filter headings with spelling differences.
Assuming an Advanced Filter extraction updates automatically.
Copying filtered data without checking whether hidden rows are included.
Failing to clear active filters before concluding records are missing.

Correct Practices

Work inside a clean Excel Table and expand the full selection.
Keep one header row and one record per row.
Store business meaning in fields, then use colour only as support.
Write the criteria logic before applying filters.
Copy criteria headings directly from the source.
Document whether the output is static or dynamic.
Use visible-cell-aware calculations and explicit copy procedures.
Reconcile record counts and sample identifiers after every major operation.

Remember: A visually correct report can still be logically wrong. Always test boundary values, sample source records and total record counts.

Quick Revision

Remember These Key Points

Review these principles before moving to Named Ranges and Formula Management.

Protect Record Integrity

Sort the complete dataset so all cells belonging to a record move together.

Prioritize Sort Levels

The first level creates the main groups; later levels organize records inside them.

Use Custom Business Order

Custom Lists are ideal for priority, workflow, month and hierarchy sequences.

Understand Filter Logic

Different AutoFilter columns normally combine with AND logic.

Design Criteria Carefully

Same criteria row means AND; different rows mean OR in Advanced Filter.

Choose Static or Dynamic

Advanced Filter extraction is static; dynamic-array reports update automatically.

Protected Learning Content