Training By Himanshu

Tag: Excel dynamic arrays

  • How to Use the VSTACK Function to Combine Multiple Sheets in Excel

    The VSTACK function in Excel (available in Microsoft 365 and Excel 2021+) allows you to vertically stack arrays or ranges. It’s especially powerful when you want to combine data from multiple sheets into a single list for reporting, analysis, or dashboards.

    🧠 Function Syntax:

    VSTACK(array1, [array2], …)
    • array1, array2, … are the ranges or arrays you want to stack vertically.
    • All ranges must have the same number of columns.

    📘 Scenario-Based Example: Combine Sales Data of Multiple Cities

    Let’s say you’re maintaining monthly sales data for your retail business across 3 different cities – Delhi, Mumbai, and Kolkata. Each city has its own worksheet with the same structure.

    📄 Sheet: Delhi

    NameProductSales
    RajeshLaptop55000
    AnjaliPhone30000

    📄 Sheet: Mumbai

    NameProductSales
    VikramTablet20000
    NehaPhone25000

    📄 Sheet: Kolkata

    NameProductSales
    ArjunLaptop60000
    PriyaPhone28000

    🛠️ Step-by-Step: Combine All Sheets Using VSTACK

    1. Go to a new sheet called “AllData”.
    2. In cell A1, enter this formula:
    =VSTACK(Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

    ✅ This will vertically combine the data from the three sheets into one continuous table.

    📌 With Header Row Included

    If you also want the headers, you can do:

    =VSTACK({"Name","Product","Sales"}, Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

    This adds a custom header at the top.

    🎯 Tips for Real-World Use

    • Dynamic Ranges: Use Excel Tables or LET function with named ranges for flexibility.
    • Error Handling: Use IFERROR inside nested formulas if some ranges might be empty.
    • Tracking Source Sheet: Add a column with the sheet name:
    =VSTACK(
  CHOOSE({1,2,3,4},
    "Delhi", Delhi!A2:A3, Delhi!B2:B3, Delhi!C2:C3),
  CHOOSE({1,2,3,4},
    "Mumbai", Mumbai!A2:A3, Mumbai!B2:B3, Mumbai!C2:C3),
  CHOOSE({1,2,3,4},
    "Kolkata", Kolkata!A2:A3, Kolkata!B2:B3, Kolkata!C2:C3)
)

    This adds the city name as a column, useful for filtering and pivoting.

    📣 Promote Your Excel Skills

    Want to learn more Excel automation and dynamic functions like VSTACK, LET, FILTER, etc.?

    👉 Mastering MS Excel – A Comprehensive Course
    Build job-ready Excel skills with real-world business scenarios and Indian datasets.

    Top rated products

  • How to Use TOCOL and TOROW Functions in Excel (With Examples)

    Excel 365 and Excel 2021 introduce powerful dynamic array functions like TOCOL and TOROW, which help you reshape arrays into a single column or row effortlessly. Let’s explore how they work and when to use them.

    🔷 1. TOCOL Function – Convert to Column

    📌 Purpose:

    TOCOL transforms a 2D array or table into a single vertical list.

    🧮 Syntax:

    excelCopyEditTOCOL(array, [ignore], [scan_by_column])
    ParameterDescription
    arrayThe range to convert
    ignore0 = none, 1 = ignore blanks, 2 = ignore errors
    scan_by_columnTRUE = by column (default), FALSE = by row

    📊 Example:

    ABC
    123
    456
    excelCopyEdit=TOCOL(A1:C2)

    Result:

    CopyEdit1  
4  
2  
5  
3  
6

    With blank cells ignored:

    excelCopyEdit=TOCOL(A1:C2, 1)

    🔷 2. TOROW Function – Convert to Row

    📌 Purpose:

    TOROW turns a 2D array into a single horizontal list.

    🧮 Syntax:

    excelCopyEditTOROW(array, [ignore], [scan_by_column])

    📊 Example:

    Using the same data:

    excelCopyEdit=TOROW(A1:C2)

    Result:

    CopyEdit1   4   2   5   3   6

    Row-wise scan:

    excelCopyEdit=TOROW(A1:C2, 0, FALSE)

    Result:

    CopyEdit1   2   3   4   5   6

    ✅ Why Use TOCOL/TOROW?

    • Flatten 2D ranges for lookup or processing
    • Prepare lists for filtering or advanced formulas
    • Save time over manual copy-paste or TRANSPOSE hacks

    🎓 Take Your Excel Skills to the Next Level!

    Want to master functions like TOCOL, TOROW, XLOOKUP, FILTER, TEXTSPLIT, and more?

    🚀 Join my best-selling Excel course:
    👉 Mastering MS Excel – A Comprehensive Training Course

    ✅ Available in both Online & Pen Drive formats
    📈 Suitable for students, professionals & business users

    Top rated 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

  • 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