1.

Filter in Excel

What is a Filter?
A Filter allows you to display only the rows in a dataset that meet certain conditions. Rows
that do not match the condition are temporarily hidden — they are NOT deleted.

How to Apply AutoFilter

1. Click any cell inside your data range.
2. Go to the Data tab → click Filter (or press Ctrl + Shift + L).
3. Drop-down arrows appear in each column header.
4. Click the arrow on the column you want to filter.
5. Choose values, text filters, number filters, or date filters.
6. Click OK.

Types of Filter Options

Syntax / Description Example
Select specific values Tick/untick checkboxes in the dropdown list

Text Filters → Contains Show rows where cell contains a keyword

Text Filters → Begins With Show rows where cell starts with a
letter/word

Number Filters → Greater Show rows where value > a number you type
Than
Number Filters → Top 10 Show top or bottom N values

Date Filters → This Month Show rows with dates in the current month

Search Box Type to search and filter in real time

Tip: To clear a filter on one column, click its dropdown → Clear Filter. To remove all filters,
press Ctrl + Shift + L again.

2. Advanced Filter

What is Advanced Filter?

Advanced Filter lets you filter data using complex, multi-column criteria written in a separate
'Criteria Range' on the worksheet. It can also copy the filtered results to another location.

Setting Up the Criteria Range

 • Copy the exact column headers to an empty area of the sheet.
• Below each header, type the criteria.
• Criteria in the SAME ROW = AND condition (both must be true).
• Criteria in DIFFERENT ROWS = OR condition (either can be true).

Steps to Use Advanced Filter

7. Click any cell in your data table.
8. Go to the Data tab → Sort & Filter group → Advanced.
9. Choose: Filter the list, in-place OR Copy to another location.
10. Set List Range: select your entire data including headers.
11. Set Criteria Range: select your criteria table (headers + conditions).
12. If copying, set Copy to: the destination cell.
13. Click OK.

Criteria Examples
Syntax / Description Example
Equal to value Under Dept header: type Sales

Greater than number Under Salary header: type >50000

Wildcards Under Name header: type A* (starts with A)

AND condition Same row: Dept = Sales AND Salary > 50000

OR condition Row 1: Dept = Sales Row 2: Dept = HR

Unique records only Tick 'Unique Records Only' checkbox in dialog

Note: Advanced Filter is powerful for extracting data to a new sheet or range without
disturbing the original data.

3. Logical Functions

Logical functions return TRUE or FALSE based on a condition. They are the foundation of
decision-making in Excel formulas.

3.1 IF Function
The IF function tests a condition and returns one value if TRUE and another value if FALSE.

Syntax: =IF(logical_test, value_if_true, value_if_false)

Examples
Syntax / Description Example
Pass/Fail based on marks =IF(B2>=40, "Pass", "Fail")

Bonus check =IF(C2>100000, "Bonus", "No Bonus")

Blank cell handling =IF(A2="", "Empty", "Has Data")

Return a number =IF(A2>0, A2*0.1, 0)

3.2 IF with OR
Use OR inside IF when you want TRUE if ANY one of multiple conditions is met.
Syntax: =IF(OR(cond1, cond2, ...), value_if_true, value_if_false)

Syntax / Description Example

Pass if Maths OR English =IF(OR(B2>=40, C2>=40), "Pass", "Fail")
>= 40
Eligible if Dept = HR or =IF(OR(A2="HR", A2="Admin"), "Yes", "No")
Admin
Weekend check =IF(OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7),
"Weekend", "Weekday")

3.3 IF with AND

Use AND inside IF when ALL conditions must be true to return the TRUE result.
Syntax: =IF(AND(cond1, cond2, ...), value_if_true, value_if_false)

Syntax / Description Example

Both Maths AND English =IF(AND(B2>=40, C2>=40), "Pass", "Fail")
>= 40
Senior Employee in Sales =IF(AND(A2="Sales", B2>5), "Senior", "Junior")

Amount within range =IF(AND(A2>=100, A2<=500), "In Range", "Out of

Range")

3.4 IF with NOT

Use NOT inside IF to reverse a condition — TRUE becomes FALSE and vice versa.
Syntax: =IF(NOT(condition), value_if_true, value_if_false)

Syntax / Description Example

 Not equal to a value =IF(NOT(A2="Admin"), "Non-Admin", "Admin")

Not blank =IF(NOT(ISBLANK(A2)), "Has Data", "Blank")

Not in a department =IF(NOT(A2="HR"), "Other Dept", "HR Dept")

3.5 IFS Function

IFS checks multiple conditions in order and returns the value for the first TRUE condition. It
replaces deeply nested IFs and is much easier to read.
Syntax: =IFS(cond1, val1, cond2, val2, cond3, val3, ...)

Syntax / Example
Description
Grade from marks =IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C",
B2>=40,"D", TRUE,"F")

Salary category =IFS(C2>80000,"High", C2>50000,"Medium",

TRUE,"Low")

Sales performance =IFS(D2>200,"Excellent", D2>150,"Good",

label D2>100,"Average", TRUE,"Poor")

Note: Always end IFS with TRUE as the last condition to act as a catch-all default, otherwise
it returns #N/A if no condition is met.

3.6 Nested IF
A Nested IF places another IF function inside the value_if_true or value_if_false argument.
Use when you have multiple mutually exclusive outcomes.
Syntax: =IF(cond1, val1, IF(cond2, val2, IF(cond3, val3, default)))

Syntax / Example
Description
3-level grade =IF(B2>=75,"Distinction", IF(B2>=60,"Merit",
IF(B2>=40,"Pass","Fail")))

Tax slab =IF(A2>1000000, A2*0.30, IF(A2>500000, A2*0.20,

IF(A2>250000, A2*0.10, 0)))

Shipping tier =IF(B2>5000,"Free Shipping", IF(B2>2000,"Discounted",

"Full Price"))

Tip: Excel allows up to 64 levels of nesting, but beyond 3–4 levels use IFS or SWITCH for
readability.

4. Statistical & Conditional Functions

4.1 SUMIF
SUMIF adds up values in a range that meet a single condition.
Syntax: =SUMIF(range, criteria, [sum_range])

Argument Meaning Example

range Column to check the condition A2:A20

criteria The condition to match "Sales" or >5000

sum_rang Column to sum (if different from range) C2:C20

e

SUMIF Examples
Syntax / Description Example
Sum Sales dept =SUMIF(A2:A20, "Sales", C2:C20)
salaries
Sum amounts > 1000 =SUMIF(B2:B20, ">1000", B2:B20)

Sum for specific month =SUMIF(D2:D50, "January", E2:E50)

Wildcard – starts with A =SUMIF(A2:A20, "A*", C2:C20)

4.2 SUMIFS
SUMIFS adds up values that meet MULTIPLE conditions simultaneously.
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1,
criteria_range2, criteria2, ...)

Syntax / Description Example

Sales dept AND =SUMIFS(C2:C20, A2:A20, "Sales", C2:C20, ">50000")
salary > 50000
Product A in Region =SUMIFS(D2:D50, A2:A50, "ProductA", B2:B50,
North "North")

Amount in date range =SUMIFS(E2:E100, D2:D100, ">="&DATE(2024,1,1),

D2:D100, "<="&DATE(2024,12,31))

4.3 AVERAGEIF
AVERAGEIF calculates the average of cells that meet a single condition.
Syntax: =AVERAGEIF(range, criteria, [average_range])
 Syntax / Description Example
Avg salary of Sales =AVERAGEIF(A2:A20, "Sales", C2:C20)
dept
Avg of values above 0 =AVERAGEIF(B2:B20, ">0", B2:B20)

Avg marks for Grade A =AVERAGEIF(D2:D50, "A", C2:C50)

4.4 AVERAGEIFS
AVERAGEIFS calculates the average of cells that meet MULTIPLE conditions.
Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1,
criteria_range2, criteria2, ...)

Syntax / Description Example

Avg salary: Sales, =AVERAGEIFS(C2:C20, A2:A20, "Sales", D2:D20,
experience>3 ">3")

Avg score: Female students, =AVERAGEIFS(E2:E50, C2:C50, "Female", E2:E50,

>60 ">60")

4.5 COUNTIF
COUNTIF counts the number of cells that meet a single condition.
Syntax: =COUNTIF(range, criteria)

Syntax / Description Example

Count employees in Sales =COUNTIF(A2:A20, "Sales")

Count values greater than 50 =COUNTIF(B2:B20, ">50")

Count non-blank cells =COUNTIF(C2:C20, "<>")

Count cells containing 'North' =COUNTIF(D2:D20, "*North*")

4.6 COUNTIFS
COUNTIFS counts cells that meet MULTIPLE conditions simultaneously.
Syntax: =COUNTIFS(range1, criteria1, range2, criteria2, ...)

Syntax / Description Example

Sales dept AND salary =COUNTIFS(A2:A20, "Sales", C2:C20, ">50000")
> 50000
 Female employees in =COUNTIFS(B2:B50, "Female", C2:C50, "Delhi")
Delhi
Orders between 2 =COUNTIFS(D2:D100, ">="&DATE(2024,1,1), D2:D100,
dates "<="&DATE(2024,3,31))

Quick Reference: SUMIF vs SUMIFS vs COUNTIF vs COUNTIFS

Function Purpose Conditions Result
SUMIF Sum values 1 condition Total (number)
SUMIFS Sum values Multiple Total (number)
AVERAGEIF Average values 1 condition Average
(number)
AVERAGEIFS Average values Multiple Average
(number)
COUNTIF Count cells 1 condition Count (integer)
COUNTIFS Count cells Multiple Count (integer)

5. Pivot Table

What is a Pivot Table?

A Pivot Table is an interactive summary table that lets you quickly group, count, sum,
average, and analyse large datasets without writing formulas. You simply drag and drop
fields to reorganise the data view.

Creating a Pivot Table

14. Ensure your data has headers in the first row with no blank rows/columns.
15. Click any cell inside the data.
16. Go to Insert tab → PivotTable.
17. Choose: New Worksheet or Existing Worksheet for placement.
18. Click OK — the PivotTable field pane appears on the right.

The Four Areas of a Pivot Table

Area What to put here Example
Rows Categories shown as row labels (left side) Department, Product, City

Columns Sub-categories shown as column headings Month, Quarter, Year

 Values Numeric field to summarise (sum, avg, count, Sales Amount, Salary,
etc.) Quantity
Filters Field used to filter the entire report Region, Year, Category

Key Pivot Table Operations

Change Summary Function

• Right-click any value in the Values area → Value Field Settings.
• Choose: Sum, Count, Average, Max, Min, Product, etc.

Grouping Data
• Dates: Right-click a date → Group → choose Days, Months, Quarters, Years.
• Numbers: Right-click a number field → Group → set Start, End, and interval.

Sorting & Filtering

• Click the dropdown arrow on a Row/Column label to sort or filter.
• Use the Filters area to filter the entire pivot by a field.
• Insert → Slicer for a visual filter panel with clickable buttons.
• Insert → Timeline for a visual date filter slider.

Refresh a Pivot Table

• When source data changes, right-click the Pivot Table → Refresh.
• Or go to PivotTable Analyze tab → Refresh.

Show Values As
• Right-click a value → Show Values As → % of Grand Total, Running Total, Rank,
etc.

Best Practice: Always format your source data as an Excel Table (Ctrl+T) before
creating a Pivot Table. This way the Pivot automatically includes new rows when you
Refresh.

6. Pivot Chart

What is a Pivot Chart?

A Pivot Chart is a visual representation of a Pivot Table. It is linked to the Pivot Table —
changes in the Pivot Table automatically reflect in the chart, and you can filter the chart
using the same slicers and field buttons.
Creating a Pivot Chart

Method 1: From an Existing Pivot Table

19. Click anywhere inside the Pivot Table.
20. Go to PivotTable Analyze tab → PivotChart.
21. Choose the chart type (Bar, Column, Line, Pie, etc.).
22. Click OK.

Method 2: Create Both Together

23. Click any cell in your source data (not the Pivot Table).
24. Go to Insert tab → PivotChart → PivotChart & PivotTable.
25. Both are created simultaneously.

Key Features of Pivot Charts

Feature Description
Field Visible on chart – click to filter directly on the chart
Buttons
Slicers Visual filter panels shared between Pivot Table and Chart
Drill Down Click a data point to drill into a subset of data
Chart Types Column, Bar, Line, Pie, Area — all work with Pivot Charts (except XY Scatter
and Bubble)
Auto Automatically updates when source Pivot Table is refreshed
Refresh
Formatting Use Chart Design and Format tabs to customise colours, labels, title, etc.

Customising a Pivot Chart

• Change chart type: Right-click chart → Change Chart Type.
• Add Data Labels: Chart Design tab → Add Chart Element → Data Labels.
• Move chart: Chart Design → Move Chart → New Sheet (for full-page view).
• Hide field buttons: PivotChart Analyze → Field Buttons → Hide All.
• Apply styles: Chart Design tab → Chart Styles gallery.

Tip: Pivot Charts are ideal for dashboards. Combine multiple Pivot Charts with shared
Slicers on one sheet to create an interactive, click-to-filter business dashboard.