Training By Himanshu

Blog

  • How to Use BYROW and BYCOL Functions in Excel 365 with Practical Examples

    🧠 What Are BYCOL and BYROW Functions in Excel 365?

    BYCOL and BYROW are part of the Lambda helper functions in Excel 365. These functions allow you to apply custom logic across columns or rows of a range or array, making them incredibly useful for dynamic and reusable calculations.

    🔹 1. BYROW Function

    ✅ Purpose:

    Processes data row by row, applying a specified Lambda function to each row.

    📘 Syntax:

    excelCopyEdit=BYROW(array, lambda(row))
    • array: The data range you want to process.
    • lambda(row): A custom calculation to perform on each row.

    🧪 Example: Sum each row in a range

    You have this data in cells A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, SUM(r)))

    ✅ Output:

    Sum
    10
    7
    15

    Each row is summed individually and spilled vertically.

    🔹 2. BYCOL Function

    ✅ Purpose:

    Processes data column by column, applying a specified Lambda function to each column.

    📘 Syntax:

    excelCopyEdit=BYCOL(array, lambda(column))
    • array: The data range you want to process.
    • lambda(column): A custom calculation to perform on each column.

    🧪 Example: Find the average of each column

    Same data in A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, AVERAGE(c)))

    ✅ Output:

    Average
    3.0
    3.0
    4.67

    Each column’s average is calculated and spilled horizontally.

    🔁 When to Use BYROW and BYCOL?

    Use CaseUse Function
    Sum or average of each rowBYROW
    Custom logic applied to each columnBYCOL
    Conditional check row-wiseBYROW + IF
    Min/max/median by columnBYCOL

    💡 More Practical Examples

    🎯 Count how many values > 3 in each row:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, COUNTIF(r, ">3")))

    🎯 Find max value in each column:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, MAX(c)))

    ⚠️ Requirements

    • Available in Excel 365 and Excel 2021 only
    • Must use LAMBDA function inside

    🚀 Want to Automate This Logic?

    If you’re excited by what BYCOL and BYROW can do with formulas, imagine how much more powerful Excel becomes when you can automate this logic using VBA macros.

    Instead of manually applying formulas, you could:

    • Automatically summarize each row/column with a button click
    • Dynamically format top values
    • Export row/column summaries to reports

    🎓 Master Excel Automation with VBA (Beginner-Friendly)

    📘 Mastering Excel Automation – Excel VBA Training Course

    🔑 Why Learn VBA?

    • Eliminate repetitive tasks
    • Build powerful Excel tools
    • Automate complex logic (like BYROW/BYCOL) programmatically

    🎬 Course Highlights:

    • 42 easy-to-follow videos
    • 4 hours 8 minutes total
    • ₹441 only (Limited-time offer, originally ₹1,299)
    • Lifetime access

    🎯 Designed for non-programmers and Excel enthusiasts alike!

    🔗 👉 Enroll today and start automating Excel your way

    On sale products

  • How to Use the FILTER Function for Complete and Partial Text Match in Excel

    The FILTER function in Excel 365 is a powerful tool that allows you to extract rows from a dataset that meet specific criteria. It dynamically spills the matching results into adjacent cells — and is ideal for creating reports, dashboards, and searchable tables.

    🧠 FILTER Function Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])
    • array: The range you want to filter.
    • include: A logical expression that determines which rows to return.
    • if_empty: (Optional) Value to return if no match is found.

    ✅ 1. Complete Match of a Text String

    📘 Scenario:

    You have a list of employees and want to extract all rows where the department is exactly “HR”.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales
    AartiHR
    NikhilIT

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, B2:B5 = "HR", "No match found")

    🟢 Output:

    NameDepartment
    RohanHR
    AartiHR

    ✅ Exact/complete matches only — cells must exactly equal "HR".

    🔍 2. Partial Match of a Text String

    📘 Scenario:

    You want to filter all rows where the department contains the word “Sales”, including partial terms like “Sales – North”, “Sales Team”, etc.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales – East
    AartiHR
    NikhilSales Team

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, ISNUMBER(SEARCH("Sales", B2:B5)), "No match")

    🟢 Output:

    NameDepartment
    MeenaSales – East
    NikhilSales Team

    ✅ SEARCH enables case-insensitive partial matching, and ISNUMBER checks whether the text was found.

    💡 Tip:

    • Use SEARCH("text", cell) for partial, case-insensitive match.
    • Use FIND("text", cell) for partial, case-sensitive match.

    🎯 When to Use Complete vs Partial Match?

    Use CaseMatch TypeFormula Logic
    Filter all “IT” records onlyCompleteB2:B10 = "IT"
    Filter names that contain “Raj”PartialISNUMBER(SEARCH("Raj", A2:A10))

    🚀 Take Filtering Further with Excel VBA

    If you’re impressed by what Excel formulas can do, imagine being able to automate these filters, build custom forms, and generate filtered reports with one click.

    With Excel VBA, you can do all this and more — turning hours of manual work into minutes.

    🎓 Learn to Automate Excel with VBA – Without Prior Coding

    📘 Mastering Excel Automation – Excel VBA Training Course

    ✅ What You’ll Learn:

    • Automate repetitive tasks
    • Build dynamic reports
    • Create custom filters, buttons, and user forms
    • Use loops, conditions, and functions to control your spreadsheets

    🎥 42 structured videos
    🕒 4 hours 8 minutes of hands-on content
    💰 Limited-time price: ₹441 (was ₹1,299)
    📚 Lifetime access – Learn at your own pace

    🌟 Who Should Join?

    • Excel users ready to upgrade their skills
    • Professionals needing smart reporting
    • Beginners looking to enter automation

    🔗 👉 Enroll today and transform the way you use Excel

    Best selling products

  • Understanding Simpson’s Rule in Excel – With Practical Example

    In the world of data analysis, engineering, and applied mathematics, integration is often required to calculate areas under curves. When dealing with complex functions or raw tabulated data, traditional calculus may not be feasible — and that’s where Simpson’s Rule comes in.

    Excel provides a great platform to apply this technique using formulas, even without using advanced programming.

    🔍 What is Simpson’s Rule?

    Simpson’s Rule is a numerical method that approximates the definite integral of a function by estimating the area under the curve using parabolic arcs rather than straight lines (as in the trapezoidal rule). It provides higher accuracy, especially when the data or function changes curvature.

    ✅ Simpson’s Rule Formula

    For a function f(x)f(x) defined on interval [a,b][a, b], divided into n even sub-intervals, Simpson’s Rule is: ∫abf(x)dx≈h3[f(x0)+4f(x1)+2f(x2)+4f(x3)+⋯+4f(xn−1)+f(xn)]\int_a^b f(x)dx \approx \frac{h}{3} \left[ f(x_0) + 4f(x_1) + 2f(x_2) + 4f(x_3) + \dots + 4f(x_{n-1}) + f(x_n) \right]

    Where:

    • h=b−anh = \frac{b – a}{n}
    • nn is even
    • x0,x1,…,xnx_0, x_1, …, x_n are equally spaced data points

    💼 Real-World Example in Excel

    Let’s apply Simpson’s Rule to estimate the following integral: ∫0411+x2dx\int_0^4 \frac{1}{1 + x^2} dx

    This is the integral of the arctangent function, which cannot be easily integrated manually.

    🧮 Step-by-Step in Excel

    1. Create the x values (A2:A6)
      You divide the interval [0, 4] into 4 equal parts (n = 4):
      0, 1, 2, 3, 4
    2. Create the corresponding f(x) values in column B
      Formula: =1 / (1 + A2^2), then fill down.
    A (x)B = f(x) = 1/(1+x²)
    01.0000
    10.5000
    20.2000
    30.1000
    40.0588
    1. Calculate h
      Formula: = (A6 - A2) / 4 Result: 1
    2. Apply Simpson’s Rule in Excel = (1/3) * (B2 + 4*B3 + 2*B4 + 4*B5 + B6) Result: Approx. 1.3255

    This value is very close to the actual integral of arctangent(4) ≈ 1.3258, showcasing Simpson’s accuracy.

    ✨ Where Can You Use Simpson’s Rule in Excel?

    • When working with experimental data from labs or sensors
    • To approximate areas under curves in physics, finance, biology, and statistics
    • When analytical integration is too complex or not possible
    • For students and professionals who need quick, reliable estimations

    💡 Going Further with Excel Automation

    If you’re finding it powerful to use Excel for such mathematical tasks, imagine how much more you can achieve by automating calculations, creating custom functions, and building interactive tools within Excel itself.

    This is where learning Excel VBA (Visual Basic for Applications) makes a real difference.

    🎓 Learn to Automate Excel with Ease

    Take the next step in your Excel journey with
    Mastering Excel Automation – Excel VBA Training Course

    This online course is a practical guide to Excel automation, helping you eliminate repetitive tasks and build smart Excel solutions.

    🎯 Course Overview:

    • 💻 42 structured video lessons
    • 🕒 4 hours 8 minutes of focused training
    • 🔰 No prior programming needed
    • 📈 Learn variables, loops, conditions, user forms, and more

    🌟 Ideal for:

    • Data professionals
    • Business analysts
    • Students
    • Anyone who wants to enhance productivity in Excel

    💸 Special Price: ₹441 (Originally ₹1,299)
    🎓 Learn at your own pace, with lifetime access

    🔗 Explore the course and unlock your automation potential

    Top rated products

  • How to Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel 365 with Real-World Examples

    📘 Overview of the Functions

    These text functions are new in Excel 365 and Excel 2021, part of the dynamic array functions family. They are useful for splitting or extracting parts of text based on delimiters (like commas, spaces, hyphens, etc.).

    🔹 1. TEXTBEFORE

    ✅ Purpose:

    Extracts the text before a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    You have email addresses in a list and want to extract usernames (text before @).

    🧪 Example:

    excelCopyEdit=TEXTBEFORE("john.doe@gmail.com", "@")

    ➡️ Result: john.doe

    🔹 2. TEXTAFTER

    ✅ Purpose:

    Extracts the text after a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    From an email, you want to extract just the domain name.

    🧪 Example:

    excelCopyEdit=TEXTAFTER("john.doe@gmail.com", "@")

    ➡️ Result: gmail.com

    🔹 3. TEXTSPLIT

    ✅ Purpose:

    Splits a text string into rows or columns using one or more delimiters.

    📘 Syntax:

    excelCopyEdit=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

    🔧 Scenario:

    You have full names like "John,Doe" and want to split them into first name and last name in two columns.

    🧪 Example:

    excelCopyEdit=TEXTSPLIT("John,Doe", ",")

    ➡️ Result:

    AB
    JohnDoe

    🔄 Combined Real-World Example

    🎯 Scenario:

    You have a product list like this:

    arduinoCopyEdit"SKU123-Apple-Fruit"
"SKU456-Banana-Fruit"

    And you want to extract:

    ABCD
    SKU456-Banana-FruitSKU456BananaFruit

    🧪 Formulas:

    To get the SKU:

    excelCopyEdit=TEXTBEFORE(A1, "-")

    To get the Fruit Name:

    excelCopyEdit=TEXTSPLIT(TEXTAFTER(TEXTBEFORE(A1,"-Fruit"), "-"), "-")

    To get the Category:

    excelCopyEdit=TEXTAFTER(A1, "-", 2)

    📝 Summary Table

    FunctionUse Case ExampleDescription
    TEXTBEFORETEXTBEFORE("file.docx", ".")Returns "file" before .
    TEXTAFTERTEXTAFTER("file.docx", ".")Returns "docx" after .
    TEXTSPLITTEXTSPLIT("John,Doe", ",")Splits into "John" and "Doe"

    ✅ Bonus: Why Use These?

    • Avoids complex combinations of LEFT, RIGHT, FIND, and LEN
    • Works dynamically on arrays and ranges
    • Simplifies text cleaning and parsing in data analysis

    Top rated products

  • Excel 365 VALUETOTEXT Function Explained: Syntax, Examples, and Use Cases

    🔤 VALUETOTEXT Function in Excel 365 – Explained in Detail

    ✅ What is VALUETOTEXT?

    The VALUETOTEXT function in Excel 365 converts any value — number, text, logical value, or error — into a text string.

    It is particularly useful when you want to ensure data types are consistent, especially when working with dynamic arrays, formulas, or combining different data types into text outputs.

    📘 Syntax

    =VALUETOTEXT(value, [format])
    ArgumentDescription
    valueThe value or range you want to convert to text
    format (optional)Format type: 0 for concise (default), 1 for strict

    🧩 Format Options

    • 0 (Concise) – Outputs text without quotes, more human-readable.
    • 1 (Strict) – Outputs text with quotes, useful for programming/debugging.

    📌 Examples

    ✅ Example 1: Convert a number to text

    =VALUETOTEXT(123)

    Result: "123"

    ✅ Example 2: Convert boolean to text

    =VALUETOTEXT(TRUE)

    Result: "TRUE"

    ✅ Example 3: Convert a text value (with default format)

    =VALUETOTEXT("Excel")

    Result: "Excel" (No quotes in the result because default format is concise)

    ✅ Example 4: Use strict formatting

    =VALUETOTEXT("Excel", 1)

    Result: "\"Excel\"" (Quotes included)

    ✅ Example 5: Convert a formula result

    =VALUETOTEXT(A1+B1)

    If A1 = 10 and B1 = 20, result: "30"

    🧠 Usage with Arrays

    If you use VALUETOTEXT on an array, it returns each item as a text string, making it useful for debugging array formulas.

    =VALUETOTEXT({1,2,3})

    Result: { "1", "2", "3" } (array of text)

    🎯 When Should You Use VALUETOTEXT?

    • ✅ When building dynamic labels, tooltips, or messages with text + values
    • ✅ When converting numeric output to text format for export
    • ✅ For debugging dynamic array formulas
    • ✅ To standardize data type for further text manipulation or functions like TEXTJOIN, CONCAT, etc.

    ⚠️ Important Notes

    • VALUETOTEXT is available only in Excel 365 and Excel 2021.
    • It’s similar to TEXT, but simpler and doesn’t require a number format.
    • Different from VALUE, which converts text to a number (opposite functionality).

    🔁 Comparison: VALUETOTEXT vs TEXT

    FeatureVALUETOTEXTTEXT
    Converts to text?✅ Yes✅ Yes
    Requires format?❌ No✅ Yes (number format)
    Works on arrays?✅ Yes✅ Yes (limited)
    Output formattingBasic text conversionCustom number/text format

    Best selling products

  • XLOOKUP Function in Excel 365: Complete Guide with Examples and Top 20 Interview Questions

    🔍 How to Use the XLOOKUP Function in Excel 365 — Detailed Guide

    ✅ What is XLOOKUP?

    XLOOKUP is a powerful lookup function introduced in Excel 365 and Excel 2021 to replace older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH. It can search horizontally or vertically, supports approximate/partial matches, and even returns custom messages when no match is found.

    📌 Syntax:

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    ArgumentDescription
    lookup_valueThe value to search for
    lookup_arrayThe range or array to search in
    return_arrayThe range or array to return data from
    if_not_found(Optional) Value to return if no match is found
    match_mode(Optional) 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
    search_mode(Optional) 1 = search from first to last (default), -1 = search from last to first

    🧪 Basic Example:

    You have the following data:

    AB
    ProductPrice
    Apple100
    Banana60
    Mango80

    To find the price of Mango:

    =XLOOKUP("Mango", A2:A4, B2:B4)

    ➡️ Result: 80

    🧪 Example with if_not_found:

    =XLOOKUP("Orange", A2:A4, B2:B4, "Not Available")

    ➡️ Result: Not Available (because “Orange” doesn’t exist)

    🧪 Example using wildcard match:

    =XLOOKUP("*man*", A2:A4, B2:B4, , 2)

    ➡️ This matches any product containing “man” (e.g., “Mango”)

    🧪 Reverse Lookup (Bottom to Top):

    =XLOOKUP("Mango", A2:A4, B2:B4, , 0, -1)

    ➡️ Searches from bottom to top. Useful if the latest entry is preferred.

    🧠 20 Interview-Based Questions on XLOOKUP with Answers

    Q1. What is XLOOKUP in Excel?
    A1. XLOOKUP is a modern lookup function that replaces older functions like VLOOKUP and HLOOKUP. It can search vertically or horizontally and offers more flexibility.

    Q2. How is XLOOKUP better than VLOOKUP?
    A2. XLOOKUP allows lookup to the left, supports default return on no match, wildcards, and reverse searches, which VLOOKUP cannot do.

    Q3. Can XLOOKUP search horizontally?
    A3. Yes. You can use it like HLOOKUP by selecting rows instead of columns.

    Q4. What happens if the lookup value is not found?
    A4. If you specify the if_not_found parameter, that value is returned. Otherwise, Excel returns a #N/A error.

    Q5. How can you use XLOOKUP for an exact match?
    A5. Either omit the match_mode (default is exact) or explicitly set it to 0.

    Q6. Can XLOOKUP return an entire row or column?
    A6. Yes, it supports dynamic arrays, so it can return multiple values from a row or column.

    Q7. What does match_mode = 2 mean?
    A7. It enables wildcard matching using * (any number of characters) or ? (single character).

    Q8. What is the purpose of the search_mode parameter?
    A8. It controls the search direction: 1 = top to bottom (default), -1 = bottom to top.

    Q9. Is XLOOKUP case-sensitive?
    A9. No, XLOOKUP is not case-sensitive by default.

    Q10. Can XLOOKUP replace INDEX + MATCH?
    A10. Yes, and it’s simpler to write and understand.

    Q11. What’s the difference between XLOOKUP and LOOKUP?
    A11. LOOKUP is an older function requiring sorted data; XLOOKUP doesn’t and is more robust.

    Q12. What is returned if multiple matches are found?
    A12. XLOOKUP returns the first match, unless search_mode is set to -1 (then it returns the last match).

    Q13. Can XLOOKUP handle blank cells?
    A13. Yes. It will match blank cells if "" is used as the lookup_value.

    Q14. Can XLOOKUP be nested with other functions?
    A14. Yes, it works well inside other functions like IF, SUM, etc.

    Q15. How does XLOOKUP behave in arrays with errors?
    A15. It stops at the first error unless error handling (like IFERROR) is added.

    Q16. Is XLOOKUP available in Excel 2016 or 2019?
    A16. No. XLOOKUP is only available in Excel 365 and Excel 2021.

    Q17. Can XLOOKUP search from right to left?
    A17. Yes, it’s not restricted by column order like VLOOKUP.

    Q18. How to use XLOOKUP for range lookups (approximate match)?
    A18. Set match_mode to -1 (for next smaller) or 1 (for next larger).

    Q19. Can you perform two-way lookups using XLOOKUP?
    A19. Yes. Combine two XLOOKUPs — one for row and one for column.

    Q20. How does XLOOKUP handle dynamic named ranges or structured tables?
    A20. It works seamlessly with dynamic arrays, tables, and named ranges.

    Top rated products

  • Top New Functions in Excel 2021 Explained with Examples: XLOOKUP, FILTER, SORT & More

    🆕 New Functions Introduced in Excel 2021 — Explained with Usefulness

    Microsoft Excel 2021 brought a significant upgrade by introducing several dynamic array functions and smarter lookup and filtering tools. These functions were previously exclusive to Microsoft 365 users but are now part of the perpetual Excel 2021 version, helping users automate tasks, reduce formula complexity, and improve data analysis.

    🔍 1. XLOOKUP

    Purpose: To search for a value in a column or row and return a corresponding value from another column or row.

    Syntax:

    excelCopyEdit=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    Usefulness:

    • Replaces older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH.
    • No need to worry about column numbers or data being sorted.
    • Supports exact, approximate, and wildcard matching.
    • Works both vertically and horizontally.

    Example:

    excelCopyEdit=XLOOKUP("Apple", A2:A100, B2:B100, "Not Found")

    This looks for “Apple” in column A and returns the value from column B in the same row.

    🔢 2. XMATCH

    Purpose: Returns the relative position of a value within a range, similar to MATCH but more versatile.

    Syntax:

    excelCopyEdit=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    Usefulness:

    • Supports reverse search and wildcard matching.
    • Useful for locating the position of a value in arrays, which can then be used with INDEX or CHOOSE.

    Example:

    excelCopyEdit=XMATCH(50, A1:A10)

    Returns the position of 50 in the range A1:A10.

    🧹 3. FILTER

    Purpose: Extracts only the data that meets certain criteria from a range.

    Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])

    Usefulness:

    • Dynamically displays filtered data in a separate area.
    • Great for dashboards, reporting, or conditional data extraction.
    • Automatically expands or contracts based on the filter results.

    Example:

    excelCopyEdit=FILTER(A2:B10, B2:B10="North")

    Returns only rows where the second column has “North” as the value.

    🔢 4. SORT

    Purpose: Sorts a range or array in ascending or descending order.

    Syntax:

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

    Usefulness:

    • Unlike traditional sort, it doesn’t affect the original data.
    • Automatically updates when the source data changes.
    • Useful in dynamic dashboards and data tables.

    Example:

    excelCopyEdit=SORT(A2:B10, 2, -1)

    Sorts the range A2:B10 by the second column in descending order.

    🔢 5. SORTBY

    Purpose: Sorts a range or array based on the values in another array.

    Syntax:

    excelCopyEdit=SORTBY(array, by_array1, [sort_order1], ...)

    Usefulness:

    • More flexible than SORT, as it lets you sort by related fields not in the output.
    • Excellent for sorting one table based on another column or lookup.

    Example:

    excelCopyEdit=SORTBY(A2:B10, C2:C10, 1)

    Sorts A2:B10 based on values in C2:C10 in ascending order.

    🔄 6. UNIQUE

    Purpose: Extracts a list of unique values from a range or array.

    Syntax:

    excelCopyEdit=UNIQUE(array, [by_col], [exactly_once])

    Usefulness:

    • Removes duplicates quickly and dynamically.
    • Especially useful for creating drop-down lists or summary views.

    Example:

    excelCopyEdit=UNIQUE(A2:A100)

    Returns a list of unique values from column A.

    🔢 7. SEQUENCE

    Purpose: Generates a list of sequential numbers in an array format.

    Syntax:

    excelCopyEdit=SEQUENCE(rows, [columns], [start], [step])

    Usefulness:

    • Helpful for creating index numbers, date sequences, or testing data structures.
    • Can generate both 1D and 2D arrays.

    Example:

    excelCopyEdit=SEQUENCE(5,1,10,2)

    Generates 5 numbers starting from 10, increasing by 2 (i.e., 10, 12, 14, 16, 18).

    🎲 8. RANDARRAY

    Purpose: Returns an array of random numbers.

    Syntax:

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [integer])

    Usefulness:

    • Generate sample data for testing or simulations.
    • Can return decimal or whole numbers.
    • Recalculates with every workbook change unless frozen with F9.

    Example:

    excelCopyEdit=RANDARRAY(5, 2, 1, 100, TRUE)

    Generates a 5×2 array of random whole numbers between 1 and 100.

    📦 9. LET

    Purpose: Assigns names to calculation results to reuse within a formula, improving performance and readability.

    Syntax:

    excelCopyEdit=LET(name1, value1, calculation)

    Usefulness:

    • Makes complex formulas more readable.
    • Optimizes performance by computing once and reusing.

    Example:

    excelCopyEdit=LET(x, A1+10, x*2)

    Calculates A1 + 10 once, stores it as x, and then returns x * 2.

    📘 Bonus: Other Useful Functions from Excel 2019 Now Common in Excel 2021

    While not brand-new to Excel 2021, the following were improved and widely adopted:

    • TEXTJOIN – Joins multiple text items with a delimiter.
    • IFS – Replaces complex nested IF formulas.
    • SWITCH – Easier alternative to multiple IF statements for fixed-value cases.

    ⚠️ Not Available in Excel 2021

    Some advanced functions are only available in Microsoft 365 (not Excel 2021), such as:

    • TEXTSPLIT
    • DROP, TAKE, VSTACK, HSTACK
    • WRAPROWS, WRAPCOLS
    • TOCOL, TOROW
    • MAP, REDUCE, SCAN, BYROW, BYCOL

    These are part of the Lambda family and advanced array manipulations introduced later in Excel 365.

    Top rated products

  • Mastering the DROP Function in Excel 365: Syntax, Examples, and Interview Q&A

    ✅ How to Use DROP Function in Excel 365

    The DROP function in Excel 365 is a dynamic array function that allows you to remove a specified number of rows or columns from the start or end of an array or range.

    🔧 Syntax:

    DROP(array, rows, [columns])
    ArgumentDescription
    arrayThe array or range of data to modify
    rowsNumber of rows to drop. Positive to drop from top, negative from bottom
    [columns](Optional) Number of columns to drop. Positive to drop from left, negative from right

    📘 Example 1: Drop Top 2 Rows

    =DROP(A1:C5, 2)

    ➡️ Drops the first 2 rows, returns rows 3 to 5 from columns A to C.

    📘 Example 2: Drop Last 1 Row and First 1 Column

    =DROP(A1:C5, -1, 1)

    ➡️ Drops the last row and the first column.

    📘 Example 3: Drop Last 2 Columns

    =DROP(A1:D4, 0, -2)

    ➡️ Keeps all rows, removes the last 2 columns.

    🧠 Interview-Based Questions (with answers)

    Q1. What is the use of the DROP function in Excel 365?

    A1. The DROP function is used to exclude a specific number of rows or columns from an array or range, returning the remaining values dynamically. It’s particularly helpful when cleaning data or adjusting tables on the fly.

    Q2. Can the DROP function be used with ranges that include text data?

    A2. Yes, the DROP function works with arrays that include text, numbers, dates, or any Excel-supported data types.

    Q3. What will the result be if you use a negative value for the rows or columns arguments in DROP?

    A3. A negative value for rows drops rows from the bottom. A negative value for columns drops columns from the right.

    Q4. What happens if you use the DROP function on a range smaller than the number of rows or columns you try to drop?

    A4. Excel will return a #CALC! error, indicating the drop exceeds the array bounds.

    Q5. Can you combine DROP with other dynamic array functions like SORT or FILTER?

    A5. Yes, DROP is often combined with functions like SORT, FILTER, TAKE, or UNIQUE to create powerful, flexible data transformations in Excel 365.

    On sale products

  • How to Use ARRAYTOTEXT Function in Excel 365?

    ✅ What is the ARRAYTOTEXT Function?

    ARRAYTOTEXT(array, [format]) is a text function in Excel 365 that converts an array (range of values or formulas) into a single text string.
    It is helpful when you want to visualize or export the values of a range as a single readable string.

    Syntax

    ARRAYTOTEXT(array, [format])
    • array – Required. The array or range to convert.
    • format – Optional.
      • 0 (default): Returns compact format (best for ranges with just values).
      • 1: Returns strict format (adds quotes and brackets, good for formulas or nested arrays).

    Priya runs a home tiffin service in Pune. Every day, she notes down the dishes prepared in Excel. Her customers often ask for the daily menu via WhatsApp.

    She wants a quick way to convert the dish list in cells (A2:A6) into a single line of text that she can copy and send.

    Data in Excel

    A2: Roti
A3: Paneer Butter Masala
A4: Dal Tadka
A5: Rice
A6: Salad

    Use ARRAYTOTEXT

    =ARRAYTOTEXT(A2:A6)

    Output (Compact format):

    Roti,Paneer Butter Masala,Dal Tadka,Rice,Salad

    ✅ Now Priya can copy-paste this line into WhatsApp quickly.

    🛠 Strict Format Option

    If Priya uses:

    =ARRAYTOTEXT(A2:A6, 1)

    She gets:

    {"Roti";"Paneer Butter Masala";"Dal Tadka";"Rice";"Salad"}

    Useful if she needs to paste it into a formula or some system that requires strict array syntax.

    🎯 When to Use ARRAYTOTEXT?

    • Sending list of items via email/WhatsApp (like Priya).
    • Logging or displaying a formula output for review.
    • Saving snapshot of values in formulas (for audit or export).
    • Creating readable logs from dynamic data.

    ✅ Summary

    FeatureDetails
    Function NameARRAYTOTEXT
    PurposeConverts a range/array to a single text
    Optional ArgumentFormat: 0 (compact), 1 (strict)
    Real-life Use CaseTiffin service menu sharing, student name list export, audit logs

    On sale products

  • How to Create a Pivot Table from Another Pivot Table in Excel (Step-by-Step Guide)

    Creating a Pivot Table from another Pivot Table in Excel can be very helpful when you want to summarize, filter, or analyze data further without returning to the raw source data. Here’s how you can do it the right way, along with best practices and real-world examples.

    🧠 Why Make a Pivot Table from Another Pivot Table?

    Sometimes, your original Pivot Table has too much detail, and you want to:

    • Summarize it again (e.g., monthly to yearly totals)
    • Filter it differently without changing the original
    • Build dashboards with multiple views of the same summarized data

    ✅ Methods to Create a Pivot Table from Another Pivot Table

    🔹 Method 1: Use the Existing Pivot Table as a Data Source

    ⚠️ Note: This works only if the original Pivot Table was created from a data range or table, not from OLAP models or external sources.

    Steps:

    1. Click anywhere inside the original Pivot Table.
    2. Press Ctrl + A to select the whole Pivot Table.
    3. Copy it using Ctrl + C.
    4. Paste it into a new location using Paste Special → Values.
    5. Select the pasted data.
    6. Go to Insert → PivotTable.
    7. Choose the pasted data as your new source.
    8. Click OK.

    You now have a new Pivot Table that is based on the output of the first one, and you can summarize it however you want.

    🔹 Method 2: Convert First Pivot Table to Static Data

    If you want a permanent copy of the summarized data from Pivot #1:

    1. Select the Pivot Table → Right-click → Copy.
    2. Paste it as Values Only using Paste Special (Ctrl + Alt + V).
    3. Use this new static table as the source for your second Pivot Table.

    🔹 Method 3: Use GetPivotData or Power Query (Advanced)

    For more dynamic scenarios:

    • Use GETPIVOTDATA to extract specific values and feed them into formulas or dashboards.
    • Use Power Query to pull data from the Pivot Table range, clean it, and create a new Pivot Table.

    📊 Example Scenario

    Original Pivot Table

    You have a monthly sales Pivot Table:

    MonthSales RepSales Amount
    JanRavi₹25,000
    JanNeha₹30,000
    FebRavi₹22,000
    FebNeha₹33,000

    You now want to:
    👉 Create a yearly total per Sales Rep
    Use the steps above to:

    • Copy & paste the first Pivot Table as values
    • Insert a new Pivot Table summarizing by Sales Rep only

    🚀 Bonus Tip: Use Named Ranges for Flexibility

    If you plan to reuse this method:

    • Convert the pasted values into a named range or Excel Table
    • This helps you reference it dynamically across the workbook

    ⚠️ Important Notes

    • The second Pivot Table won’t update automatically if you change the first one unless it’s linked via formulas or Power Query
    • Always double-check for grand totals or subtotals, which might skew your new Pivot Table

    📘 Want to Learn Pivot Tables Like a Pro?

    ✅ Master dynamic reporting, nested PivotTables, GETPIVOTDATA, slicers, charts, and more in my course:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Best selling products

  • EXPAND Function in Excel 365 – Resize Arrays with Ease

    🔍 What is the EXPAND Function in Excel 365?

    The EXPAND function is a dynamic array function introduced in Excel 365. It allows you to resize an array to a specified number of rows and columns by adding empty cells or a custom value as needed.

    Think of it as a way to force a range into a specific shape, useful when building dynamic templates, padding arrays, or preparing structured data outputs.

    🔧 Syntax

    =EXPAND(array, rows, columns, [pad_with])
    ArgumentDescription
    arrayThe original array to expand
    rowsThe total number of rows desired in the output
    columnsThe total number of columns desired
    pad_with(Optional) The value to use for padding if the array is smaller than the specified size (default is blank)

    ✅ Examples of EXPAND in Excel

    🔹 Example 1: Expand a 2×2 Array to 4×4 with Blanks

    =EXPAND({1,2;3,4}, 4, 4)

    ✅ Output:

    1   2   ""  ""
3   4   ""  ""
""  ""  ""  ""
""  ""  ""  ""

    🔹 Example 2: Expand with a Custom Padding Value

    =EXPAND({1,2;3,4}, 3, 5, 0)

    ✅ Output:

    1   2   0   0   0  
3   4   0   0   0  
0   0   0   0   0

    🔹 Example 3: Use with VSTACK or HSTACK

    You can combine EXPAND with VSTACK to align data nicely:

    =EXPAND(VSTACK({1,2}, {3,4}), 5, 2, "-")

    🔹 Example 4: Prepare Fixed Template Output

    Use EXPAND to standardize report sections, e.g., always show 10 rows in a report, even if data has fewer:

    =EXPAND(A2:B4, 10, 2, "N/A")

    🔹 Example 5: Resize Named Ranges for Dashboards

    Create a uniform input structure for dashboards that doesn’t break when data is missing.

    🧠 Why Use EXPAND?

    • Ensures consistent array size for formulas or visualizations
    • Helps in report automation
    • Pairs well with functions like DROP, TAKE, VSTACK, HSTACK
    • Great for data transformation pipelines

    ❓ 5 Interview-Based Questions on EXPAND

    1. What is the purpose of the EXPAND function in Excel 365?

    Expected Answer: To resize an array to a specified number of rows and columns, filling in missing cells with blank or a defined value.

    2. What will this formula return?

    =EXPAND({10,20;30,40}, 3, 3, "X")

    Answer:

    10   20   X  
30   40   X  
X    X    X

    3. How can EXPAND be used to create a fixed-size export template?

    Expected Answer: By padding data with a default value up to a known row/column count, ensuring uniformity in exports or dashboard feeds.

    4. What happens if the array passed to EXPAND is already larger than the specified size?

    Answer: Excel will not truncate the array — it will simply return the full array. EXPAND only pads; it doesn’t shrink.

    5. Write a formula to expand a 2×2 array into a 4×4 array using the value “NA” as filler.

    =EXPAND({1,2;3,4}, 4, 4, "NA")

    🎓 Learn More Excel 365 Power Functions

    Ready to master advanced Excel functions like EXPAND, REDUCE, SCAN, LAMBDA, and more?

    👉 Join My Excel Mastery Course
    ✅ Covers automation, dynamic reports, dashboards, and real-life use cases.

    Top rated products

  • Mastering the MAP Function in Excel 365 – Explained with Examples

    🧠 What is the MAP Function in Excel 365?

    The MAP function is a Lambda helper function in Excel 365 that lets you apply a custom formula (Lambda) to each element of one or more arrays.

    It’s similar to the “map” concept in programming — you pass in arrays, and MAP processes each corresponding item across those arrays using your custom logic.

    🔧 Syntax of MAP

    =MAP(array1, [array2], ..., lambda(value1, [value2], ..., calculation))
    ParameterDescription
    array1The first array to apply the function to
    array2…(Optional) Additional arrays
    lambdaA custom function that defines what to do with each item in the array(s)

    ✅ Key Features

    • Processes each item in an array (or matching items from multiple arrays)
    • Great for row-wise transformations, custom logic, or string manipulation
    • Fully dynamic and compatible with spilled ranges
    • Ideal for creating reusable custom logic without VBA

    🔍 Examples of MAP in Action

    🔹 Example 1: Add 10 to Each Number

    If A1:A5 = {5, 10, 15, 20, 25}

    =MAP(A1:A5, LAMBDA(x, x + 10))

    ✅ Output: {15, 20, 25, 30, 35}

    🔹 Example 2: Combine Names from Two Columns

    AB
    RaviSharma
    PriyaMehta
    AkashVerma
    =MAP(A2:A4, B2:B4, LAMBDA(f, l, f & " " & l))

    ✅ Output:
    Ravi Sharma
    Priya Mehta
    Akash Verma

    🔹 Example 3: Apply IF Logic to Array

    Add 100 if value > 50, else keep it unchanged:

    =MAP(A1:A5, LAMBDA(x, IF(x > 50, x + 100, x)))

    🔹 Example 4: Format Text to Title Case (First letter capital)

    Assume names in A2:A4:

    =MAP(A2:A4, LAMBDA(n, UPPER(LEFT(n,1)) & LOWER(MID(n,2,LEN(n)))))

    ✅ Transforms “rAVI” into “Ravi”

    📌 Real-World Use Cases

    • Process data row-by-row or column-by-column
    • Apply different logic to multiple inputs
    • Create advanced conditional formatting via formulas
    • Perform string cleanup, name formatting, score adjustments
    • Replace helper columns with dynamic logic

    ❓ Interview-Style Question

    Question:
    You’re given two arrays:
    Array1 = {10, 20, 30, 40}
    Array2 = {1, 2, 3, 4}

    Write a formula using MAP that multiplies each pair of values from Array1 and Array2.

    Answer:

    =MAP({10, 20, 30, 40}, {1, 2, 3, 4}, LAMBDA(a, b, a * b))

    ✅ Output: {10, 40, 90, 160}

    🎓 Want to Master Lambda & MAP in Real Scenarios?

    Learn how to use MAP, REDUCE, SCAN, LAMBDA, and other Excel 365 functions with dashboards, custom tools, and real case studies in:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Top rated products