Training By Himanshu

Tag: Excel VBA

  • 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

  • Extract Numbers from Text in Excel Using VBA – Works for Indian & European Formats

    🧾 Scenario:

    At Shree Tech Pvt. Ltd., Priya is a finance executive handling a lot of messy Excel data received from multiple vendors and sales teams across India and Europe.

    One day, she encounters a peculiar problem.
    In the “Remarks” column, instead of clean numbers, she sees entries like:

    • "₹3,499 paid in full"
    • "1.250,50 EUR"
    • "Advance of 7500.00 received"
    • "Amount is Rs. 2,50,000/-"

    She needs to extract only the numeric value from these cells, but Excel’s built-in tools can’t help much.

    That’s when her teammate, Rohit, a skilled MIS guy, steps in with a magic wand—a custom VBA function called getNumber.

    🧙‍♂️ The Magic VBA Function: getNumber

    Here’s the full code Rohit shares:

    vbaCopyEditPublic Function getNumber(fromThis As Range) As Double
    'Extract the number from a cell and return it.
    Dim retVal As String
    Dim ltr As String, i As Integer, european As Boolean
    
    retVal = ""
    getNumber = 0
    european = False
    
    On Error GoTo last
    'Check if the range contains European format number i.e. , for decimal point
    If fromThis.Value Like "*.*,*" Then
        european = True
    End If
    
    For i = 1 To Len(fromThis)
        ltr = Mid(fromThis, i, 1)
        If IsNumeric(ltr) Then
            retVal = retVal & ltr
        ElseIf ltr = "." And (Not european) And Len(retVal) > 0 Then
            retVal = retVal & ltr
        ElseIf ltr = "," And european And Len(retVal) > 0 Then
            retVal = retVal & "."
        End If
    Next i
    getNumber = CDbl(retVal)
last:
End Function

    🔍 Line-by-Line Breakdown with Office-style Explanation

    ✅ What it does:

    Extracts numbers embedded in any text, whether the number is in Indian format (e.g., 2,50,000) or European format (e.g., 1.234,56).

    🎬 Scene-by-Scene Breakdown:

    🪪 Characters:

    • fromThis: The Excel cell that has the mixed content (like "Total ₹4,500.50 paid").
    • retVal: The string variable used to slowly build the extracted number.
    • european: A flag to detect if commas are used as decimal separators (common in European format like "1.234,56").

    💡 Step 1: Initialization

    vbaCopyEditretVal = ""
getNumber = 0
european = False

    Rohit clears any previous values and sets the assumption that the format is not European by default.

    🧠 Step 2: Detecting European Format

    vbaCopyEditIf fromThis.Value Like "*.*,*" Then
    european = True
End If

    This checks if the cell contains both a dot and a comma (e.g., "1.234,56"). If yes, it assumes the comma is the decimal point (European format).

    Priya’s vendor from Germany sent "1.250,50 EUR". This line sets european = True.

    🔁 Step 3: Loop Through Each Character

    vbaCopyEditFor i = 1 To Len(fromThis)
    ltr = Mid(fromThis, i, 1)

    The loop reads the text character by character. If the cell has "Amount ₹2,50,000.75", it starts reading "A", "m", "o", etc.

    🔢 Step 4: Build the Numeric Part

    Here’s the logic Rohit uses:

    vbaCopyEditIf IsNumeric(ltr) Then
    retVal = retVal & ltr

    If the character is a digit (0–9), it adds to the final number string.

    Then:

    vbaCopyEditElseIf ltr = "." And (Not european) And Len(retVal) > 0 Then
    retVal = retVal & ltr

    If it’s a . and it’s not European format, it’s added as the decimal point.

    vbaCopyEditElseIf ltr = "," And european And Len(retVal) > 0 Then
    retVal = retVal & "."

    If it’s European format, then the comma , is converted into a dot .—because VBA/Excel understand . as the decimal point.

    So "1.234,56" becomes "1234.56" internally.

    💾 Step 5: Convert the Final String to Number

    vbaCopyEditgetNumber = CDbl(retVal)

    Finally, the retVal string, say "4500.75", is converted into a Double data type using CDbl.

    🛑 Step 6: Error Handling

    vbaCopyEditOn Error GoTo last
...
last:
End Function

    If there’s any weird data or unexpected character that crashes the function, it fails silently and exits.

    📦 Examples: How It Works in Practice

    Cell ContentOutputExplanation
    "Rs. 4,500.75 paid"4500.75Indian format, plain extraction
    "1.234,56 EUR"1234.56European format, comma → dot
    "Amount: ₹2,50,000/-"250000Only digits picked, commas ignored
    "Advance of 7500.00 received"7500.00Straight number pulled out
    "Zero balance"0No digits found, returns 0

    ✅ Where to Use This Function

    Use =getNumber(A2) in any cell, where A2 contains your text with numbers.

    🎁 Bonus Tip from Rohit:

    You can paste this VBA code into your Excel file by pressing:

    1. ALT + F11 → Open VBA editor
    2. Insert > Module
    3. Paste the code
    4. Save as Macro-Enabled Workbook (.xlsm)

    Download Number Extraction VBA Function File

    Get NumbersDownload

    Top rated products

  • Create Multiple Pivot Tables in Excel Automatically Using VBA

    Pivot Tables are one of Excel’s most powerful tools for summarizing data and discovering insights. But if you’re working with large datasets and need multiple Pivot Tables, creating each one manually can be time-consuming and prone to error.

    In this tutorial, we’ll walk through a powerful Excel VBA macro that does all the hard work for you—automatically generating multiple Pivot Tables from your dataset in seconds.

    🧠 What You’ll Learn:

    • How to set up your data source dynamically using VBA
    • How to create multiple Pivot Tables using a single Pivot Cache
    • How to organize, format, and style each Pivot Table
    • How to combine rows, columns, and data fields in advanced Pivot Table design

    🛠 VBA Macro to Insert Multiple Pivot Tables

    Here’s the complete VBA script that automatically creates 8 categorized Pivot Tables plus one detailed summary Pivot Table:

    vbCopyEditSub Insert_Multiple_Pivot_Tables()
    ' Full VBA code here (omitted here for brevity)
End Sub

    The macro performs the following key steps:

    🔄 1. Deletes and Recreates the “PivotTable” Sheet

    Ensures your output is always clean by removing any existing PivotTable sheet and creating a fresh one.

    📌 2. Dynamically Detects the Data Range

    Instead of hardcoding, it uses:

    vbaCopyEditLastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

    This makes your macro adaptable to datasets of varying lengths and widths.

    📦 3. Creates a Single Pivot Cache

    Instead of making a new cache for every Pivot Table (which increases file size), it smartly uses just one:

    vbaCopyEditSet PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange)

    📈 4. Inserts 8 Thematic Pivot Tables:

    Each pivot summarizes a different aspect of the data:

    • Region-wise Total Sales
    • Product-wise Total Sales
    • Payment Mode-wise Sales
    • Delivery Status-wise Units
    • Customer Type-wise Sales
    • Order Priority-wise Units
    • Warranty-wise Units
    • Return Eligibility-wise Units

    Each is formatted with:

    vbaCopyEditpvt.ShowTableStyleRowStripes = True
pvt.TableStyle2 = "PivotStyleDark2"

    📊 5. Adds a Detailed Multi-Dimensional Pivot Table

    At the end of the macro, a detailed sales pivot is generated with:

    • Row Fields: Region and Salesperson
    • Column Field: Product
    • Data Field: Total Sales (formatted as Revenue)

    The code includes:

    vbaCopyEditWith PTable.PivotFields("Total Sales")
    .Orientation = xlDataField
    .Function = xlSum
    .NumberFormat = "#,##0"
    .Name = "Revenue"
End With

    And finally, it auto-adjusts column widths and zooms out to 80% for better readability.

    📂 Download the Excel Macro File

    Automate PivotDownload


    (Make sure to enable macros after opening)

    💡 Why Use VBA for Pivot Tables?

    • ⚡ Speed: Create 8+ Pivot Tables instantly
    • 🔁 Automation: Run it anytime with new data
    • 📦 Efficiency: Uses a single Pivot Cache to reduce file size
    • 🎯 Customization: Easy to modify for different categories or fields

    ✍️ Final Thoughts

    With just a few lines of VBA, you can transform repetitive tasks into powerful automation tools. Pivot Tables offer deep insights—and now, you’ve just automated the whole process!

    Have questions or want to explore more Excel automation? Feel free to connect!

    Get the Free Training App

  • How to count the number of items through Spin Button in Excel?

    You can use a Spin Button in Excel (from the Form Controls) to dynamically count and display items, such as incrementing a value, controlling a formula, or navigating records.

    Here’s a step-by-step guide on how to use a Spin Button to count the number of items in Excel:

    🔢 Goal: Use a Spin Button to Count Items in Excel

    For example, if you want to count how many items are in a list, or simulate a counter that you can increase/decrease with a Spin Button.

    ✅ Step-by-Step Guide

    Step 1: Enable the Developer Tab

    1. Go to File > Options > Customize Ribbon.
    2. Tick Developer on the right side and click OK.

    Step 2: Insert the Spin Button

    1. Go to the Developer tab.
    2. Click Insert under the Controls group.
    3. Under Form Controls, click on the Spin Button (Form Control).
    4. Click and drag on the sheet to place the Spin Button.

    Step 3: Link the Spin Button to a Cell

    1. Right-click the Spin Button and choose Format Control.
    2. Under the Control tab:
      • Current value: 1
      • Minimum value: 1 (or 0 depending on your need)
      • Maximum value: e.g., 100
      • Incremental change: 1
      • Cell link: Choose a cell (say B1)
    3. Click OK.

    Now, every time you click the up/down arrows of the Spin Button, the value in B1 will increase or decrease.

    Step 4: Use That Cell to Count Items

    Let’s say you have a list of items in A2:A100. You want to count how many items are currently visible or considered.

    You can write in another cell (e.g., C1):

    =COUNTA(A2:INDEX(A2:A100,B1))

    This formula will count non-empty cells from A2 to the row determined by the Spin Button’s value in B1.

    🎯 Example Use Cases

    • Count tasks completed (Done list)
    • Browse through list items one by one
    • Display dynamic summaries or progress bars
    • Interactive dashboards

    🎓 Want to Learn More Excel Automation Tricks?

    If you enjoy interactive features like this, you’ll love the full Excel course that covers Form Controls, Data Analysis, Dashboards, VBA, and more:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes hands-on examples
    ✅ Build automation without heavy coding
    ✅ Great for professionals, students, and entrepreneurs

    👉 Click to Enroll Now

    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