Training By Himanshu

Tag: Excel Formulas

  • 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

  • Quick Ways to List All Hyperlinks in Excel: Formulas & Macros Explained

    To quickly list all hyperlinks in an Excel sheet, you can use a VBA macro, since Excel doesn’t have a built-in formula to directly extract all hyperlinks from a sheet. Below are multiple methods depending on your need and comfort level.

    ✅ Method 1: Use VBA to List All Hyperlinks in the Sheet

    📋 What it does:

    This macro will loop through all cells in the sheet and list every hyperlink’s text and URL in a new sheet.

    🔧 Steps:

    1. Press Alt + F11 to open the VBA Editor.
    2. Click Insert > Module.
    3. Paste the following code:
    Sub ListAllHyperlinks()
    Dim ws As Worksheet
    Dim linkCell As Hyperlink
    Dim outputSheet As Worksheet
    Dim i As Long

    ' Create a new sheet for the hyperlink list
    Set outputSheet = ThisWorkbook.Sheets.Add
    outputSheet.Name = "Hyperlink List"

    ' Add headers
    outputSheet.Cells(1, 1).Value = "Text to Display"
    outputSheet.Cells(1, 2).Value = "Hyperlink Address"

    i = 2

    ' Loop through all sheets and all hyperlinks
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> outputSheet.Name Then
            For Each linkCell In ws.Hyperlinks
                outputSheet.Cells(i, 1).Value = linkCell.TextToDisplay
                outputSheet.Cells(i, 2).Value = linkCell.Address
                i = i + 1
            Next linkCell
        End If
    Next ws

    MsgBox "All hyperlinks listed in the sheet 'Hyperlink List'.", vbInformation
End Sub
    1. Press F5 or run the macro from Excel.

    📝 Output:

    A new sheet named “Hyperlink List” will be created with two columns:

    Text to DisplayHyperlink Address
    Googlehttps://google.com
    Training Sitehttps://trainingbyhimanshu.in

    ⚡ Method 2: Use Formula (If Hyperlink Is in a Cell)

    You can extract a hyperlink URL from a cell using a User Defined Function (UDF) via VBA:

    📌 VBA UDF to extract hyperlink address:

    Function GetHyperlinkAddress(rng As Range) As String
    On Error Resume Next
    GetHyperlinkAddress = rng.Hyperlinks(1).Address
End Function

    Use it like this in Excel:

    =GetHyperlinkAddress(A2)

    This works only if the hyperlink is inserted as a clickable link in the cell.

    🚫 Limitation of Excel Formulas:

    Built-in Excel formulas like =CELL("filename", A1) or =HYPERLINK(...) can’t extract the actual hyperlink address unless it’s added as a function result — which is rare.

    🧠 Summary:

    MethodBest ForTools Needed
    VBA MacroListing all links from any sheetBasic VBA
    VBA UDFExtracting hyperlink from one cellFormula + VBA
    ManualOne or two links onlyCopy-paste

    On sale products

  • Difference Between Formula and Function in Excel

    Let’s break down the difference between a Formula and a Function in Excel in simple terms, and include detailed examples to make it clear.

    FeatureFormulaFunction
    DefinitionA formula is a user-defined expression to perform calculations.A function is a built-in Excel operation used within formulas.
    Who creates it?Created manually by the userProvided by Excel
    ComplexityCan be simple or complexOften simplifies complex calculations
    Starts withAlways starts with =Always used inside a formula that starts with =
    Examples=A1 + A2=B2*C2-100=SUM(A1:A5)=IF(A1>50, "Pass", "Fail")

    🔍 What is a Formula?

    A formula is any user-created expression that performs a calculation or operation. It can include values, cell references, operators, and functions.

    ✅ Examples of Formulas:

    1. =A1 + A2
      ➤ Adds the values in cells A1 and A2.
    2. =B2 * 10 + C2
      ➤ Multiplies B2 by 10, then adds C2.
    3. =SUM(A1:A5) - D1
      ➤ Uses a function (SUM) within a formula.

    💡 All functions are part of formulas, but not all formulas include functions.

    🔍 What is a Function?

    A function is a predefined operation in Excel that performs a specific task, such as adding numbers, checking conditions, or working with text and dates.

    Functions save time and make complex calculations easier.

    ✅ Common Excel Functions:

    FunctionDescriptionExample
    SUM()Adds a range of numbers=SUM(A1:A5)
    AVERAGE()Finds the mean of values=AVERAGE(B1:B5)
    IF()Performs a logical test=IF(A1>50, "Pass", "Fail")
    VLOOKUP()Looks up a value in a table=VLOOKUP(101, A2:C10, 2, FALSE)
    LEN()Counts characters in a cell=LEN("Excel") returns 5

    🧠 Formula vs Function – A Simple Analogy

    • Think of a formula like a full sentence:
      ➤ “I added two numbers and subtracted 5.”
    • Think of a function like a word or tool used in that sentence:
      ➤ “added” is like the SUM() function.

    ✅ Summary

    FormulaFunction
    Made by the userBuilt-in by Excel
    Can contain operators, values, cell references, and functionsUsed inside formulas
    More flexible but manualEasier and more efficient

    📝 Final Example

    =SUM(A1:A3) + B1
    • This entire thing is a formula
    • Inside it, SUM(A1:A3) is a function

    On sale products

  • Mastering Autofill in Excel: Fill Values, Text, and Formulas Effortlessly

    In Excel, Autofill is a powerful feature that allows you to automatically fill cells with a series of values, formulas, or formatting. It helps save time and effort, especially when working with large data sets.

    Let’s break down Autofill in detail:

    🔹 What is Autofill?

    Autofill allows you to quickly fill cells with repetitive or sequential data like numbers, dates, days of the week, months, formulas, and custom lists by dragging the fill handle (a small square at the bottom-right corner of a selected cell or range).

    🔹 Types of Values and Text You Can Autofill

    1. Numeric Values

    • Example: If you type 1 in a cell and drag down, Excel fills the same value (1) by default.
    • If you type 1 in A1 and 2 in A2, and select both and drag, Excel detects the pattern and continues (3, 4, 5…).

    2. Text

    • If you type text like "Item" and drag down, Excel repeats "Item" in all the cells.
    • If the text contains a number (e.g., "Item1"), Excel can increment the number part (Item2, Item3…) only if it detects a pattern.

    3. Dates

    • Type 01-Jan-2023, drag down — Excel continues with 02-Jan-2023, 03-Jan-2023, etc.
    • Works for days, months, years.

    4. Days and Months

    • If you enter "Monday" or "January", Excel recognizes it as part of a built-in list and autofills the rest (Tuesday, Wednesday… or February, March…).

    5. Formulas

    • Autofill can copy formulas with relative references.
    • Example: =A1+B1 will become =A2+B2, =A3+B3, etc. as you drag down.

    🔹 How to Use Autofill

    🧭 Method 1: Drag Fill Handle

    1. Enter the starting value(s) in one or more cells.
    2. Select the cell(s).
    3. Move your mouse to the bottom-right corner until the fill handle (a small black square) appears.
    4. Drag it down, right, up, or left to autofill the cells.

    🧭 Method 2: Double-Click Fill Handle

    • If there is adjacent data (like a column next to it filled), double-click the fill handle to autofill down automatically to match the adjacent data’s length.

    🔹 Custom Autofill Lists

    You can create your own autofill list. Example: if you regularly type Step 1, Step 2, Step 3…

    🔧 Steps:

    1. Go to File > Options > Advanced.
    2. Scroll to General > Click Edit Custom Lists.
    3. Add your list (e.g., Step 1, Step 2, Step 3) and click Add.

    🔹 Autofill Options (Smart Tag)

    After using Autofill, a small box appears (Autofill Options). Click it to choose how the data is filled:

    • Copy Cells – Repeats the same value.
    • Fill Series – Continues the pattern.
    • Fill Formatting Only – Applies the same formatting, not values.
    • Fill Without Formatting – Copies values only, not formatting.
    • Flash Fill – Smart fill based on detected patterns (e.g., splitting names).

    🔹 Flash Fill (Smart Autofill)

    Example:

    • If Column A has John Smith, and you type John in Column B and Smith in Column C, Excel can automatically fill the rest of the rows by pattern.
    • Use Ctrl + E or go to Data > Flash Fill.

    🔹 Important Notes

    • Autofill detects patterns, not just values.
    • It works differently for text-only entries and mixed (text + number).
    • Works with both horizontal and vertical ranges.
    • Relative and absolute cell references affect how formulas are autofilled.

    🔚 Summary Table

    Input TypeResult by AutofillNotes
    1, 23, 4, 5...Detects numeric pattern
    JanFeb, Mar...Recognizes built-in list
    MondayTuesday...Day names auto-filled
    Item1Item2, Item3...Text with number = smart pattern
    =A1+B1=A2+B2...Formula with relative reference
    HelloHello, Hello...Repeats text

    Watch the Video