Training By Himanshu

Tag: Excel Tips

  • 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

  • What is the RANDARRAY Function in Excel 365?

    The RANDARRAY function generates an array of random numbers. You can define the number of rows and columns, the minimum and maximum values, and whether you want whole numbers or decimals.

    It’s part of the dynamic array functions introduced in Excel 365 and is useful for simulations, testing, data sampling, random list creation, and more.

    🔧 Syntax of RANDARRAY

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [whole_number])
    ParameterDescription
    rows(Optional) Number of rows to return
    columns(Optional) Number of columns to return
    min(Optional) Minimum value (default is 0)
    max(Optional) Maximum value (default is 1)
    whole_number(Optional) TRUE for integers, FALSE for decimals (default is FALSE)

    ✅ Examples of RANDARRAY in Excel

    🔹 Example 1: Generate a 5×2 Array of Random Decimals Between 0 and 1

    excelCopyEdit=RANDARRAY(5, 2)

    Generates 5 rows and 2 columns of decimal numbers between 0 and 1.

    🔹 Example 2: Generate 10 Random Whole Numbers Between 1 and 100

    excelCopyEdit=RANDARRAY(10, 1, 1, 100, TRUE)

    Creates a single column of 10 random whole numbers between 1 and 100.

    🔹 Example 3: Generate a 3×3 Matrix of Random Decimals Between 50 and 75

    excelCopyEdit=RANDARRAY(3, 3, 50, 75)

    Each cell contains a random decimal number in the range 50–75.

    🔹 Example 4: Dynamic Range for Randomized Data

    If you link rows/columns to cell values:

    excelCopyEdit=RANDARRAY(A1, B1, 10, 99, TRUE)

    This generates random whole numbers based on user-defined dimensions from cells A1 and B1.

    📌 Key Features

    • Recalculates every time the sheet changes (just like RAND or RANDBETWEEN)
    • Generates arrays dynamically — no need to drag formulas
    • Replaces the need for helper columns when generating random values
    • Supports structured logic when used with INDEX, SORTBY, SEQUENCE, etc.

    🧠 Use Cases

    • Random student roll numbers
    • Create sample datasets for testing
    • Simulate random sampling in analytics
    • Build games or quizzes in Excel
    • Generate randomized IDs, passwords, or numbers

    🔒 Prevent Random Changes

    To freeze the results (make them static):

    1. Select the range.
    2. Press Ctrl + C to copy.
    3. Right-click > Paste Values.

    🚀 Combine with Other Functions

    Example: Randomly sort names in A2:A10

    excelCopyEdit=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

    This randomly shuffles the list of names.

    🎓 Want to Learn More About Excel 365’s Smartest Tools?

    Explore RANDARRAY, SORTBY, UNIQUE, FILTER, LET, and more in real-world projects with my Excel course:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Top rated products

  • What is the REDUCE Function in Excel 365?

    The REDUCE function is a Lambda helper function introduced in Excel 365. It allows you to loop through an array, applying a formula to each element, and accumulate a single result (like a running total or combined value).

    Think of it like a fold or accumulator function in programming — it starts with an initial value and “reduces” an array step-by-step using logic you define.

    🔧 Syntax

    excelCopyEdit=REDUCE(initial_value, array, lambda(accumulator, value))
    ArgumentDescription
    initial_valueThe starting value (can be 0, “”, etc.)
    arrayThe array you want to process
    lambdaA custom formula with two parameters: accumulator (running total) and value (current array element)

    ✅ Examples of REDUCE

    🔹 Example 1: Sum All Numbers in an Array

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + v))
    • A1:A5 contains {10, 20, 30, 40, 50}
    • Output: 150

    🔁 Starts with 0, then adds each value:
    0 + 10 → 10 + 20 → 30 + 30 → 60 + 40 → 100 + 50 = 150

    🔹 Example 2: Concatenate All Text Values

    excelCopyEdit=REDUCE("", A1:A4, LAMBDA(a, v, a & v))
    • A1:A4 contains: {"Hi", " ", "there", "!"}
    • Output: "Hi there!"

    🔹 Example 3: Count Values Greater Than 50

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + IF(v > 50, 1, 0)))
    • If A1:A5 = {40, 55, 60, 30, 80}
    • Output: 3 (since 55, 60, and 80 > 50)

    🔹 Example 4: Multiply All Values

    excelCopyEdit=REDUCE(1, A1:A4, LAMBDA(a, v, a * v))
    • A1:A4 = {2, 3, 4, 5}
    • Output: 120

    🔹 Example 5: Create a Dash-Separated List

    excelCopyEdit=REDUCE("", A1:A3, LAMBDA(a, v, IF(a = "", v, a & "-" & v)))
    • A1:A3 = {Jan, Feb, Mar}
    • Output: "Jan-Feb-Mar"

    🎯 Why is REDUCE Useful?

    • Performs row-by-row logic without VBA or helper columns
    • Great for cumulative totals, conditional aggregations, and string building
    • Works well inside LAMBDA-based custom functions

    ❓ 3 Interview-Based Questions on REDUCE

    1. What is the key difference between REDUCE and SCAN in Excel 365?

    (Expected Answer: REDUCE returns only the final accumulated result, while SCAN returns all intermediate steps.)

    2. How would you use REDUCE to count how many numbers are even in a range?

    (Hint: Use IF(MOD(value,2)=0, 1, 0) inside LAMBDA and accumulate the result.)

    3. What is the purpose of the initial_value argument in REDUCE?

    (Expected: It defines the starting point of the accumulation. For summing, it would be 0; for concatenating, it may be an empty string.)

    Top rated products

  • Master the XMATCH Function in Excel 365

    📘 What is the XMATCH Function in Excel?

    The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

    🔧 Syntax of XMATCH

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
    ParameterDescription
    lookup_valueThe value you want to search for
    lookup_arrayThe range or array to search in
    match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
    search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

    ✅ Key Features of XMATCH

    • Supports vertical and horizontal lookups
    • Can search from last to first (search_mode = -1)
    • Allows wildcard characters (match_mode = 2)
    • Works with arrays and spilled ranges
    • Better compatibility with dynamic arrays

    🧪 Examples

    🔹 Example 1: Basic Exact Match

    =XMATCH("Priya", A2:A10)

    Searches for “Priya” in the list and returns the position where it’s found.

    ✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

    🔹 Example 2: Wildcard Match

    =XMATCH("P*", A2:A10, 2)

    Returns the first item starting with “P”.

    ✅ Useful for partial string lookups.

    🔹 Example 3: Reverse Search

    =XMATCH("Complete", A2:A10, 0, -1)

    Searches bottom-up for “Complete”.

    🔹 Example 4: Approximate Match

    If you have numbers like 50, 60, 70, and you’re looking for 65:

    =XMATCH(65, A2:A10, 1)

    Returns the position of the next larger number (70).

    🔹 Example 5: Use with INDEX for Advanced Lookup

    =INDEX(B2:B10, XMATCH("Ravi", A2:A10))

    Finds Ravi in column A and returns corresponding value from column B.

    ✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

    🎓 Common Use Cases

    • Find row/column numbers dynamically
    • Combine with INDEX for 2D lookups
    • Reverse search to find last matching item
    • Match using wildcards like "*Report" or "Jan???"
    • Create dynamic dashboards or filters

    ❓ 5 Interview-Based Questions on XMATCH

    1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
    2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
    3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
    4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
    5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

    📌 Final Thoughts

    XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

    🚀 Want to Master Excel 365 Lookups?

    Enroll in my in-depth Excel training course covering:

    • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
    • Dashboards, automation, case studies

    👉 Mastering MS Excel – A Comprehensive Training Course

    On sale products

  • How to Use SORT and SORTBY Functions in Excel 365

    The SORT and SORTBY functions in Excel 365 are part of the dynamic array family — they allow you to sort data easily, flexibly, and without altering the original range.

    🧠 1. SORT Function in Excel

    🔧 Syntax:

    SORT(array, [sort_index], [sort_order], [by_col])
    ArgumentDescription
    arrayThe range or array to sort
    sort_indexColumn or row number to sort by (default is 1)
    sort_order1 = Ascending, -1 = Descending
    by_colTRUE = sort by columns, FALSE = by rows (default)

    ✅ Real-Life Example: Sorting Employee Salaries

    You have the following data in A2:B6:

    NameSalary
    Ravi35000
    Priya42000
    Neha39000
    Akash30000
    Anjali45000

    Formula to sort by Salary (ascending):

    =SORT(A2:B6, 2, 1)

    ✅ Output:

    NameSalary
    Akash30000
    Ravi35000
    Neha39000
    Priya42000
    Anjali45000

    Formula to sort by Salary (descending):

    =SORT(A2:B6, 2, -1)

    🧠 2. SORTBY Function in Excel

    The SORTBY function is more flexible — it allows you to sort one array based on another.

    🔧 Syntax:

    SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
    ArgumentDescription
    arrayThe data to sort
    by_array1The column/array to sort by
    sort_order11 = Ascending, -1 = Descending

    ✅ Real-Life Example: Sort Students by Marks in Another Column

    StudentRoll NoMarks
    Aman10287
    Kirti10192
    Mohan10476
    Preeti10389

    Formula to sort by Marks (descending):

    =SORTBY(A2:C5, C2:C5, -1)

    ✅ Output:

    StudentRoll NoMarks
    Kirti10192
    Preeti10389
    Aman10287
    Mohan10476

    🔄 SORT vs SORTBY: What’s the Difference?

    FeatureSORTSORTBY
    Sort by positionYesNo
    Sort by other dataLimited✅ Full flexibility
    Multiple criteriaLimited✅ Supports multiple arrays
    Practical use casesTables, visible dataSort by hidden/helper columns

    🎯 Real-World Use Cases

    • Sort sales data by amount or date
    • Sort student scores without rearranging original data
    • Sort project tasks by deadline
    • Sort inventory by stock level using helper columns

    🎓 Want to Learn More Excel Sorting & Automation Tricks?

    Explore how to use Excel 365’s new tools like SORT, FILTER, TAKE, UNIQUE, and more in real business applications.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Smart Excel functions (SORT, FILTER, XLOOKUP)
    • Dashboards & automation
    • Excel VBA and real-life case studies

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 TAKE Function Explained: Extract Top or Bottom Rows Easily

    The TAKE function in Excel 365 is one of the powerful Dynamic Array functions introduced to make data extraction easier and cleaner.

    🧠 What is the TAKE Function?

    The TAKE function allows you to extract a specific number of rows or columns from the beginning or end of a range or array.

    It’s extremely useful when you want to:

    • Limit results (like top 5 or last 3 rows)
    • Display recent data
    • Dynamically filter table portions

    🔧 Syntax of TAKE

    =TAKE(array, [rows], [columns])

    📌 Arguments:

    ParameterDescription
    arrayThe range or array to extract data from
    rows(Optional) Number of rows to take. Positive = from top, Negative = from bottom
    columns(Optional) Number of columns to take. Positive = from left, Negative = from right

    ✅ Practical Examples of TAKE in Excel 365

    🔸 Example 1: Get the Top 5 Sales Entries

    If you have a list of sales data in range A2:B20 (Product, Sales):

    =TAKE(A2:B20, 5)

    ✅ Returns the first 5 rows from the dataset (top 5 sales records).

    🔸 Example 2: Get the Last 3 Rows

    To extract the last 3 rows from that same range:

    =TAKE(A2:B20, -3)

    ✅ Returns rows 18 to 20 from the array.

    🔸 Example 3: Take First 2 Columns Only

    If your data range is A1:E10, and you want only the first 2 columns:

    =TAKE(A1:E10,,2)

    ✅ Returns columns A and B with all 10 rows.

    🔸 Example 4: Last 5 Rows, Last 2 Columns

    For a full dynamic slice (e.g., a summary report):

    =TAKE(A2:E100, -5, -2)

    ✅ Takes the last 5 rows and last 2 columns from your dataset.

    🛑 Notes

    • You can omit either the rows or columns argument if not needed.
    • Works only in Excel 365 and Excel for the Web.
    • Output updates dynamically if the source array changes.
    • Compatible with other dynamic functions like SORT, FILTER, UNIQUE.

    🧠 Combine with Other Functions

    =TAKE(SORT(A2:B100, 2, -1), 3)

    ✅ Sorts the data in descending order by Sales (column 2), and returns the top 3 results.

    📌 Real-Life Use Cases

    • Display Top N performers from a team list
    • Show Latest 5 transactions in a bank log
    • Get first 2 columns from a wide dataset (e.g., IDs & names)
    • Build a summary dashboard showing recent trends

    🎓 Want to Master Excel’s Smartest Features?

    Learn to use TAKE, DROP, SORT, FILTER, XLOOKUP, LAMBDA, and more with real-life dashboards and projects.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ What you’ll learn:

    • Excel 365’s latest tools
    • Powerful automation techniques
    • Real-world problem-solving with formulas
    • BONUS: Excel dashboards, VBA, and charts

    🎯 Click Here to Enroll Now

    On sale products

  • UNIQUE Function in Excel 365 – Explained with Examples

    The UNIQUE function in Excel 365 is a game-changing tool that helps you extract distinct or unique values from a list or range — instantly and dynamically.

    It’s part of Excel’s new dynamic array functions, introduced in Excel 365 and Excel for the web.

    📘 What is the UNIQUE Function?

    The UNIQUE function returns a list of unique values from a range, array, or table column. It removes duplicates automatically and updates dynamically if the source data changes.

    🧪 Syntax

    UNIQUE(array, [by_col], [exactly_once])
    ArgumentDescription
    arrayThe range or array to extract unique values from
    [by_col]Optional. Use TRUE for column-wise, FALSE (default) for row-wise
    [exactly_once]Optional. If TRUE, returns values that appear only once

    ✅ Example 1: Basic Unique List

    List of names in A2:A10:

    Ravi  
Priya  
Ravi  
Neha  
Priya  
Amit

    In another cell:

    =UNIQUE(A2:A10)

    ✅ Output:

    Ravi  
Priya  
Neha  
Amit

    ✅ Example 2: Unique Values That Appear Only Once

    Same list, but only those that appear exactly once:

    =UNIQUE(A2:A10,,TRUE)

    ✅ Output:

    Neha  
Amit

    ✅ Example 3: Unique Rows from a Table

    If you have multiple columns like Name and Department:

    NameDept
    RaviSales
    NehaHR
    RaviSales
    AmitFinance

    Use:

    =UNIQUE(A2:B5)

    ✅ Output:

    Ravi  Sales  
Neha  HR  
Amit  Finance

    🔄 Dynamic Behavior

    When new values are added to the source range, the UNIQUE function automatically updates its results — no manual refresh needed.

    🧠 Real-Life Use Cases

    • Remove duplicate customer names or email addresses
    • Count unique product types in sales data
    • Build dynamic dropdown lists with Data Validation
    • Filter one-time entries from logs or records

    🎓 Learn More Excel Magic

    Functions like UNIQUE, FILTER, SORT, XLOOKUP, and LAMBDA can transform your workflow.

    📘 For step-by-step guidance, real-world dashboards, and hands-on Excel automation:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Dynamic Array Functions
    • Excel 365 Exclusive Features
    • Dashboard Projects
    • Form Controls, VBA & More

    🎯 Click Here to Enroll Now

    On sale products

  • ISOMITTED Function in Excel 365 – Complete Guide

    The ISOMITTED function is a new and specialized function available in Excel 365 that works exclusively within LAMBDA functions. It’s designed to check if an argument has been omitted when the LAMBDA function is called.

    📘 What is ISOMITTED in Excel?

    ISOMITTED checks whether a specific parameter in a LAMBDA function was provided or left out when the function was called.

    🔧 Syntax:

    =ISOMITTED(argument)
    • argument — A parameter defined in a LAMBDA.
    • Returns TRUE if the argument is omitted, FALSE if provided.

    🧠 Why is it Useful?

    It allows you to:

    • Define optional parameters in your custom functions.
    • Create default values when a parameter is not supplied.
    • Add dynamic behavior depending on whether a user provided an input.

    ✅ Example 1: Optional Discount Argument

    Let’s define a function that calculates the total price after an optional discount.

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100)

    💡 Since discount is omitted, it returns 100 — the original price.

    But:

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100, 0.2)

    💡 Returns 80 after applying the 20% discount.

    ✅ Example 2: Creating a Named Function

    You can also create a reusable function:

    1. Go to Formulas > Name Manager > New
    2. Name: SmartDiscount
    3. Refers to:
    =LAMBDA(price, discount,
    IF(ISOMITTED(discount), price, price - price * discount)
)

    Now you can use:

    =SmartDiscount(200)        → returns 200  
=SmartDiscount(200, 0.1)   → returns 180

    🛑 Limitations

    • Can only be used inside a LAMBDA function
    • Not available outside that context
    • Only works in Excel 365 and Excel for the Web

    🎯 Use Cases

    • Building reusable Excel mini-apps
    • Creating optional inputs in custom functions
    • Creating smarter calculators with defaults

    🎓 Want to Learn More Excel 365 Advanced Features?

    If you want to explore LAMBDA, ISOMITTED, LET, and more dynamic Excel tools, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • Excel 365-exclusive functions
    • LET, LAMBDA, XLOOKUP, FILTER
    • Real-life examples and automations
    • Projects and templates for professionals

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 LAMBDA Function Explained: Make Your Own Formulas Without VBA

    The LAMBDA function in Excel 365 is a powerful and advanced feature that allows you to create custom functions without VBA or macros. It’s like writing your own Excel formula and saving it as a function you can reuse across the workbook.

    Let’s walk through it step by step with simple explanations and examples.

    🧠 What is the LAMBDA Function in Excel?

    The LAMBDA function allows you to:

    • Define custom functions using Excel formulas
    • Reuse logic without copying complex formulas
    • Replace repetitive expressions
    • Avoid writing VBA or using Add-ins

    🔧 Syntax:

    =LAMBDA(parameter1, parameter2, ..., calculation)

    You define inputs (parameters) and use them in the calculation.

    ✅ How to Use the LAMBDA Function

    🔸 Step 1: Create a Simple LAMBDA Formula

    Example: Create a LAMBDA to square a number.

    =LAMBDA(x, x^2)(5)
    • Here, x is the input.
    • x^2 is the formula.
    • (5) is the value passed to the function.

    ✅ Output: 25

    🔸 Step 2: Create a LAMBDA Function for Reuse

    You can also name your custom LAMBDA function for repeated use.

    🧭 Steps:

    1. Go to Formulas > Name Manager
    2. Click New
    3. In Name, type: SquareNum
    4. In Refers to, enter: =LAMBDA(x, x^2)
    5. Click OK.

    Now you can use your new function like any built-in Excel function:

    =SquareNum(6)

    ✅ Output: 36

    📘 Real-Life Examples of LAMBDA

    🔸 Example 1: Calculate Profit Margin

    =LAMBDA(cost, price, (price - cost)/price)

    Use it as:

    =LAMBDA(cost, price, (price - cost)/price)(100, 150)

    ✅ Output: 0.333 (or 33.3%)

    🔸 Example 2: Fahrenheit to Celsius Converter

    =LAMBDA(f, (f - 32) * 5/9)(98.6)

    ✅ Output: 37°C

    🔸 Example 3: Named Reusable LAMBDA for Area of a Circle

    1. Go to Name Manager > New
    2. Name: CircleArea
    3. Refers to:
    =LAMBDA(r, PI()*r^2)

    Use it in a cell:

    =CircleArea(5)

    ✅ Output: 78.54

    🛑 Important Notes

    • LAMBDA must end with a calculation using defined parameters.
    • You can nest LAMBDAs for advanced logic.
    • Works only in Excel 365 (and Excel for the web).
    • Doesn’t run without input — i.e., you must “call” it at least once for testing.

    🎓 Want to Master More Advanced Excel Tools?

    If you’re ready to build powerful logic, reusable formulas, dashboards, and even Excel apps without coding, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • LET, LAMBDA, FILTER, XLOOKUP
    • Real-world dashboards
    • Excel automation & templates
    • For beginners to advanced users

    🎯 Click to Enroll Now

    Best selling products

  • How to Use the LET Function in Excel 365 (Step-by-Step with Examples)

    The LET function is one of the powerful new additions in Excel 365, designed to make complex formulas easier to read, write, and optimize.

    🧠 What is the LET Function in Excel?

    The LET function allows you to:

    • Define variables within a formula
    • Assign values to those variables
    • Use the variables multiple times without repeating the calculation

    This makes formulas cleaner, faster, and more efficient—especially when repeating the same expressions.

    🧪 Syntax of the LET Function

    LET(name1, name_value1, [name2, name_value2, …], calculation)
    • name1, name2: Variable names (your choice)
    • name_value1, name_value2: The value or expression assigned to the variable
    • calculation: The final expression that uses those variables

    ✅ Benefits of Using LET:

    • Improves readability of complex formulas
    • Reduces redundancy (no need to repeat the same expression)
    • Enhances performance (calculates repeated expressions once)

    🔍 3 Practical Examples of LET in Excel 365

    🔸 Example 1: Simplify a Repeated Calculation

    📍 Task:

    Calculate total profit using Revenue - Cost, but both are used multiple times in the formula.

    🔽 Without LET:

    =(A2*B2 - C2) + (A2*B2 - C2)*0.1

    ✅ With LET:

    =LET(
    Revenue, A2*B2,
    Profit, Revenue - C2,
    Profit + Profit*0.1
)

    ✔️ This is more readable and avoids repeating A2*B2.

    🔸 Example 2: Average of Adjusted Scores

    📍 Task:

    Subtract a baseline (e.g., 5) from each of three values and then average the results.

    ✅ With LET:

    =LET(
    x, A2-5,
    y, B2-5,
    z, C2-5,
    AVERAGE(x, y, z)
)

    This makes it clear what is being subtracted and from where.

    🔸 Example 3: Nested Logical Check

    📍 Task:

    If a score is greater than 40, calculate bonus as 10% of it. If it’s less than or equal to 40, no bonus.

    ✅ With LET:

    =LET(
    score, A2,
    bonus, score*0.1,
    IF(score>40, bonus, 0)
)

    🧠 You can now reuse score and bonus in the formula cleanly.

    📘 Real-Life Use Cases

    • Financial modeling (e.g., tax formulas, profit sharing)
    • Academic scoring systems
    • Inventory management with dynamic thresholds
    • Any case where a formula becomes long or repeats similar calculations

    🎓 Learn More Excel 365 Features Like LET

    Mastering functions like LET can help you write smarter, faster, and more powerful Excel models. For full Excel training with real-world applications:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Covers:

    • Excel 365 new functions (LET, FILTER, XLOOKUP)
    • Automation with formulas and VBA
    • Real-life dashboards and business models

    🎯 Click to Enroll Now

    Top rated products