Day-4 Excel Functions & Data Validation.

Excel Functions & Data Validation.

1. IF, AND, and OR Functions.

✅ IF Function – Conditional Decision Making.

  • The IF function is used when you want Excel to make decisions based on logic.
  • It checks whether a condition is true or false, and then returns a value accordingly.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Detailed Example:

=IF(A2>=35, "Passed", "Failed")

If the marks in cell A2 are 35 or more, the result will be “Passed”, otherwise “Failed”.

Nested IF Example:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "Fail")))

Checks multiple conditions to assign grades.

✅ AND Function – Check All Conditions Must Be True

AND returns TRUE only if all conditions are true.

Syntax:

=AND(condition1, condition2, ...)

Use Case: Check if a student has passed in both subjects:

=IF(AND(B2>=35, C2>=35), "Pass", "Fail")

Real-Life Example: Employee gets bonus only if they meet sales target AND worked full days:

=IF(AND(Sales>=100000, Attendance="Full"), "Bonus", "No Bonus")

✅ OR Function – Check If At Least One Condition is True.

OR returns TRUE if any one condition is true.

Syntax:

=OR(condition1, condition2, ...)

Use Case: Allow re-exam if the student fails in any one subject:

=IF(OR(B2<35, C2<35), "Re-exam", "Cleared")

Practical Tip: You can combine IF, AND, and OR for complex logic:

=IF(AND(OR(A2="M", A2="F"), B2>=18), "Eligible", "Not Eligible")

🔹 2. COUNTIF, COUNTIFS, SUMIF, SUMIFS – Advanced Data Analysis.

🔷 What is COUNTIFS?

The COUNTIFS function counts the number of rows that meet multiple criteria across one or more ranges. It’s the enhanced version of COUNTIF, which only supports a single condition.

🔧 Syntax:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • criteria_range1: The first range of cells to evaluate.
  • criteria1: The condition for the first range.
  • criteria_range2: (Optional) The second range.
  • criteria2: (Optional) The second condition, and so on.
✅ All ranges must be of equal size (same number of rows/columns).

Simple Example:

A (Name) B (Department) C (Score)
John HR 85
Maya IT 92
Rohit HR 75
Asha IT 68
Neha HR 90

Goal: Count how many HR employees scored more than 80.

Formula:
=COUNTIFS(B2:B6, "HR", C2:C6, ">80")

💡 Multiple Conditions Across Same or Different Columns

Example 1: Count IT employees who scored less than 80

=COUNTIFS(B2:B6, "IT", C2:C6, "<80")
Result: 1 → (Asha)

Example 2: Count rows where:

  • Department is HR

  • Score >= 85

  • Name starts with “N”

=COUNTIFS(B2:B6, "HR", C2:C6, ">=85", A2:A6, "N*")

🧠 Wildcard Characters in COUNTIFS

  • * (asterisk): Any number of characters

  • ? (question mark): Single character

=COUNTIFS(A2:A6, "*a")

Counts names ending with ‘a’ → Asha, Maya, Neha

📅 Using COUNTIFS with Dates.

Example:

Count how many records have:
  • Join Date between Jan 1, 2023 and Dec 31, 2023
=COUNTIFS(D2:D100, ">=01/01/2023", D2:D100, "<=31/12/2023")

➕ SUMIF Function in Excel.

🔷 What is SUMIF?

SUMIF is used to sum the values in a range that meet a single criterion.
For example:
  • Sum all sales made in “North” region.
  • Total marks of students who scored above 80.
  • Sum salary of employees in “IT” department.

🔧 Syntax:

=SUMIF(range, criteria, [sum_range])
  • range: The range to evaluate for the condition.
  • criteria: The condition to apply.
  • sum_range (optional): The actual range to sum. If omitted, Excel sums the same range as the one evaluated.

Simple Example:

Sample Data:
A (Name) B (Department) C (Score)
John HR 85
Maya IT 92
Rohit HR 75
Asha IT 68
Neha HR 90

Goal: Sum salaries of HR department employees.

Formula:

=SUMIF(B2:B6, "HR", C2:C6)

💡 How It Works:

  1. Excel looks at B2:B6 to find cells that contain “HR”.
  2. For each match, it picks the corresponding value from C2:C6.
  3. Adds those matched values.

➕ SUMIFS – Add Values with Multiple Conditions

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Example: Add sales where region is “North” and sales > ₹10,000:
=SUMIFS(C2:C100, A2:A100, "North", B2:B100, ">10000")

✅ Data Validation in Excel

📌 What is Data Validation?

Data Validation in Excel allows you to control what kind of data can be entered into a cell or range of cells. It’s useful to prevent:

  • Typing mistakes
  • Invalid entries
  • Out-of-range numbers or dates
  • Incorrect formats
✅ It helps in maintaining data integrity in any Excel worksheet.

🔧 How to Apply Data Validation

  1. Select the cell(s) you want to apply validation to.
  2. Go to the Data tab on the Ribbon.
  3. Click Data Validation (under the Data Tools group).
  4. In the Data Validation dialog box, set:
    • Validation Criteria
    • Input Message (optional)
    • Error Alert (optional)

🎯 Types of Data Validation in Excel

Here are the types of validations you can apply, along with practical examples:

1. Whole Number

Allows only whole numbers within a defined range.
Use Case: Allow marks between 0 and 100.
Steps:
  • Allow: Whole Number
  • Data: between
  • Minimum: 0
  • Maximum: 100
🔒 Prevents entry of text or decimal numbers.

2. Decimal

Allows decimal values in a specific range.
Use Case: Enter salaries between ₹15,000.00 and ₹75,000.00.
Steps:
  • Allow: Decimal
  • Data: between
  • Min: 15000
  • Max: 75000

3. List (Drop-down Menu)

Creates a drop-down with predefined values.
Use Case: Select department from HR, IT, Sales, Finance.
Steps:
  • Allow: List
  • Source: HR,IT,Sales,Finance
✅ Users can only pick from the list (no typing mistakes).
💡 You can also use a cell range:
excel
=Sheet2!A1:A4

4. Date

Restrict input to a valid date or a date range.
Use Case: Allow only future dates or between two dates.
Steps:
  • Allow: Date
  • Data: greater than or equal to
  • Start date: =TODAY()
⏳ Useful for deadline entry, birthdates, appointments.

5. Time

Restrict input to a time or time range.
Use Case: Allow only office hours (9 AM to 5 PM)
Steps:
  • Allow: Time
  • Data: between
  • Start: 09:00 AM
  • End: 05:00 PM

6. Text Length

Restrict the number of characters allowed.
Use Case: Allow only 10-digit mobile numbers.
Steps:
  • Allow: Text length
  • Data: equal to
  • Length: 10
🛡️ Prevents too short or too long inputs in mobile numbers, roll numbers, etc.

7. Custom Formula

Use Excel formulas for advanced validation rules.
Example 1: Cell must start with “EMP”
=LEFT(A1,3)="EMP"

✨ Input Message & Error Alert

🗒️ Input Message

Gives instructions when the user selects a cell.
Example: “Enter score between 0 and 100 only.”
❌ Error Alert
Displays a pop-up message when incorrect data is entered.

Alert Types:

  • Stop (default) – Prevents invalid entry.
  • Warning – Warns, but allows override.
  • Information – Just notifies, doesn’t restrict.
🧠 Use friendly messages like:
“Oops! Enter only a number between 1 and 10.”

💡 Advanced Tricks

🔁 Create Dependent Drop-down Lists (Cascading)

Use Case: Select “Country” in one cell → See only “States” of that country in another.
  1. Create named ranges for each category (India, USA, etc.).
  2. Use INDIRECT formula:
=INDIRECT(A1)

🔁 Dynamic Drop-down with Excel Table

  • Create a dynamic list using Excel Table.
  • Name the range.
  • Use =OFFSET() or a dynamic named range formula.
  • Set as the source in Data Validation.

🧽 Clear Data Validation

To remove validation from a cell:
  1. Select the cell(s)
  2. Go to Data → Data Validation
  3. Click Clear All

🔍 Find All Cells with Validation

Use Go To Special:
  • Press Ctrl + G → Click Special → Select Data Validation.

✅ Summary Table

Type Use Example Formula/Source Example
COUNTIF Count cells based on one condition
COUNTIFS Count cells based on multiple conditions
Wildcards (* and ?)
Works With Dates (using cell references)
Dynamic Criteria
IF Make decisions based on logic
AND/OR Combine multiple conditions
SUMIF Add values with a single condition
SUMIFS Add values with multiple conditions
Data Validation Control what users can enter to ensure data quality

Leave a Reply

Your email address will not be published. Required fields are marked *