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

Copy code

=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

Copy code

=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.

Microsoft Excel - Protecting excel worksheets and workbooks

🔐 1️⃣ Protecting an Entire Worksheet

This prevents users from editing locked cells.

Steps:

Open your Excel sheet.

Go to Review tab.

Click Protect Sheet.

Set a password (optional but recommended).

Choose what users are allowed to do (select cells, format, etc.).

Click OK.

👉 By default, all cells are locked — protection works only after you enable “Protect Sheet”.

🔒 2️⃣ Protecting Specific Cells in a Worksheet

If you want users to edit only certain cells:

Step 1: Unlock editable cells

Select the cells you want users to edit.

Right-click → Format Cells.

Go to Protection tab.

Uncheck Locked → Click OK.

Step 2: Protect the sheet

Go to Review → Protect Sheet.

Set password → Click OK.

Now only unlocked cells can be edited ✅

🏗 3️⃣ Protecting the Structure of a Workbook

This prevents users from:

Adding new sheets

Deleting sheets

Renaming sheets

Moving sheets

Steps:

Go to Review tab.

Click Protect Workbook.

Check Structure.

Add password (optional).

Click OK.

🔑 4️⃣ Adding a Workbook Password (Open Password)

This prevents the file from opening without a password.

Steps:

Click File → Info.

Click Protect Workbook.

Select Encrypt with Password.

Enter password → Click OK.

Save the file.

⚠️ Important: If you forget this password, it cannot be recovered easily.

Common Datadog Mapping Mistakes in OIC Integrations

During recent reviews of Datadog error logging for OIC integrations, the following common mapping issues were identified. These need to be addressed to ensure accurate monitoring, correct ticket routing, and proper error categorization.

🔎 Observed Common Mistakes

1. Environment is populated as DEV/SIT/NA in OIC SIT test. It should be strictly set to SIT when testing in SIT.
2. Either Source or Target marked as NA.
3. RICEF ID is missing.
4. Different application names used in Source, such as: CCS vs Oracle CCS ,PPM vs Oracle PPM
5. Instance ID logged is not the actual service where the error happened, but rather the Datadog logging service ID.
6. Error Severity is missing. (In past days, all entries showed severity as "Error". The team should clearly distinguish between Info vs Error.)
7. Error Type is missing or incorrectly populated.
8. Values other than Technical or Business are used. Currently, all executions are logged as Technical error. Data validation errors should be categorized under Business Error, otherwise all tickets will be routed to the OIC team.
9. Short Description or Error Description is missing. ErrorDescription should contain a detailed message, but some entries contain only a custom summary.
10. Created By user field is not properly populated. It should indicate: OIC user (if triggered internally), or API key user (if invoked from external API).
11. Error Categorization not clearly defined: TECHNICAL ERROR vs BUSINESS ERROR.

OIC - Smart Error Categorization in Oracle Integration Cloud (OIC): Business vs Technical Errors

In enterprise integrations, not all errors are the same.

Some errors are due to business rules, while others are technical/system failures.

If we don’t categorize them properly, monitoring tools like Datadog or ServiceNow will show noisy, unclear alerts.

This blog explains how to design a clean and scalable error classification framework in OIC.

🎯 Why Error Categorization Matters

When invoking any backend service (REST/SOAP API, ERP, DB, File, etc.), failures can occur due to:

❌ Missing mandatory fields

❌ Validation failure

❌ File not found

❌ Endpoint down

❌ Timeout / network issue

❌ Authentication failure

If we treat everything as “technical error”, business users get confused.

If we treat everything as “business error”, support teams struggle.

So we need a structured approach.

🟢 Step 1: Define Error Types

1️⃣ Business Errors

These occur due to functional validation or business rule violations.

Examples:

Mandatory field missing

Invalid data format

Duplicate record

File not found (expected business file missing)

Validation failure from downstream API

👉 These should be thrown as custom business faults using Throw New Fault in OIC.

Example:

Error Code: MANDATORY_FIELD_ERROR

Error Type: BUSINESS

Severity: MEDIUM

2️⃣ Technical Errors

These occur due to system/infrastructure failures.

Examples:

500 Internal Server Error

Connection timeout

Authentication failure

Network issue

Service unavailable

👉 These are generally caught in Global Fault Handler and categorized as technical errors.

Example:

Error Code: ENDPOINT_TIMEOUT

Error Type: TECHNICAL

Severity: HIGH

🛠 Step 2: Throw Business Errors in OIC

When invoking a business API:

Add a Scope

Inside the scope → Add service invocation

In the fault handler:

Check response error

Use Throw New Fault

Set custom error key (like FILE_NOT_FOUND_ERROR)

This clearly separates business failure from system failure.

🗂 Step 3: Use Lookup for Centralized Error Mapping

Create a Lookup Table in OIC:

Error Key Error Type Reason Severity errorcode if needed

MANDATORY_FIELD_ERROR BUSINESS Mandatory field missing MEDIUM

FILE_NOT_FOUND_ERROR BUSINESS Expected file not available HIGH

ENDPOINT_TIMEOUT TECHNICAL Service timeout HIGH

DEFAULT_TECH_ERROR TECHNICAL Unknown system failure CRITICAL

🔎 Step 4: Match Thrown Error with Lookup

When sending error details to monitoring tools:

Capture thrown error key

Call Lookup

If match found:

Pull error type

Pull severity

Pull reason

If no match found:

Default to DEFAULT_TECH_ERROR

This ensures:

Standardized error reporting

Controlled severity levels

Clean dashboards in Datadog / ServiceNow

📡 Step 5: Send Structured Error to Monitoring Tools

Payload Example:

{

  "integrationName": "CustomerSync",

  "errorCode": "MANDATORY_FIELD_ERROR",

  "errorType": "BUSINESS",

  "severity": "MEDIUM",

  "reason": "Customer email is missing",

  "timestamp": "2026-02-17T10:30:00"

}

This helps:

Business team → Understand validation issues

Support team → Identify system failures quickly

Monitoring tools → Trigger correct alerts

🏗 Recommended Architecture Pattern

✔ Use Throw New Fault for business errors

✔ Use Global Fault Handler for technical errors

✔ Maintain centralized Error Lookup Table

✔ Always send structured payload to monitoring tools

✔ Keep default fallback for unknown errors

🚀 Benefits of This Approach

• Clear separation of Business vs Technical issues
• Cleaner observability in Datadog / ServiceNow
• Standardized error governance
• Reusable across all integrations
• Easy to maintain and scale

✅ Final Thought

In OIC, error handling should not be reactive — it should be designed intentionally.

By combining:

Scoped fault handling

Custom business faults

Centralized lookup mapping

Structured monitoring payload

You build a robust enterprise-grade integration framework.

Microsoft Excel -Auditing an Excel Worksheet

Excel provides powerful Formula Auditing tools to help you understand, debug, and validate formulas.

You’ll find these under:

Formulas Tab → Formula Auditing Group

1️⃣ Tracing Precedents in Excel Formulas

🔎 What Are Precedents?

Precedents are the cells that provide data to a formula.

Example:

If cell C1 = A1 + B1,

Then A1 and B1 are precedents of C1.

✅ How to Trace Precedents:

Select the cell containing the formula.

Go to Formulas tab.

Click Trace Precedents.

🔹 Blue arrows will appear pointing from input cells to the formula cell.

➕ Extra Tips:

Click again to trace multiple levels.

Use Remove Arrows to clear them.

If the arrow is dotted, it may refer to another worksheet.

2️⃣ Tracing Dependents in Excel Formulas

🔎 What Are Dependents?

Dependents are cells that use the selected cell in their formulas.

Example:

If C1 = A1 + B1,

Then C1 is dependent on A1.

✅ How to Trace Dependents:

Select the input cell.

Go to Formulas tab.

Click Trace Dependents.

🔹 Arrows will point from the selected cell to formulas that depend on it.

This is very useful when:

Updating key values

Checking impact before deleting data

Understanding calculation flow

3️⃣ Working with the Watch Window

👀 What is Watch Window?

The Watch Window allows you to monitor important cells without scrolling across sheets.

✅ How to Use:

Go to Formulas tab.

Click Watch Window.

Click Add Watch.

Select the cell(s) to monitor.

Click Add.

You’ll see:

Workbook

Sheet

Cell

Current Value

Formula

💡 Best Use Cases:

• Monitoring totals
• Watching KPIs
• Debugging large financial models
• Tracking values across multiple sheets

4️⃣ Showing Formulas Instead of Results

Sometimes you need to view formulas instead of calculated values.

✅ Method 1:

Press Ctrl + ` (grave accent key)

✅ Method 2:

Go to Formulas tab

Click Show Formulas

This will:

Display formulas in all cells

Expand column widths automatically

Press again to return to normal view.

🎯 Why Formula Auditing Is Important

• ✔ Helps detect errors
• ✔ Improves spreadsheet transparency
• ✔ Makes financial models easier to validate
• ✔ Prevents accidental data corruption
• ✔ Supports audit and compliance checks

OIC - OIC Scheduled Integrations: Does Ad-Hoc Run Impact Scheduled Run Parameters?

When working with Scheduled Integrations in Oracle Integration Cloud (OIC), a common doubt is:

If I run the integration manually (Ad-Hoc / Run Now), will it impact the scheduled StartTime / EndTime values?

✅ Correct Answer (Based on Testing)

No — Ad-Hoc run does NOT impact scheduled parameter values.

There is no direct relationship between Ad-Hoc execution and Scheduled execution parameter persistence.

📌 How It Actually Works

1️⃣ Scheduled Run

Triggered automatically by OIC Scheduler

Uses stored schedule parameter values

Follows recurrence (15 min / hourly / daily etc.)

Parameter continuity works only within scheduled executions

2️⃣ Ad-Hoc Run (Run Now)

Triggered manually from OIC Console

You may override parameters at runtime

Executes independently of scheduler

Does NOT modify schedule configuration

Any values entered during Ad-Hoc run are used only for that execution.

🔍 Important Clarification

Earlier confusion usually comes from watermark/rotation logic like:

StartTime = EndTime

EndTime = sysdate

If your integration updates values internally, that affects the current run execution only.

It does NOT update the scheduler’s stored parameters.

The next scheduled run will still use the originally configured schedule parameters.

📊 Final Behavior Summary

Scenario | Impacts Next Scheduled Run?

Ad-Hoc run (no changes) | ❌ No

Ad-Hoc run (override values) | ❌ No

Scheduled run | ✅ Uses its own stored schedule values

Internal variable assignment for adhoc run ,| ❌ Does not change scheduler config

🎯 Final Conclusion

✔ Ad-Hoc run is completely independent

✔ It does NOT update scheduler stored parameters

✔ Scheduled runs continue with configured values

✔ No cross-impact between Ad-Hoc and Scheduled runs

OIC - Rolling Date Window Using Schedule Parameters in Oracle OIC

📌 Use Case

In many OIC scheduled integrations, we need to: 

Process data between StartTime and EndTime

After successful run:

Move EndTime to StartTime

Reset EndTime

Use current timestamp for next execution

This ensures:

No duplicate processing

No missing data

Smooth incremental extraction

Example:

Run 1 → Process: Jan 01 – Jan 02

Run 2 → Process: Jan 02 – Jan 03

Run 3 → Process: Jan 03 – Jan 04

This is called a Rolling Date Window.

🛠 Solution Steps in OIC

Step 1️⃣ – Create Schedule Parameters

1. Go to Schedule → Parameters
2. Create: StartTime , EndTime
3. Set initial default values.

Step 2️⃣ – Capture Current Time (Timezone Adjusted)

Use Assign action:

format-dateTime(string(adjust-dateTime-to-timezone(current-dateTime(), dayTimeDuration('PT8H'))),'[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01].[H001]Z')

This: Gets current time Adjusts to required timezone (HK example PT8H)

Formats to ISO timestamp

Store it in: Var_CurrentHKDateTime

Step 3️⃣ – Use Stitch (Conditional Logic)

Condition:

EndTime != ""

When EndTime exists:

Use existing StartTime & EndTime

Otherwise:

Set EndTime = Current Timestamp

Step 4️⃣ – Rotate the Values (Important Step)

After successful processing:

Use Assign:

StartTime = EndDate

EndTime = ""

This is called:

🔄 Parameter Rotation

🔁 Rolling Window Shift

📦 Incremental Date Rotation Logic

🎯 Benefits

✅ Prevents duplicate data

✅ Enables incremental extraction

✅ Fully dynamic window

✅ No manual parameter update

✅ Works for ERP, HCM, REST, DB polling