Class 4: Essential Excel Formulas

This guide expands on key Excel formulas by offering detailed explanations, real-world examples from
the workbook (excel [Link]), and practical tips for data analysis. Whether you're cleaning up messy
data, performing calculations, or analyzing trends, these formulas will help you work smarter in Excel.

1. CONCATENATE (or CONCAT / TEXTJOIN)

Purpose: Combines text from multiple cells into one cohesive string.

Functions:

• =CONCATENATE(A1, B1) (older versions)

• =CONCAT(A1, B1) (modern version)

• =TEXTJOIN(" ", TRUE, A1, B1) (for joining with a delimiter)

Example from Concatenate Sheet:

• Columns: FirstName (B), LastName (C)

• Formula: =CONCATENATE(B2, " ", C2)

• Result: "Jim Halpert"

Use Case: Creating full names, addresses, or email tags.

2. MAX and MIN

Purpose: Identifies the highest and lowest values in a range.

Formulas:

• =MAX(range) returns the largest number.

• =MIN(range) returns the smallest number.

Example from Max-Min Sheet:

• Column G contains Salary.

• =MAX(G2:G6) returns the highest salary.

• =MIN(G2:G6) returns the lowest salary.

Use Case: Salary comparison, sales leaderboards, peak measurements.

3. TRIM
Purpose: Cleans up extra spaces from text (except single spaces between words).

Formula:

• =TRIM(A1)

Example from TRIM Sheet:

• Dirty data: " Flenderson "

• Formula: =TRIM(C2) → Result: "Flenderson"

Use Case: Data cleanup before importing/exporting or analysis.

4. SUBSTITUTE

Purpose: Replaces part of a text string with another.

Formula:

• =SUBSTITUTE(text, old_text, new_text)

Example from Substitute Sheet:

• JobTitle: "Salesman"

• Formula: =SUBSTITUTE(F2, "Salesman", "Sales Executive")

• Result: "Sales Executive"

Use Case: Updating job titles, categories, or inconsistent naming conventions.

5. LEN

Purpose: Returns the number of characters in a string (including spaces).

Formula:

• =LEN(A1)

Example from Len Sheet:

• Text: "Jim"

• Formula: =LEN(B2) → Result: 3

Use Case: Validating text length (e.g., IDs, passwords), checking for empty spaces.

6. LEFT and RIGHT

Purpose: Extracts characters from the beginning (LEFT) or end (RIGHT) of text.
Formulas:

• =LEFT(A1, n)

• =RIGHT(A1, n)

Example from LeftRight Sheet:

• Full Email: "[Link]@[Link]"

• =LEFT(I2, 4) → "Jim."

• =RIGHT(I2, 8) → "@[Link]"

Use Case: Pulling name prefixes, email domains, or codes.

7. DAYS and NETWORKDAYS

Purpose:

• DAYS: Returns total number of days between two dates.

• NETWORKDAYS: Returns working days (excludes weekends).

Formulas:

• =DAYS(end_date, start_date)

• =NETWORKDAYS(start_date, end_date)

Example from Days-NetworkDays Sheet:

• Start: I2, End: J2

• =DAYS(J2, I2) → 9

• =NETWORKDAYS(I2, J2) → 7 (excludes weekend)

Use Case: Project timelines, leave tracking, payroll calculations.

8. IF

Purpose: Returns one value if a condition is true and another if false.

Formula:

• =IF(condition, value_if_true, value_if_false)

Example from IF-IFS Sheet:

• =IF(G2>50000, "High", "Low") → Checks salary status

Use Case: Pass/Fail results, flagging errors, decision logic.

Purpose: Evaluates multiple conditions in order.

Formula:

• =IFS(condition1, result1, condition2, result2, ..., TRUE, default_result)

Example:

• =IFS(G2>60000, "Very High", G2>50000, "High", TRUE, "Low")

Use Case: Grading systems, salary bands, category grouping.

10. SUM, SUMIF, SUMIFS

Purpose: Adds numbers with or without conditions.

Formulas:

• =SUM(range)

• =SUMIF(criteria_range, criteria, sum_range)

• =SUMIFS(sum_range, criteria_range1, criteria1, ...)

Examples from SUM-SumIF Sheet:

• Total Salary: =SUM(G2:G6)

• Sum of salaries for employees >30 years: =SUMIF(D2:D6, ">30", G2:G6)

• Sum of salaries for Males >30 years: =SUMIFS(G2:G6, D2:D6, ">30", E2:E6, "Male")

Use Case: Budgeting, performance tracking, segmented analysis.

11. COUNT, COUNTIF, COUNTIFS

Purpose: Counts cells (with or without conditions).

Formulas:

• =COUNT(range)

• =COUNTIF(range, criteria)

• =COUNTIFS(range1, criteria1, range2, criteria2, ...)

Examples from Count-CountIF Sheet:

• Total entries: =COUNT(A2:A6)

• Count Males >30 years: =COUNTIFS(E2:E6, "Male", D2:D6, ">30")

Use Case: Demographic reports, survey data, frequency tracking.

Practical Applications of Formulas

1. HR Analysis

• Use COUNTIF and SUMIF to segment employee data.

• Clean names with TRIM and join full names using CONCATENATE.

2. Financial Summaries

• Calculate bonuses using IF and IFS based on salary bands.

• Aggregate team totals using SUMIFS.

3. Project Management

• Use DAYS and NETWORKDAYS to manage timelines.

• Conditional logic with IF for overdue tasks.

4. Marketing & CRM

• Use RIGHT to extract domains and group customers.

• Apply SUBSTITUTE to fix or update outdated product names.

5. Data Validation & Cleanup

• Use LEN to find unexpected extra spaces or missing characters.

• TRIM and SUBSTITUTE help remove unwanted formatting.

These formulas form the backbone of any meaningful Excel analysis. Understanding them deeply will not
only speed up your workflow but also improve accuracy and presentation. Continue to practice with
real-world spreadsheets and you’ll master Excel in no time!