Modern Excel and Power ToolsModule 1: Dynamic Arrays & Modern Formula Spilling

FILTER Function: Extract Sales Data Meeting Multiple Criteria

Master the FILTER function with AND (*), OR (+), and NOT logic to dynamically extract filtered corporate records without helper columns or manual filters.

Free preview

You are previewing a full lesson.

This lesson is a free preview. No account is needed to read it.

Interactive Study Modes:Switch between stacked, split-screen workbench, or cinematic theater mode.

FILTER Function: Extract Sales Data Meeting Multiple Criteria

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Open VBA EditorLaunches the Visual Basic Editor to inspect, debug, and run macro code.

⌥AltF11

Launch Power Query EditorOpens Power Query window to transform, clean, and reshape incoming data.

⌥AltAPT

Insert PivotTableCreates an instant summary PivotTable from any structured data table.

⌥AltNV

Reference Spill RangeAppends the hash symbol to dynamically capture the entire spilled array range.

#

Evaluate Formula Step-by-StepSteps through complex nested formula logic to debug errors and intermediate results.

⌥AltMV

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

⚡ Power Tools & Macros Note: In-browser sandbox simulates Excel formula syntax and math logic. For compiled VBA macros and native Power Query M-code transforms, please open the practice workbooks provided above in desktop Microsoft Excel (365 / 2021).

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Hint:Use formula =SUM(D2:D5)

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Lesson notes & syllabus guide

FILTER Function: Dynamic Multi-Criteria Extraction

Traditional Excel me data filter karne ke liye AutoFilter click karna padta tha ya complex INDEX-MATCH-AGGREGATE helper formulas banane padte the. Modern Excel ka FILTER function pure dataset ko dynamically query karta hai.

1. Syntax & Core Mechanics

=FILTER(array, include, [if_empty])

array: Original data table ya range (e.g., A2:E200).
include: Boolean array criteria. TRUE rows return hongi, FALSE filter out hongi.
if_empty: Optional message (e.g., "No Records Found") agar koi match na mile.

2. Multi-Criteria Filtering: AND Logic (`*`) & OR Logic (`+`)

Corporate reporting me single criteria kaafi nahi hota. Example: Tata Motors EV dealer sales me hume wo dealers chahiye jo "West" region me hain AUR jinki quarterly sales > ₹50,00,000 hain:

=FILTER(A2:F150, (C2:C150="West") * (E2:E150>5000000), "No Qualifying EV Dealers")

Agar hume "Nexon EV" YA "Punch EV" me se kisi ka bhi sales record chahiye (OR condition):

=FILTER(A2:F150, (D2:D150="Nexon EV") + (D2:D150="Punch EV"), "No Matching Models")

[!TIP] Excel me Boolean math follow hota hai: Multiplication (*) represents AND condition (both conditions must be 1/TRUE). Addition (+) represents OR condition (either condition can be 1/TRUE).

3. Slicing Output Columns: FILTER inside CHOOSECOLS

Agar pure 20 columns return nahi karne, balki sirf Dealer Name aur Revenue columns display karne hain:

=CHOOSECOLS(FILTER(A2:F150, C2:C150="West"), 2, 5)

Lesson resources

Download the workbooks and cheat sheets used in this lesson.

Tata Motors EV Dealer Sales Registry — Practice Workbooktata_motors_ev_dealer_sales_before.xlsx

FILTER Dynamic Array Function Quiz

Pass mark 70%

5 questions on FILTER arguments, Boolean AND/OR logic, and empty state handling.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Tata Motors EV Regional Performance Filter

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

FILTER Function: Dynamic Multi-Criteria Extraction

1. Syntax & Core Mechanics

=FILTER(array, include, [if_empty])

array: Original data table ya range (e.g., A2:E200).

include: Boolean array criteria. TRUE rows return hongi, FALSE filter out hongi.

if_empty: Optional message (e.g., "No Records Found") agar koi match na mile.

2. Multi-Criteria Filtering: AND Logic (*) & OR Logic (+)

Corporate reporting me single criteria kaafi nahi hota. Example: Tata Motors EV dealer sales me hume wo dealers chahiye jo "West" region me hain AUR jinki quarterly sales > ₹50,00,000 hain:

=FILTER(A2:F150, (C2:C150="West") * (E2:E150>5000000), "No Qualifying EV Dealers")

Agar hume "Nexon EV" YA "Punch EV" me se kisi ka bhi sales record chahiye (OR condition):

=FILTER(A2:F150, (D2:D150="Nexon EV") + (D2:D150="Punch EV"), "No Matching Models")

[!TIP] Excel me Boolean math follow hota hai: Multiplication (*) represents AND condition (both conditions must be 1/TRUE). Addition (+) represents OR condition (either condition can be 1/TRUE).

3. Slicing Output Columns: FILTER inside CHOOSECOLS

Agar pure 20 columns return nahi karne, balki sirf Dealer Name aur Revenue columns display karne hain:

=CHOOSECOLS(FILTER(A2:F150, C2:C150="West"), 2, 5)

FILTER Function: Extract Sales Data Meeting Multiple Criteria

You are previewing a full lesson.

FILTER Function: Extract Sales Data Meeting Multiple Criteria

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Lesson notes & syllabus guide

FILTER Function: Dynamic Multi-Criteria Extraction

1. Syntax & Core Mechanics

2. Multi-Criteria Filtering: AND Logic (*) & OR Logic (+)

3. Slicing Output Columns: FILTER inside CHOOSECOLS

Lesson resources

FILTER Dynamic Array Function Quiz

Tata Motors EV Regional Performance Filter

Lesson Q&A & Peer Discussions

FILTER Function: Extract Sales Data Meeting Multiple Criteria

You are previewing a full lesson.

FILTER Function: Extract Sales Data Meeting Multiple Criteria

Interactive Spreadsheet Practice Sandbox

Exercise 1: Basic SUM Addition

Lesson notes & syllabus guide

FILTER Function: Dynamic Multi-Criteria Extraction

1. Syntax & Core Mechanics

2. Multi-Criteria Filtering: AND Logic (*) & OR Logic (+)

3. Slicing Output Columns: FILTER inside CHOOSECOLS

Lesson resources

FILTER Dynamic Array Function Quiz

Tata Motors EV Regional Performance Filter

Lesson Q&A & Peer Discussions

2. Multi-Criteria Filtering: AND Logic (`*`) & OR Logic (`+`)

2. Multi-Criteria Filtering: AND Logic (`*`) & OR Logic (`+`)