Microsoft Excel - New Excel functions for 2019 and microsoft excel 365 editions

Working...

📘 Introduction to New Excel Functions

Microsoft introduced powerful dynamic array functions in Microsoft Excel 365 that are not available in Excel 2019 (except a few like TEXTJOIN).

✅ Excel 365 (Dynamic Array Enabled)

FILTER()

SORT()

UNIQUE()

XLOOKUP()

SWITCH()

TEXTSPLIT()

Dynamic array behavior (spill feature)

⚠ Excel 2019

No dynamic array functions

XLOOKUP not available

FILTER, SORT, UNIQUE not available

TEXTJOIN and SWITCH are available

No spill functionality

🔹 Microsoft Excel 365 FILTER() Function

📌 Purpose:

Filters data based on criteria.

✅ Syntax:

Excel

Copy code

=FILTER(array, include, [if_empty])

✅ Example:

Excel

Copy code

=FILTER(A2:C10, B2:B10="Sales")

Returns only rows where department is Sales.

🔹 Microsoft Excel 365 SORT() Function

📌 Purpose:

Sorts data dynamically.

✅ Syntax:

Excel

Copy code

=SORT(array, [sort_index], [sort_order])

✅ Example:

Excel

Copy code

=SORT(A2:C10, 2, 1)

Sorts by 2nd column in ascending order.

🔹 Microsoft Excel 365 UNIQUE() Function

📌 Purpose:

Returns distinct values.

✅ Syntax:

Excel

Copy code

=UNIQUE(array)

✅ Example:

Excel

Copy code

=UNIQUE(A2:A20)

Removes duplicates dynamically.

🔹 Dynamic FILTER() with Data Validation

You can combine FILTER + Data Validation (Dropdown).

Example:

Create dropdown in E1 (Departments)

Use formula:

Excel

=FILTER(A2:C20, B2:B20=E1)

Now results change automatically based on dropdown selection.

🔹 Microsoft Excel 365 XLOOKUP() Function

📌 Purpose:

Modern replacement for VLOOKUP & HLOOKUP.

✅ Syntax:

Excel

Copy code

=XLOOKUP(lookup_value, lookup_array, return_array)

✅ Example:

Excel

=XLOOKUP(E2, A2:A10, C2:C10)

✅ Advantages over VLOOKUP:

No column index number needed

Works left to right & right to left

Exact match by default

Error handling built-in

🔹 EXERCISE: Dynamic Employee Order List

🎯 Scenario:

Create a dynamic list of employees sorted by order amount.

Solution:

Excel

Copy code

=SORT(FILTER(A2:D20, C2:C20>50000), 3, -1)

✔ Filters orders above 50,000

✔ Sorts by amount descending

✔ Updates automatically

🔹 Microsoft Excel 365 SWITCH() Function

📌 Purpose:

Multiple condition replacement for nested IF.

✅ Syntax:

Excel

Copy code

=SWITCH(expression, value1, result1, value2, result2, default)

✅ Example:

Excel

Copy code

=SWITCH(A2,

"HR","Human Resource",

"IT","Information Tech",

"Unknown")

💡 TIP: TRUE Expression with SWITCH()

You can use TRUE for logical comparisons:

Excel

Copy code

=SWITCH(TRUE(),

A2>90,"A Grade",

A2>75,"B Grade",

A2>50,"C Grade",

"Fail")

This replaces multiple IF conditions.

🔹 Microsoft Excel 365 TEXTJOIN() Function

📌 Purpose:

Combines text with delimiter.

✅ Syntax:

Excel

Copy code

=TEXTJOIN(delimiter, ignore_empty, text1, text2…)

✅ Example:

Excel

Copy code

=TEXTJOIN(", ", TRUE, A2:A5)

💡 TIP: TEXTJOIN() with Criteria

Combine with FILTER:

Excel

Copy code

=TEXTJOIN(", ", TRUE, FILTER(A2:A20, B2:B20="Sales"))

Joins only Sales employees.

🔹 Microsoft Excel 365 TEXTSPLIT() Function

📌 Purpose:

Splits text into multiple columns/rows.

✅ Syntax:

Excel

Copy code

=TEXTSPLIT(text, col_delimiter)

✅ Example:

Excel

Copy code

=TEXTSPLIT(A2, ",")

Splits comma-separated values.