Part III: Data Management

Sorting and Filtering

🎯 Learning Objectives

• Sort data in ascending (A–Z) and descending (Z–A) order
• Perform multi-level sorting (e.g., by Department then by Salary)
• Sort by cell color, font color, and conditional formatting icons
• Apply AutoFilter to filter data by value, text, number, and date conditions
• Use Advanced Filter for criteria ranges, unique records, and copying results to another location
• Master dynamic array functions: SORT(), SORTBY(), and FILTER()

Sort Basics & Multi-Level Sorting

Simple Sort: A–Z and Z–A

Sorting arranges your data in a specific order. Ascending (A–Z) arranges text alphabetically, numbers from smallest to largest, and dates from earliest to latest. Descending (Z–A) does the reverse. Think of it like organizing your school register — names A to Z make it easy to look up any student.

Step-by-Step: Quick Sort

1. Click any cell in the column you want to sort (e.g., click on cell E2 in the Salary column)
2. Go to Data tab → Sort & Filter group
3. Click Sort A to Z (↑) for ascending or Sort Z to A (↓) for descending
4. Excel automatically detects the data range and sorts the entire table by that column

Example 1: Sort employees by Salary (ascending)

Click any cell in the Salary column → Data → Sort A to Z. Result:

Custom Sort & Multi-Level Sort

What if you want to sort by Department first, and then within each department, sort by Salary from highest to lowest? That's multi-level sorting — exactly how HR managers organize payroll reports at companies like TCS and Infosys.

Step-by-Step: Multi-Level Sort

1. Click any cell in your data range
2. Go to Data tab → click Sort (the full Sort button, not the quick A-Z buttons)
3. In the Sort dialog:
   • Sort by: Department → Order: A to Z
   • Click Add Level
   • Then by: Salary → Order: Largest to Smallest
4. Click OK

Example 2: Multi-level sort — Department (A–Z), then Salary (High to Low)

Custom Sort Order

Sometimes alphabetical order isn't what you want. For example, you might want months to sort as Jan, Feb, Mar... not Apr, Aug, Dec. Or departments in a specific business hierarchy: "Management → IT → Finance → HR → Marketing."

Step-by-Step: Custom Sort List

1. Data → Sort → In the Order dropdown, select Custom List...
2. Type your custom order in the List entries box (one item per line): Management, IT, Finance, HR, Marketing
3. Click Add, then OK

Example 3: Sort by custom department order

Custom order: IT, Finance, HR, Marketing. Result: All IT employees appear first, then Finance, then HR, then Marketing — regardless of alphabetical order.

Sort by Cell Color / Font Color / Icon

If you've used conditional formatting to highlight cells (e.g., red for low performers, green for high performers), you can sort by those colors.

1. Data → Sort → In Sort On, choose Cell Color, Font Color, or Conditional Formatting Icon
2. Select the color/icon you want on top
3. Add more levels for each color

Example 4: Sort by cell color

Suppose cells with Salary > ₹80,000 are highlighted green and Salary < ₹65,000 are red. Sort by Cell Color → Green on Top → Red on Bottom.

Example 5: Real-Life — CBSE Board Results Sorting

A CBSE school coordinator receives mark sheets for 500 students. She needs to sort by: Stream (Science, Commerce, Arts) first, then by Total Marks (highest first) within each stream, then by Name (A–Z) for students with the same marks.

Sort levels: 1) Stream → Custom List (Science, Commerce, Arts) 2) Total Marks → Largest to Smallest 3) Name → A to Z

Example 6: Real-Life — Zomato Restaurant Ratings Sort

A Zomato city manager exports restaurant data for Pune. He needs restaurants sorted by Cuisine (A–Z), then Rating (highest first), then Average Cost for Two (lowest first) to recommend affordable highly-rated options.

AutoFilter — Quick Data Filtering

Theory: What is Filtering?

Filtering hides rows that don't match your criteria, showing only the rows you need. Unlike sorting (which rearranges), filtering is non-destructive — your hidden rows are still there, just temporarily invisible. Think of it like a coffee filter: the coffee passes through, the grounds stay behind. Your data passes the test and is shown; the rest hides.

Step-by-Step: Enable AutoFilter

1. Click any cell in your data range
2. Go to Data tab → click Filter (or press Ctrl + Shift + L)
3. Dropdown arrows (▼) appear on each column header
4. Click a dropdown arrow to set filter criteria

Filter by Value (Checkbox Selection)

Click the dropdown on Department → Uncheck "Select All" → Check only "IT" → OK. Only IT department employees are now visible.

Example 7: Filter by Department = IT

Notice: Row numbers turn blue and the dropdown arrow shows a funnel icon (🔽) to indicate active filtering.

Text Filters

Click the dropdown → Text Filters → options include:

Example 8: Filter names beginning with "A" or "R"

Department dropdown → Text Filters → Custom AutoFilter → Name "Begins With" A Or Name "Begins With" R. Result: Amit Sharma, Anjali Desai, Rahul Verma, Rohan Joshi.

Number Filters

For numeric columns, you get number-specific options:

Example 9: Filter Salary > ₹80,000

Click Salary dropdown → Number Filters → Greater Than → 80000. Result: Amit (85K), Rahul (92K), Vikram (95K), Rohan (88K) — the 4 highest earners.

Example 10: Top 3 Salaries

Salary dropdown → Number Filters → Top 10 → Change to "Top 3 Items." Result: Vikram (95K), Rahul (92K), Rohan (88K).

Date Filters

Date columns offer powerful time-based filters:

• Today, Yesterday, Tomorrow
• This Week, Last Week, Next Week
• This Month, Last Month, This Quarter, This Year
• Before, After, Between specific dates
• Year/Month/Day hierarchical grouping

Example 11: Filter employees who joined after 01-Jan-2021

Join Date dropdown → Date Filters → After → 01/01/2021. Result: Rahul Verma (Jul 2021), Vikram Singh (Feb 2022), Divya Nair (Jun 2021).

Example 12: Filter employees who joined in 2020

Join Date dropdown → Uncheck all → Expand 2020 → Check all months of 2020. Result: Amit Sharma (Jan 2020), Anjali Desai (Aug 2020), Rohan Joshi (Sep 2020).

Filter by Color / Icon

If you've applied conditional formatting: Click dropdown → Filter by Color → Select the cell color, font color, or icon to show only matching rows.

Example 13: Real-Life — GST Return Filing

An accountant at a Reliance Retail store filters their sales register: Department = "Electronics" AND Invoice Date = "This Month" AND Amount > ₹10,000. This gives them high-value electronics invoices for the current month's GST-3B filing.

Advanced Filter

Theory: When AutoFilter Isn't Enough

AutoFilter handles simple criteria well, but Advanced Filter excels at complex, multi-condition filtering and can do things AutoFilter cannot: extract unique records, use OR conditions across different columns, and copy filtered results to a completely different location on the sheet.

Key Differences: AutoFilter vs Advanced Filter

Setting Up the Criteria Range

Advanced Filter requires a criteria range — a small area on your sheet with headers that exactly match your data headers, and criteria values below them.

Rule for criteria range:

• Same row = AND — all conditions must be true
• Different rows = OR — any one condition must be true

Example 14: AND Criteria — IT Department AND Salary > ₹90,000

Criteria range (e.g., cells H1:I2):

Steps: Data → Advanced → Filter the list in-place → List range: $A$1:$G$11 → Criteria range: $H$1:$I$2 → OK

Result: Rahul Verma (IT, ₹92,000) and Vikram Singh (IT, ₹95,000)

Example 15: OR Criteria — Department = IT OR Department = Finance

Criteria range:

Both values are in different rows under the same header → OR logic.

Example 16: Complex — (IT AND Salary > 90K) OR (HR AND any salary)

Criteria range:

Row 2: IT AND >90000. Row 3: HR AND (no condition = any salary). These rows are OR'd together.

Copy to Another Location

1. In the Advanced Filter dialog, select "Copy to another location"
2. In Copy to: field, select the destination cell (e.g., K1)
3. The filtered results appear at the new location without disturbing the original data

Example 17: Extract unique departments

List range: $C$1:$C$11 (Department column) → Criteria range: leave blank → Copy to: $K$1 → Check "Unique records only" → OK. Result at K1: Department, IT, HR, Finance, Marketing (unique values only).

SORT(), SORTBY(), and FILTER() — Dynamic Array Functions

These are modern Excel 365 / Excel 2021 functions that return arrays of results that automatically spill into neighboring cells. They're game-changers because they update dynamically — no need to re-sort or re-filter manually when data changes.

Example 18: Sort employees by Salary (descending)

Excel Formula=SORT(A2:G11, 5, -1)

This sorts the entire data range by column 5 (Salary), in descending order (-1). The result spills automatically starting from the cell where you enter this formula.

Example 19: Sort names by Rating (descending)

Excel Formula=SORTBY(B2:B11, G2:G11, -1)

Returns employee names sorted by their ratings, highest first: Rahul Verma (4.8), Vikram Singh (4.7), Rohan Joshi (4.6), ...

Multi-level SORTBY:

Excel Formula=SORTBY(A2:G11, C2:C11, 1, E2:E11, -1)

Sort by Department ascending, then by Salary descending within each department.

Example 20: Filter IT employees with Salary > ₹85,000

Excel Formula=FILTER(A2:G11, (C2:C11="IT") * (E2:E11>85000), "No records found")

The * between conditions acts as AND. Result:

FILTER with OR condition:

Excel Formula=FILTER(A2:G11, (C2:C11="IT") + (C2:C11="HR"), "No records")

The + between conditions acts as OR. Returns all IT and HR employees.

Combining SORT and FILTER:

Excel Formula=SORT(FILTER(A2:G11, C2:C11="IT"), 5, -1)

Filters IT employees first, then sorts the result by Salary (column 5) in descending order. This is like writing a SQL query: SELECT * FROM Employees WHERE Department='IT' ORDER BY Salary DESC

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1: Basic Sort

Using the employee dataset, sort all employees by City in alphabetical order (A–Z). Write down the first 3 employees that appear after sorting.

Answer: Bangalore: Rahul Verma, Meera Iyer; Chennai: Divya Nair (first 3 alphabetically by city).

Exercise 2: Multi-Level Sort

Sort the employee data by Rating (highest first). For employees with the same rating, sort by Name (A–Z). Who appears first? Who appears last?

Answer: First: Rahul Verma (4.8). Last: Anjali Desai (3.9).

Exercise 3: AutoFilter — Number Filter

Apply AutoFilter to show only employees whose salary is between ₹65,000 and ₹85,000 (inclusive). How many employees are visible?

Answer: 5 employees — Divya Nair (68K), Meera Iyer (71K), Karan Mehta (72K), Sneha Gupta (78K), Amit Sharma (85K).

Exercise 4: AutoFilter — Text Filter + Date Filter

Filter employees whose name contains "a" (case-insensitive) AND who joined after 01-Jan-2020. List the results.

Answer: Amit Sharma (Jan 2020 — equal to, not after, so depends on interpretation), Rahul Verma (Jul 2021), Anjali Desai (Aug 2020), Divya Nair (Jun 2021), Rohan Joshi (Sep 2020). Note: "a" is in Sharma, Rahul, Anjali, Nair, Rohan.

Exercise 5: Advanced Filter — OR Across Columns

Using Advanced Filter, extract employees who are in Mumbai OR have a Rating > 4.5. Set up the criteria range and write the results.

Answer: Criteria range: City = "Mumbai" (row 1), Rating > 4.5 (row 2, different row = OR). Result: Amit Sharma (Mumbai), Sneha Gupta (Mumbai), Rahul Verma (4.8), Vikram Singh (4.7), Rohan Joshi (4.6).

Exercise 6: FILTER Function

Write a FILTER formula to extract all employees in the Marketing department with a Rating ≥ 4.2.

Answer: =FILTER(A2:G11, (C2:C11="Marketing") * (G2:G11>=4.2), "No results"). Result: Meera Iyer (Marketing, 4.4).

Exercise 7: SORT + FILTER Combination

Write a single formula that filters employees earning above ₹70,000 and sorts the results by Join Date (oldest first).

Answer: =SORT(FILTER(A2:G11, E2:E11>70000), 6, 1) — sorts filtered results by column 6 (Join Date), ascending.

Exercise 8: SORTBY with Multiple Keys

Write a SORTBY formula to sort the full dataset by Department (A–Z) and then Rating (highest first).

Answer: =SORTBY(A2:G11, C2:C11, 1, G2:G11, -1)

Exercise 9: Unique Department List

Use Advanced Filter to extract a unique list of all departments, copied to cell K1.

Answer: List range: $C$1:$C$11, Criteria range: leave blank, Copy to: $K$1, Check "Unique records only." Result: IT, HR, Finance, Marketing.

Exercise 10: Real-World — GST Report

A shopkeeper has 200 sales entries with columns: Date, Item, Category, Amount, GST%. Filter all entries where Category = "Electronics" and Amount > ₹5,000, then sort by Date (newest first). Write the steps and formula approach.

Answer: Formula: =SORT(FILTER(data, (Category="Electronics")*(Amount>5000)), DateColumn, -1)

MCQ Quiz

Interview Questions

Excel=SORT(FILTER(A2:G11, (C2:C11="IT") * (E2:E11>80000), "No results"), 7, -1)

Mini Project: Employee Database with Dynamic Filtering

Data Validation

🎯 Learning Objectives

• Apply data validation rules: whole number, decimal, list, date, time, text length, and custom
• Configure input messages and error alerts (Stop, Warning, Information)
• Create dropdown lists from ranges and manual entries
• Build dependent (cascading) dropdown lists: State → City
• Write custom validation formulas: no duplicates, email format, phone number format
• Use the Circle Invalid Data feature to audit existing data

Data Validation Types

Accessing Data Validation

1. Select the cell(s) where you want to apply validation
2. Go to Data tab → Data Tools group → Data Validation
3. The Data Validation dialog has 3 tabs: Settings, Input Message, Error Alert

Validation Type 1: Whole Number

Restricts input to whole numbers (no decimals) within a specified range.

Example 1: Age must be between 18 and 65

Settings: Allow = Whole number, Data = between, Minimum = 18, Maximum = 65

If a user types 17, 66, or 25.5 — Excel rejects the entry with an error message.

Example 2: Quantity ordered (1 to 100)

Settings: Allow = Whole number, Data = between, Min = 1, Max = 100. This prevents negative quantities and unreasonable orders in a Flipkart purchase tracker.

Validation Type 2: Decimal

Allows decimal numbers within a range.

Example 3: GST Rate (0% to 28%)

Settings: Allow = Decimal, Data = between, Minimum = 0, Maximum = 28. GST slabs in India are 0%, 5%, 12%, 18%, 28% — this validation prevents entering invalid rates like 35% or -5%.

Validation Type 3: List

Creates a dropdown list — the most commonly used validation type. Users can only select from predefined options.

Example 4: Department dropdown from manual list

Settings: Allow = List, Source = IT, HR, Finance, Marketing, Operations (comma-separated, typed directly)

Example 5: City dropdown from a cell range

If cities are listed in cells K1:K10 on a "Lists" sheet: Settings: Allow = List, Source = =Lists!$K$1:$K$10

Validation Type 4: Date

Restricts input to valid dates within a range.

Example 6: Join Date must be between 01-Jan-2015 and today

Settings: Allow = Date, Data = between, Start date = 01/01/2015, End date = =TODAY()

Using =TODAY() as the end date makes the validation dynamic — it always allows dates up to the current day.

Validation Type 5: Time

Restricts to valid time entries.

Example 7: Shift start time must be between 6:00 AM and 10:00 PM

Settings: Allow = Time, Data = between, Start time = 6:00 AM, End time = 10:00 PM

Validation Type 6: Text Length

Limits the number of characters entered.

Example 8: PAN Card number must be exactly 10 characters

Settings: Allow = Text length, Data = equal to, Length = 10. Indian PAN numbers are always 10 characters (e.g., ABCDE1234F).

Example 9: Mobile number — exactly 10 digits

Settings: Allow = Text length, Data = equal to, Length = 10. This ensures no one enters "+91" prefix or 9-digit numbers.

Input Message & Error Alert

Input Message

A helpful tooltip that appears when the user clicks on the validated cell.

Input Message tab: Title = "Enter Age", Message = "Please enter your age as a whole number between 18 and 65."

Error Alert Types

Example 10: Custom error for Age field

Error Alert tab: Style = Stop, Title = "Invalid Age", Error message = "Age must be between 18 and 65. Please re-enter."

Dropdown Lists — From Ranges & Manual Lists

Method 1: Manual Comma-Separated List

Quick and simple for short, static lists.

Settings: Allow = List, Source = Male, Female, Other

Method 2: From a Cell Range

Better for longer lists and lists that might change. Store your list items in a dedicated column (often on a separate "Lists" or "Master" sheet).

Example 11: Indian States Dropdown

On a sheet called "Master":

Validation Source: =Master!$A$1:$A$8

Method 3: Using Named Ranges for Dropdown

Define a named range called StateList = Master!$A$1:$A$8. Then in validation: Source = =StateList. This is cleaner and easier to maintain — we'll cover Named Ranges in detail in Chapter 12.

Example 12: Payment Mode Dropdown

For a Reliance Retail billing system:

Source: Cash, UPI, Credit Card, Debit Card, Net Banking, Wallet

Example 13: GST Slab Dropdown

Source: 0%, 5%, 12%, 18%, 28%. This ensures only valid Indian GST rates are entered in the invoice system.

Dependent (Cascading) Dropdown Lists

Theory: What Are Cascading Dropdowns?

A cascading dropdown means the options in the second dropdown depend on what was selected in the first. For example: if you select Maharashtra as the state, the city dropdown should show Mumbai, Pune, Nagpur — not Delhi, Chennai, or Kolkata. This is extremely common in Indian data entry forms — from bank account opening forms to IRCTC ticket booking.

Step-by-Step: Building State → City Cascading Dropdown

Step 1: Create the master data on a separate sheet ("Master"):

Step 2: Create Named Ranges for each state's cities. Select Mumbai through Nashik → Name Box → Type Maharashtra → Enter. Repeat for each state column. The named range name must exactly match the state name in your dropdown.

Step 3: In your data entry sheet, cell A2 has the State dropdown (Source: Maharashtra, Karnataka, Tamil Nadu, Delhi).

Step 4: For the City dropdown in cell B2, use this validation formula:

Settings: Allow = List, Source = =INDIRECT(A2)

Example 14: Department → Designation Cascading

At TCS, different departments have different designations:

Named ranges: IT → {Developer, Tester, Architect, DevOps}, HR → {Recruiter, HR Manager, Trainer, HRBP}, Finance → {Analyst, Auditor, Controller, CFO}. City cell formula: =INDIRECT(DepartmentCell)

Custom Validation Formulas

Theory: The Power of Custom Validation

When built-in validation types aren't enough, you can write your own formula. The formula must return TRUE (valid) or FALSE (invalid). Settings: Allow = Custom, Formula = your formula.

Example 15: No Duplicate Entries Allowed

Prevent duplicate Emp IDs in column A. Apply to A2:A100:

Custom Formula=COUNTIF($A:$A, A2)=1

This counts how many times the value in A2 appears in column A. If it appears exactly once (the current entry), it's valid. If someone tries to enter a duplicate, COUNTIF returns 2, which ≠ 1, so the entry is rejected.

Example 16: Email Format Validation

Basic email validation — must contain "@" and ".":

Custom Formula=AND(ISNUMBER(FIND("@",A2)), ISNUMBER(FIND(".",A2)), FIND("@",A2)>1, FIND(".",A2)>FIND("@",A2)+1)

This checks: (1) "@" exists, (2) "." exists, (3) "@" is not the first character, (4) "." comes after "@" with at least one character between them.

Example 17: Indian Phone Number Validation

Must start with 6, 7, 8, or 9 and be exactly 10 digits:

Custom Formula=AND(LEN(A2)=10, ISNUMBER(A2+0), OR(LEFT(A2,1)="6", LEFT(A2,1)="7", LEFT(A2,1)="8", LEFT(A2,1)="9"))

This validates: (1) exactly 10 characters, (2) all digits (can be added to a number), (3) starts with 6/7/8/9 (valid Indian mobile prefixes).

Example 18: Date Not in Future

Join Date cannot be in the future:

Custom Formula=A2<=TODAY()

Example 19: PAN Card Format Validation

Indian PAN format: 5 letters, 4 digits, 1 letter (e.g., ABCDE1234F):

Custom Formula=AND(LEN(A2)=10, EXACT(UPPER(A2),A2), ISNUMBER(MID(A2,6,4)+0), NOT(ISNUMBER(LEFT(A2,5)+0)), NOT(ISNUMBER(RIGHT(A2,1)+0)))

This checks: exactly 10 characters, all uppercase, characters 6-9 are digits, first 5 are not numbers, last character is not a number.

Circle Invalid Data

If you apply validation to cells that already contain data, existing invalid entries won't be blocked (validation only checks new entries). To find them:

1. Go to Data tab → Data Validation dropdown arrow → Circle Invalid Data
2. Excel draws red circles around all cells that violate their validation rules
3. To remove circles: Data → Data Validation dropdown → Clear Validation Circles

Practice Exercises, MCQs & Interview Questions

Practice Exercises

Exercise 1: Whole Number Validation

Create a validation rule for a "Number of Items" column that only accepts whole numbers between 1 and 500. Add an input message: "Enter quantity (1-500)" and a Stop error alert: "Quantity must be between 1 and 500."

Answer: Allow = Whole number, Data = between, Min = 1, Max = 500. Input Message: Title = "Quantity", Message = "Enter quantity (1-500)". Error Alert: Style = Stop, Title = "Invalid Quantity", Message = "Quantity must be between 1 and 500."

Exercise 2: Date Validation

For an "Invoice Date" column, ensure dates are within the current financial year (01-Apr-2025 to 31-Mar-2026). Use a dynamic formula for the current financial year.

Answer: Allow = Date, Data = between, Start = 01/04/2025, End = 31/03/2026. For dynamic: Start = =DATE(IF(MONTH(TODAY())>=4,YEAR(TODAY()),YEAR(TODAY())-1),4,1)

Exercise 3: Dropdown List

Create a dropdown for "Blood Group" with options: A+, A-, B+, B-, AB+, AB-, O+, O-. Ensure the dropdown shows an in-cell dropdown button.

Answer: Allow = List, Source = A+,A-,B+,B-,AB+,AB-,O+,O-. Check "In-cell dropdown" checkbox.

Exercise 4: Cascading Dropdown — State → City

Create a cascading dropdown where: Gujarat cities = Ahmedabad, Surat, Vadodara, Rajkot; Rajasthan cities = Jaipur, Udaipur, Jodhpur, Ajmer. Show all steps including named ranges.

Answer: 1) Type city lists in separate columns. 2) Name range A1:A4 as "Gujarat", B1:B4 as "Rajasthan". 3) State dropdown: Source = Gujarat, Rajasthan. 4) City dropdown: Source = =INDIRECT(StateCell).

Exercise 5: No Duplicates Validation

Apply custom validation to column B (Roll Numbers) so that no student roll number is entered twice. Write the formula.

Answer: Custom formula: =COUNTIF($B:$B, B2)=1. Apply to B2:B1000.

Exercise 6: Phone Number Validation

Create a validation for Indian mobile numbers: must be exactly 10 digits, must start with 6, 7, 8, or 9, must not contain any letters.

Answer: =AND(LEN(B2)=10, ISNUMBER(B2+0), OR(LEFT(B2,1)="6", LEFT(B2,1)="7", LEFT(B2,1)="8", LEFT(B2,1)="9"))

Exercise 7: Aadhaar Number Validation

Aadhaar numbers are exactly 12 digits. Write a custom validation formula.

Answer: =AND(LEN(A2)=12, ISNUMBER(A2+0)). Note: Store Aadhaar as text (format cells as Text first) to preserve leading zeros.

Exercise 8: Circle Invalid Data

You have a column of ages with values: 25, 17, 42, -5, 70, 30, 150, 28. Apply validation (18-65) and use Circle Invalid Data. Which cells get circled?

Answer: 17 (below 18), -5 (below 18), 70 (above 65), 150 (above 65) — 4 cells circled.

MCQ Quiz

Interview Questions

Mini Project: Data Entry Form with Validated Fields

Excel Formula=UNIQUE(C2:C11)

Excel Formula=UNIQUE(C2:C11, FALSE, TRUE)

Excel Formula=UNIQUE(C2:D11)

Excel Formula=ROWS(UNIQUE(C2:C11))

Excel Formula=ROWS(UNIQUE(ProductColumn))

Excel Formula=TRIM(A2)      ' Removes leading, trailing, and extra internal spaces
=CLEAN(A2)     ' Removes non-printable characters (ASCII 0-31)
=TRIM(CLEAN(A2))  ' Both together — the gold standard

Excel Formula=UPPER(A2)     ' "amit sharma" → "AMIT SHARMA"
=LOWER(A2)     ' "AMIT SHARMA" → "amit sharma"
=PROPER(A2)    ' "amit sharma" → "Amit Sharma"

Excel Formula=[@Salary]*12

Excel Formula=SUM(tblEmployees[Salary])

Excel Formula=AVERAGEIF(tblEmployees[Department], "IT", tblEmployees[Salary])

Excel Formula=[@Amount]*[@[GST Rate]]

Excel Formula=COUNTIF(tblEmployees[City], "Mumbai")

Excel Formula=SUM(EmployeeSalaries)        ' Instead of =SUM(E2:E11)
=AVERAGE(EmployeeSalaries)    ' Instead of =AVERAGE(E2:E11)
=MAX(EmployeeSalaries)        ' Highest salary
=MIN(EmployeeSalaries)        ' Lowest salary

Excel Formula=VLOOKUP("E005", EmployeeData, 5, FALSE)

Excel Formula=SUMIF(Department, "IT", Salary)

Excel Formula=COUNTIF(City, "Mumbai")

Excel Formula=A2 * GST_Rate    ' Multiplies by 0.18 without referencing any cell

Excel Formula=B2 * USD_to_INR    ' Converts USD amount in B2 to INR