Training By Himanshu

Tag: Microsoft Excel

  • 100 Excel Shortcuts to Boost Productivity and Save Hours at Work

    If you want to save hours every week and become the fastest Excel user in your office, then mastering keyboard shortcuts is the smartest move. They not only speed up your work but also make you look like a true Excel pro.

    100 Excel Shortcuts with Explanations

    Here’s a categorized list so you can learn easily:

    🔹 Basic Shortcuts

    1. Ctrl + N – Create a new workbook instantly.
    2. Ctrl + O – Open an existing workbook.
    3. Ctrl + S – Save the current file.
    4. F12 – Save As dialog box.
    5. Ctrl + P – Print your sheet.
    6. Ctrl + W – Close the current workbook.
    7. Ctrl + F4 – Close Excel completely.
    8. Ctrl + Z – Undo the last action.
    9. Ctrl + Y – Redo the last undone action.
    10. Ctrl + C – Copy selected cells.

    🔹 Navigation Shortcuts

    1. Ctrl + Arrow Keys – Jump to the last filled cell in that direction.
    2. Ctrl + Home – Go to the first cell (A1).
    3. Ctrl + End – Go to the last used cell.
    4. Page Up/Page Down – Move one screen up/down.
    5. Alt + Page Up/Down – Move one screen left/right.
    6. Tab/Shift + Tab – Move right/left in a row.
    7. Ctrl + G (F5) – Go to a specific cell.
    8. Ctrl + F – Find anything in the sheet.
    9. Ctrl + H – Replace text or values.
    10. Ctrl + Backspace – Show active cell.

    🔹 Data Entry & Editing

    1. F2 – Edit the active cell.
    2. Alt + Enter – Insert a line break inside a cell.
    3. Ctrl + D – Fill down from the above cell.
    4. Ctrl + R – Fill right from the left cell.
    5. Ctrl + ; – Insert today’s date.
    6. Ctrl + Shift + : – Insert current time.
    7. Ctrl + Shift + “+” – Insert new row/column.
    8. Ctrl + “-“ – Delete selected row/column.
    9. Ctrl + Space – Select entire column.
    10. Shift + Space – Select entire row.

    🔹 Formatting Shortcuts

    1. Ctrl + B – Bold text.
    2. Ctrl + I – Italic text.
    3. Ctrl + U – Underline text.
    4. Alt + H + O + I – Auto-fit column width.
    5. Alt + H + O + A – Auto-fit row height.
    6. Ctrl + 1 – Format cells dialog box.
    7. Ctrl + Shift + $ – Apply currency format.
    8. Ctrl + Shift + % – Apply percentage format.
    9. Ctrl + Shift + # – Apply date format.
    10. Ctrl + Shift + @ – Apply time format.

    🔹 Selection Shortcuts

    1. Ctrl + A – Select all cells in sheet.
    2. Ctrl + Shift + Arrow Keys – Select range to last filled cell.
    3. Shift + Arrow Keys – Select cells one by one.
    4. Ctrl + Shift + End – Select from current cell to last used cell.
    5. Ctrl + Shift + Home – Select from current cell to A1.
    6. Ctrl + * (asterisk) – Select current data region.
    7. Shift + Space + Ctrl – Select entire worksheet.
    8. F8 – Extend selection mode.
    9. Shift + F8 – Add non-adjacent cells to selection.
    10. Alt + ; – Select only visible cells.

    🔹 Formula Shortcuts

    1. Alt + = – AutoSum quickly.
    2. Shift + F9 – Calculate selected cells.
    3. F9 – Calculate all sheets.
    4. Ctrl + ` (grave accent) – Show formulas instead of results.
    5. Ctrl + Shift + Enter – Enter array formula.
    6. Ctrl + Shift + A – Insert function arguments.
    7. Shift + F3 – Insert function window.
    8. Ctrl + Shift + L – Apply/remove filters.
    9. Alt + Down Arrow – Open filter dropdown.
    10. Ctrl + [ – Trace dependent cells.

    🔹 Worksheet Shortcuts

    1. Ctrl + Page Up – Move to previous sheet.
    2. Ctrl + Page Down – Move to next sheet.
    3. Shift + F11 – Insert new worksheet.
    4. Alt + E + L – Delete current worksheet.
    5. Ctrl + 9 – Hide selected rows.
    6. Ctrl + Shift + 9 – Unhide rows.
    7. Ctrl + 0 – Hide selected columns.
    8. Ctrl + Shift + 0 – Unhide columns.
    9. Alt + O + H + R – Rename sheet.
    10. Ctrl + Drag Sheet Tab – Copy worksheet.

    🔹 Advanced & Miscellaneous

    1. Alt + F1 – Create a chart in same sheet.
    2. F11 – Create chart in new sheet.
    3. Ctrl + K – Insert hyperlink.
    4. Ctrl + Alt + V – Paste Special dialog box.
    5. Ctrl + Shift + V – Paste values only.
    6. Ctrl + Alt + T – Insert table.
    7. Ctrl + Shift + O – Select cells with comments.
    8. Shift + F2 – Edit comment.
    9. Ctrl + Alt + F9 – Recalculate all worksheets.
    10. Alt + F8 – Open macro dialog box.

    🔹 Time-Saving Favorites

    1. Ctrl + T – Create table from data.
    2. Alt + H + S + I – Insert sparkline.
    3. Ctrl + Shift + K – Insert hyperlink quickly.
    4. Ctrl + Shift + U – Expand/Collapse formula bar.
    5. Alt + H + V + S – Paste Special with options.
    6. Alt + H + D + C – Delete column.
    7. Alt + H + D + R – Delete row.
    8. Alt + A + M – Remove duplicates.
    9. Alt + N + P – Insert pivot table.
    10. Alt + F + T – Excel options.

    🔹 Final 10 Power Shortcuts

    1. Ctrl + Alt + Shift + F9 – Force full calculation.
    2. Alt + F11 – Open VBA editor.
    3. Alt + Q – Close VBA editor.
    4. Ctrl + Shift + F3 – Create named ranges.
    5. Ctrl + F3 – Name manager.
    6. Alt + A + T – Apply text to columns.
    7. Alt + H + O + I – Auto-fit column width.
    8. Ctrl + Shift + ! – Apply number format.
    9. Alt + D + F + F – Freeze panes.
    10. Alt + W + F + F – Toggle freeze panes.

    ✅ Final Tip

    Don’t try to memorize all 100 shortcuts in one go. Start with 10 most useful ones (like Copy, Paste Special, AutoSum, Filters, and Navigation). Once they become second nature, add 5–10 more every week. Within a month, you’ll be working twice as fast as before—and easily become the “Excel Champion” in your office.

    Subscribe to our newsletter!

    [newsletter_form type=”minimal”]

    Featured products

  • Top 20 Excel Tricks That Will Make You Work Faster

    Microsoft Excel is more than just rows and columns—it’s a productivity powerhouse. Yet, most people only use a fraction of its potential. Whether you are a student, a professional, or someone managing personal finances, knowing the right Excel tricks can save you hours of work every week.

    In this article, we’ll cover the top 20 Excel tricks that will make you faster, smarter, and more confident while working with data.

    1. Use Flash Fill for Instant Data Entry

    Typing repetitive patterns like names, email IDs, or codes?

    • Just type the first example, press Ctrl + E, and Excel will auto-complete the rest.
      👉 Example: If you have a column of full names, type the first first-name in the next column and press Ctrl + E. Excel instantly extracts all first names.

    2. Quickly Select Data with Ctrl + Shift + Arrow Keys

    Instead of dragging the mouse, use:

    • Ctrl + Shift + ↓ to select an entire column of data.
    • Ctrl + Shift + → to select a full row.
      Perfect for big data sets!

    3. Turn Numbers into Charts in Seconds

    Highlight your data → Press Alt + F1 → Boom! Instant chart on the same sheet.
    👉 Use F11 to create the chart in a new sheet.

    4. Paste Special (Values, Formats, Operations)

    Right-click → Paste Special (or Ctrl + Alt + V) to:

    • Paste only values (skip formulas).
    • Paste formats only.
    • Even add, subtract, multiply directly while pasting.
      Huge time-saver!

    5. Insert Today’s Date & Time Instantly

    • Ctrl + ; → Inserts today’s date.
    • Ctrl + Shift + ; → Inserts current time.

    6. Use Conditional Formatting for Insights

    Highlight data trends without formulas.
    👉 Example: Use Color Scales to quickly spot highest and lowest values in a report.

    7. Freeze Panes for Easy Navigation

    Working on long spreadsheets?

    • Go to View → Freeze Panes to lock headers or first columns so they stay visible as you scroll.

    8. Quickly Remove Duplicates

    Go to Data → Remove Duplicates.
    👉 Example: Clean email lists or product codes in seconds.

    9. Use Text to Columns

    Split data without formulas.
    👉 Example: Separate first and last names or split data by commas, spaces, or custom delimiters.

    10. VLOOKUP (Still a King!)

    Find data instantly from large tables.
    👉 Example: =VLOOKUP(101, A2:D100, 3, FALSE) → Finds product info for ID 101.

    11. XLOOKUP (The Modern Alternative)

    Available in newer Excel versions. Unlike VLOOKUP, it works left-to-right and right-to-left.
    👉 Example: =XLOOKUP(101, A2:A100, D2:D100)

    12. Use FILTER Function

    Extract data that matches a condition.
    👉 Example: =FILTER(A2:D100, C2:C100=”Sales”) → Pulls all Sales department rows.

    13. Quick AutoSum with Alt + =

    Select a column → Press Alt + = → Excel automatically inserts a SUM formula.

    14. Turn Data into a Table (Ctrl + T)

    Tables auto-expand, have filters, and make formulas easier to manage.

    15. Power Query for Data Cleaning

    Found in Data → Get & Transform Data.
    👉 Combine multiple sheets, clean messy data, and automate tasks without writing a single formula.

    16. Use Named Ranges

    Instead of =SUM(A2:A100), use =SUM(Sales).
    👉 Named ranges make formulas easier to read and maintain.

    17. Keyboard Shortcuts You Must Know

    • Ctrl + Z → Undo
    • Ctrl + Y → Redo
    • Ctrl + F → Find
    • Ctrl + H → Replace
    • Ctrl + Space → Select entire column
    • Shift + Space → Select entire row

    18. IF Function for Logic

    👉 Example: =IF(C2>=50, “Pass”, “Fail”)
    Automates decision-making in your reports.

    19. Use PivotTables for Instant Summaries

    Analyze large data sets without writing formulas.
    👉 Example: Summarize sales by region, month, or product with just a few clicks.

    20. Protect Sheets and Cells

    Go to Review → Protect Sheet to lock formulas while allowing data entry in specific cells.

    ✅ Final Thoughts

    Learning these 20 Excel tricks can easily make you 2X faster at work. The key is not just to know them but to practice regularly. The more you use these shortcuts, formulas, and tools, the more time you’ll save.

    💡 Whether you’re preparing financial reports, handling business data, or cracking a job interview, mastering these Excel hacks will give you a professional edge.

    Office Productivity Courses

    Recommended Laptops

  • 100 Excel Interview Questions and Answers: Crack Your Next MIS, Data Analyst, or Excel Job Interview

    Microsoft Excel is a powerful tool used across industries for data analysis, reporting, financial modeling, and business intelligence. Whether you’re applying for roles in data analysis, finance, accounting, MIS (Management Information System), operations, or even marketing, a strong grip on Excel can set you apart.

    👤 Who Should Use This?

    This list is ideal for:

    • Job seekers in roles like MIS Executive, Data Analyst, Financial Analyst, Business Analyst, Operations Manager, or Accountant
    • Freshers preparing for entry-level roles requiring Excel
    • Professionals upskilling for promotions or transitions to analytical roles
    • Trainers or HR professionals preparing candidates for interviews

    ✅ Excel Interview Questions and Answers (100 Q&A)

    🟩 Section 1: Basic Excel Skills

    1. Q: What is Microsoft Excel used for?
      A: Excel is used for data entry, data analysis, calculations, charting, pivot tables, and automation using formulas and macros.
    2. Q: What is a cell in Excel?
      A: A cell is the intersection of a row and a column where data is entered.
    3. Q: What is the difference between a worksheet and a workbook?
      A: A worksheet is a single sheet in Excel; a workbook is a file containing one or more worksheets.
    4. Q: How do you save a workbook in Excel?
      A: Use Ctrl + S or go to File > Save/Save As.
    5. Q: What are the different data types in Excel?
      A: Text, Numbers, Dates, Boolean (TRUE/FALSE), Currency, and Custom formats.
    6. Q: How do you insert a new row or column?
      A: Right-click on the row/column header > Insert, or use Ctrl + Shift + "+".
    7. Q: How do you freeze panes?
      A: Go to View > Freeze Panes to lock rows/columns for scrolling.
    8. Q: What is a range in Excel?
      A: A range is a selection of two or more cells, e.g., A1:A10.
    9. Q: How can you wrap text in a cell?
      A: Select the cell, go to Home > Wrap Text.
    10. Q: How do you merge cells?
      A: Select cells > Home > Merge & Center.

    🟨 Section 2: Formulas and Functions

    1. Q: What is the difference between a formula and a function?
      A: A formula is user-created (e.g., =A1+A2), while a function is a predefined operation (e.g., =SUM(A1:A2)).
    2. Q: What does the SUM function do?
      A: It adds up numbers in a given range. Example: =SUM(A1:A5)
    3. Q: What is the use of IF function?
      A: It performs logical tests. Example: =IF(A1>50, “Pass”, “Fail”)
    4. Q: What does VLOOKUP do?
      A: It searches for a value in the first column and returns data from a specified column.
      Example: =VLOOKUP(101, A2:C10, 3, FALSE)
    5. Q: What is the difference between VLOOKUP and HLOOKUP?
      A: VLOOKUP searches vertically; HLOOKUP searches horizontally.
    6. Q: What does the INDEX function do?
      A: It returns the value of a cell at a specific row and column in a range.
    7. Q: How does MATCH work?
      A: MATCH returns the position of a value in a range.
      Example: =MATCH(50, A1:A10, 0)
    8. Q: What is the use of CONCATENATE or CONCAT function?
      A: Joins multiple text strings into one.
      Example: =CONCAT(A1, " ", B1)
    9. Q: What is the difference between COUNT, COUNTA, and COUNTBLANK?
      A:
      • COUNT: counts numbers only
      • COUNTA: counts non-empty cells
      • COUNTBLANK: counts empty cells
    10. Q: How do you round numbers in Excel?
      A: Use ROUND, ROUNDUP, or ROUNDDOWN functions.

    🟧 Section 3: Intermediate Excel (Data Tools & Formatting)

    1. Q: What are conditional formatting rules?
      A: They format cells based on criteria (e.g., highlight values > 100).
    2. Q: How do you apply data validation?
      A: Data > Data Validation to restrict input (e.g., allow only numbers 1–100).
    3. Q: What is the use of “Remove Duplicates”?
      A: It deletes repeated data from a range.
    4. Q: How to use Text to Columns?
      A: Data > Text to Columns (used to split data based on delimiters).
    5. Q: What is a named range?
      A: A defined name for a cell or range (e.g., =SalesTotal)
    6. Q: What are sparklines?
      A: Mini charts within a cell to show trends.
    7. Q: How do you use Find and Replace?
      A: Ctrl + F (Find), Ctrl + H (Replace)
    8. Q: What is Flash Fill?
      A: Automatically fills patterns based on previous entries (Ctrl + E)
    9. Q: What is a drop-down list in Excel?
      A: Created using Data Validation to restrict input to a list.
    10. Q: What is the use of Goal Seek?
      A: To find the input value needed to achieve a desired result.

    🟦 Section 4: Charts and Visualizations

    1. Q: How do you insert a chart?
      A: Select data > Insert > Choose a chart type (e.g., column, line, pie)
    2. Q: What is a combo chart?
      A: A chart combining two chart types (e.g., column + line)
    3. Q: What is a pivot chart?
      A: A chart based on PivotTable data.
    4. Q: Can charts be dynamic?
      A: Yes, by using named ranges or tables with formulas.
    5. Q: What is a slicer in charts or pivots?
      A: A filter control used to filter PivotTables visually.

    🟫 Section 5: Pivot Tables & Data Analysis

    1. Q: What is a PivotTable?
      A: A tool to summarize large data sets with drag-and-drop fields.
    2. Q: How do you insert a PivotTable?
      A: Insert > PivotTable > Choose data and location
    3. Q: Can you group data in PivotTable?
      A: Yes, right-click on values > Group (useful for dates or ranges)
    4. Q: What is the difference between Value Field Settings – SUM vs COUNT?
      A: SUM totals numeric values, COUNT counts entries.
    5. Q: How do you refresh a PivotTable?
      A: Right-click > Refresh or use the Refresh button in the Ribbon.

    🟥 Section 6: Advanced Excel

    1. Q: What is Power Query?
      A: A data transformation tool to import, clean, and combine data.
    2. Q: What is Power Pivot?
      A: A data modeling tool to create relationships and use DAX formulas.
    3. Q: What are array formulas?
      A: Formulas that perform multiple calculations on one or more items.
    4. Q: What is the use of XLOOKUP?
      A: A more powerful and flexible replacement for VLOOKUP.
    5. Q: How do you use dynamic arrays like FILTER and SORT?
      A:
      • =FILTER(range, condition) to filter data
      • =SORT(range, column, order) to sort data
    6. Q: What is a dashboard in Excel?
      A: A visual interface using charts, KPIs, and PivotTables to monitor key metrics.
    7. Q: What is DAX in Power Pivot?
      A: Data Analysis Expressions – a formula language for creating custom calculations.
    8. Q: What is a data model in Excel?
      A: A relational database built using Power Pivot or linked tables.
    9. Q: What is Solver?
      A: An add-in used for optimization problems (e.g., maximize profit).
    10. Q: Can Excel connect to external data sources?
      A: Yes, from Access, SQL Server, web, CSV, etc.

    🔵 Section 7: Macros and VBA

    1. Q: What is a macro in Excel?
      A: A recorded sequence of steps that can be replayed.
    2. Q: How do you record a macro?
      A: View > Macros > Record Macro
    3. Q: What is VBA?
      A: Visual Basic for Applications – programming language for automating tasks.
    4. Q: What is a module in VBA?
      A: A container for procedures or code.
    5. Q: How do you open the VBA editor?
      A: Press Alt + F11.

    🟣 Section 8: Macros and VBA (Continued)

    1. Q: What is the difference between a Sub and a Function in VBA?
      A: A Sub performs actions but doesn’t return a value. A Function performs actions and returns a value.
    2. Q: How do you write a simple macro in VBA to display a message box?
      A:
    Sub ShowMessage()
    MsgBox "Hello, this is a message!"
End Sub
    1. Q: How can you run a macro using a button?
      A: Insert a Form Control button from the Developer tab, assign the macro.
    2. Q: What is a UserForm in VBA?
      A: A custom form/dialog box you can design for data entry or interaction.
    3. Q: What are some common uses of VBA in Excel?
      A: Automating reports, generating emails, cleaning data, creating dashboards, etc.

    🔶 Section 9: Error Handling and Troubleshooting

    1. Q: What does #DIV/0! error mean?
      A: Division by zero error – occurs when dividing by 0 or a blank cell.
    2. Q: What is #N/A error?
      A: “Not Available” – typically occurs with lookup functions when value not found.
    3. Q: What is #REF! error?
      A: Invalid cell reference – often happens when a cell referred in a formula is deleted.
    4. Q: What is #VALUE! error?
      A: Incorrect data type used in a formula.
    5. Q: How do you use IFERROR function?
      A: Wrap formulas to catch and replace errors.
      Example: =IFERROR(A1/B1, "Error in calculation")
    6. Q: What is circular reference in Excel?
      A: A formula that refers to its own cell, creating an endless loop.
    7. Q: How do you audit formulas in Excel?
      A: Use Formula Auditing tools (Formulas > Trace Precedents/Dependents)
    8. Q: How to evaluate formulas step by step?
      A: Use “Evaluate Formula” tool under Formulas tab.
    9. Q: What is the purpose of Watch Window?
      A: To monitor the values of key cells during calculations.
    10. Q: How can you protect a worksheet or cell?
      A: Review > Protect Sheet. Use Format Cells > Protection to lock/unlock cells first.

    🔷 Section 10: Excel Productivity Tips

    1. Q: How do you quickly select a range of data?
      A: Use Ctrl + Shift + Arrow keys.
    2. Q: How do you select non-contiguous cells?
      A: Hold Ctrl and click on individual cells.
    3. Q: How do you convert rows to columns (or vice versa)?
      A: Use Paste Special > Transpose.
    4. Q: How do you remove blank rows quickly?
      A: Use filters to find blanks and delete rows.
    5. Q: What does Ctrl + ; do?
      A: Enters the current date.
    6. Q: What does Ctrl + Shift + L do?
      A: Applies or removes filters.
    7. Q: How can you repeat the last action?
      A: Press F4.
    8. Q: How to lock row 1 while scrolling?
      A: View > Freeze Panes > Freeze Top Row.
    9. Q: What does Alt + = do?
      A: Inserts the SUM function automatically.
    10. Q: How do you insert the current time?
      A: Press Ctrl + Shift + ;

    ⚫ Section 11: Scenario-Based & Practical Questions

    1. Q: You have employee data. How do you find duplicate names?
      A: Use Conditional Formatting > Highlight Duplicates or use =COUNTIF(range, cell)>1
    2. Q: How would you create an attendance tracker in Excel?
      A: Use dates in columns, names in rows, and mark “P”/”A”; use COUNTIF for totals.
    3. Q: How to find top 3 sales from a list?
      A: Use =LARGE(range, 1), =LARGE(range, 2), etc.
    4. Q: How to split full names into first and last names?
      A: Use =LEFT() and =RIGHT() with FIND() or use Text to Columns.
    5. Q: How would you highlight weekends in a calendar?
      A: Use Conditional Formatting with formula: =WEEKDAY(A1,2)>5
    6. Q: How do you prepare a monthly sales dashboard?
      A: Use PivotTables, Pivot Charts, Slicers, Conditional Formatting, KPI indicators.
    7. Q: A client sends data in PDF – how do you get it into Excel?
      A: Use Power Query > Get Data from PDF or copy-paste and clean.
    8. Q: How do you track changes in Excel?
      A: Use File > Info > Version History (for OneDrive) or use manual versioning.
    9. Q: How would you remove all hyperlinks in a sheet?
      A: Select all cells > Right-click > Remove Hyperlinks.
    10. Q: How do you compare two columns for matching entries?
      A: Use =IF(A2=B2, "Match", "No Match") or use =COUNTIF(range, value)

    🟤 Section 12: Bonus & Conceptual Questions

    1. Q: What is the default file extension for Excel?
      A: .xlsx (macro-enabled workbook: .xlsm)
    2. Q: Can you open CSV files in Excel?
      A: Yes, Excel can open and edit CSV files.
    3. Q: What are Excel Tables and their benefits?
      A: Structured data ranges with automatic formatting, filters, and dynamic references.
    4. Q: What is a 3D reference in Excel?
      A: A formula referring to the same cell across multiple sheets. Example: =SUM(Sheet1:Sheet3!A1)
    5. Q: What are dynamic named ranges?
      A: Named ranges that adjust automatically as data changes using formulas like OFFSET or INDEX.
    6. Q: How does Excel handle leap years in date calculations?
      A: Excel treats dates as serial numbers and accurately accounts for leap years.
    7. Q: What is the use of INDIRECT function?
      A: Returns a cell reference from a text string. Example: =INDIRECT("A"&1)
    8. Q: What is the TODAY function used for?
      A: Returns the current date. Example: =TODAY()
    9. Q: Can Excel perform web scraping?
      A: Yes, using Power Query or legacy Web connectors (with limitations).
    10. Q: What are some common interview tasks given in Excel interviews?
      A:
    • Creating dashboards
    • Cleaning raw data
    • Performing VLOOKUP/INDEX-MATCH
    • Creating PivotTables
    • Writing formulas for KPIs
    • Automating tasks using macros

    🎓 Final Tips for Excel Interview Preparation

    • Practice real-world Excel projects (MIS reports, dashboards, sales trackers).
    • Be comfortable with both mouse navigation and keyboard shortcuts.
    • Focus on accuracy, speed, and logic—especially when solving lookup or data-cleaning tasks.
    • If the job requires automation, learn VBA basics and Power Query.

    🚀 Master MIS & Data Automation – One Course, Endless Opportunities!


    Boost your career with our Complete MIS Training Program – designed for professionals who want to excel in Data Management, Reporting, and Automation using Excel, Access, Macros, and SQL.

    ✅ 16.5 hours of expert-led video
    📂 26 downloadable resources
    🏅 Certificate of Completion
    💼 Real-world projects & job-ready skills

    👉 Perfect for MIS aspirants, analysts, and working professionals.

    Start now and become the go-to expert for smart data solutions!
    🔗 Enroll today

    Featured products

  • 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 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

  • 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