Training By Himanshu

Tag: Excel for Beginners

  • ✅ How to Remove Line Breaks in Excel (Step-by-Step)

    Line breaks (also called carriage returns or newlines) often sneak into Excel cells when you’re copying from Word, web pages, or using Alt+Enter to start a new line inside a cell.

    These can mess up formulas, formatting, and data exports.

    🧹 Method 1: Use Find and Replace (Quickest Way)

    🔹 Steps:

    1. Select the range of cells (or entire sheet).
    2. Press Ctrl + H to open Find and Replace.
    3. In Find what, hold Ctrl and press J.
      (This inserts a line break — you won’t see anything, but it’s there.)
    4. In Replace with, type a space or nothing (if you want to delete the line break).
    5. Click Replace All.

    ✅ Done! All line breaks will be removed or replaced.

    🧠 Tip:

    Use a space in “Replace with” if you want to separate words, else words may merge.

    Before:
    Amit\nSharma → Looks like:

    Amit  
Sharma

    After (Replace with space):
    Amit Sharma

    🧮 Method 2: Use a Formula

    You can also remove line breaks using a formula with the SUBSTITUTE function.

    🧪 Formula:

    =SUBSTITUTE(A1, CHAR(10), " ")
    • CHAR(10) is the line break character (LF = Line Feed).
    • Replace " " with "" if you want to remove the break without adding space.

    Then copy-paste as values if needed.

    🔁 Method 3: Power Query (For Advanced Users)

    If you’re working with imported datasets:

    1. Go to Data → Get & Transform → From Table/Range
    2. In Power Query Editor, select the column
    3. Use Transform → Replace Values
    4. Replace line break: enter Ctrl + J in “Value to Find”
    5. Replace with a space or empty string
    6. Click Close & Load

    📌 Bonus: Removing Line Breaks in Google Sheets?

    Use:

    =SUBSTITUTE(A1, CHAR(10), " ")

    Or:

    =REGEXREPLACE(A1, "\n", " ")

    Top rated products

  • How to Calculate Standard Error of the Mean (SEM) in Excel

    👨‍💼 Meet Rahul – The Interview Story

    Rahul, a recent graduate from Delhi, walks confidently into an Excel data analyst interview at a top MNC. He’s aced formulas like VLOOKUP, IF, and PivotTables.

    But then, the interviewer leans in and asks:

    “Rahul, how do you calculate the Standard Error of the Mean in Excel?”

    Rahul freezes. ❄️
    He remembers hearing about it in statistics class, but Excel? No idea.

    He stammers, “Umm… maybe with AVERAGE()?”

    The interviewer smiles politely and moves on.

    Rahul didn’t get the job.
    But that day, he made a promise to himself — “I’ll never be unprepared again.”

    📚 What is Standard Error of the Mean (SEM)?

    The Standard Error of the Mean (SEM) tells you how much the sample mean (average) is likely to vary from the true population mean.

    🧮 Formula: SEM=Standard Deviationn\text{SEM} = \frac{\text{Standard Deviation}}{\sqrt{n}}SEM=n​Standard Deviation​

    Where:

    • Standard Deviation = spread of the data
    • n = sample size

    ✅ How to Calculate SEM in Excel

    Rahul opens Excel and tries it himself with a dataset of student scores:

    A (Scores)
    80
    85
    90
    88
    92

    🔹 Step 1: Calculate Standard Deviation

    Use:

    excelCopyEdit=STDEV.S(A2:A6)

    This gives the sample standard deviation.

    🔹 Step 2: Count the Sample Size

    excelCopyEdit=COUNT(A2:A6)

    Returns 5 in this case.

    🔹 Step 3: Combine to Calculate SEM

    excelCopyEdit=STDEV.S(A2:A6)/SQRT(COUNT(A2:A6))

    ✅ This is the formula to get Standard Error of the Mean.

    📊 Example Result:

    For the above scores:

    • Standard Deviation ≈ 4.38
    • Count = 5
    • SEM = 4.38 / √5 ≈ 1.96

    🧠 Rahul’s Takeaway

    Next interview, Rahul walks in, confident and ready. When asked again:

    “What’s the SEM in Excel?”

    He smiles and says:

    excelCopyEdit=STDEV.S(range)/SQRT(COUNT(range))

    And this time?
    💼 He gets the job.

    📣 Learn More with Practical Excel

    🎓 Join the Mastering MS Excel Course
    From statistics to automation — learn Excel the practical way, just like Rahul.

    Top rated products

  • How to Add Quotes Around Numbers or Text in Excel

    🎥 The Problem Begins…

    Meet Aman, a data analyst at a film production house in Mumbai. One fine Monday morning, his boss (let’s call him Kabir, the no-nonsense producer from War) walks in and says:

    “Aman, I need this actor list uploaded to our website, but make sure every name is in double quotes — our software won’t process it otherwise!”

    Aman opens Excel and sees this:

    Actor Name
    Shah Rukh Khan
    Deepika Padukone
    Ranbir Kapoor

    But he needs it to look like this:

    Actor Name (Quoted)
    “Shah Rukh Khan”
    “Deepika Padukone”
    “Ranbir Kapoor”

    😰 Aman panics for a moment… but then remembers his Excel skills💪

    🎯 When Do You Need to Add Quotes?

    You might need quotes:

    • When exporting data for CSV/JSON formats.
    • When uploading content to websites or software tools.
    • When writing formulas or generating coded strings.
    • When automating SMS or WhatsApp messages.

    ✅ Method 1: Using Concatenation Formula

    You can use & to join quotes and cell contents:

    ="""" & A2 & """"

    🔍 Breakdown:

    • """" → represents one actual ".
    • A2 → your text or number.
    • Final result: “Shah Rukh Khan”

    ✅ Method 2: Using CONCAT or TEXTJOIN

    If you prefer function-based formulas:

    =CONCAT("""", A2, """")

    or

    =TEXTJOIN("", TRUE, """", A2, """")

    ✅ Method 3: Apply Quotes to a Range in Bulk

    If you want to process an entire range:

    1. Create a helper column with the formula.
    2. Drag down.
    3. Copy → Paste as Values.
    4. Use “Find & Replace” if needed to remove or adjust quotes.

    ✅ Example with Numbers

    Let’s say Salman Khan’s movies have these budgets:

    Budget (in Cr)
    200
    150
    300

    You want:

    Quoted Budget
    “200”
    “150”
    “300”

    Use the same formula:

    ="""" & A2 & """"

    Yes, it works for text, numbers, dates — anything.

    🔥 Bonus: Single Quotes Instead of Double

    Want single quotes (')?

    ="'" & A2 & "'"

    📣 Want to Learn More Excel Magic?

    🎓 Join Mastering MS Excel Course
    Learn data cleaning, formula tricks, automation, and real-life use cases like this — with Indian examples and business logic.

    🎬 The Ending?

    Aman sends the file in 2 minutes.
    Kabir looks at it, nods, and says…

    “Mission accomplished, Mr. Excel!”

    Roll credits. 🎞️

    Top rated products

  • How to Convert Time to Decimal Hours, Minutes, and Seconds in Excel

    ⏱️ How to Convert Time to Decimal Hours, Minutes, or Seconds in Excel – Full Detailed Guide

    In Excel, time values are stored as fractions of a day. To use these time values in calculations, billing, payroll, or analysis, you often need to convert time into decimal hours, minutes, or seconds.

    ✅ Understanding Excel Time

    • 1 full day = 1
    • 12:00 PM (half day) = 0.5
    • 6:00 AM = 0.25

    So, when you enter 2:30 (2 hours 30 mins), Excel stores it as:
    2.5 hours ÷ 24 = 0.104166667

    🔄 Convert Time to Decimal Hours, Minutes, Seconds

    Assume your time value is in cell A2

    Time (A2)
    2:30

    🔹 1. Convert Time to Decimal Hours

    Formula:

    =A2*24

    Explanation:

    • Excel stores time as a fraction of 24 hours, so multiplying by 24 gives the time in decimal hours.

    Result: 2.5

    🔹 2. Convert Time to Decimal Minutes

    Formula:

    =A2*24*60

    Or simply:

    =A2*1440

    Result: 150 minutes

    🔹 3. Convert Time to Decimal Seconds

    Formula:

    =A2*24*60*60

    Or simply:

    =A2*86400

    Result: 9000 seconds

    🧠 Pro Tip: Handle Time > 24 Hours

    If time exceeds 24 hours (e.g., 28:15), make sure to:

    1. Format the time cell (A2):
      • Right-click → Format Cells → Custom → Type: [h]:mm
    2. Use the same formulas as above; Excel will still calculate correctly.

    📌 Real-World Use Cases

    Use CaseConvert To
    Employee shift logsDecimal hours
    Call duration logsMinutes/seconds
    Billing hoursDecimal hours
    Machine run-timeHours/minutes

    📣 Promote Your Excel Course

    Learn more time-saving tricks and Excel automation skills in:

    🎓 Mastering MS Excel – A Comprehensive Course
    Includes billing automation, payroll calculations, and time analytics examples.

    Top rated products

  • How to Add Country or Area Code to Phone Numbers in Excel

    📞 How to Add Country/Area Code to a Phone Number List in Excel – With Example

    Adding a country code or area code to phone numbers in Excel is a common task in data cleaning and formatting. It’s especially useful when preparing lists for international communication, WhatsApp campaigns, or CRM uploads.

    ✅ Example Scenario: Add +91 Country Code to Indian Mobile Numbers

    Let’s say you have a list of mobile numbers in Column A (without country code):

    A (Mobile No.)
    9876543210
    9123456789
    9988776655

    Your goal is to add +91 before each number.

    🔹 Method 1: Using Formula

    Use the CONCATENATE or & operator:

    ="+91" & A2

    Or:

    =CONCAT("+91", A2)

    Result:

    B (With +91)
    +919876543210
    +919123456789
    +919988776655

    ➡️ Drag the formula down to apply it to all rows.

    🔹 Method 2: For Area Codes (e.g., Delhi’s Landline ‘011’)

    If you have landline numbers and want to prefix them with area code:

    A (Landline)
    23456789
    87654321

    Formula:

    ="011" & A2

    Result:

    B (With Area Code)
    01123456789
    01187654321

    🔹 Method 3: Add Country Code Only If Missing (Advanced)

    Use IF to avoid adding code to already-formatted numbers:

    =IF(LEFT(A2, 3)="+91", A2, "+91" & A2)

    This checks if +91 already exists and avoids duplication.

    🔒 Important Notes:

    • Excel treats numbers starting with + as text. No need to format them as numbers.
    • Format the column as Text before pasting or using the formula to prevent Excel from removing leading zeroes or the +.

    📣 Promote Your Excel Course

    Want to learn more data cleaning tricks like this?

    🎓 Join the Excel Mastery Course
    Learn with real-world examples, Indian data sets, and career-focused Excel training.

    Top rated products

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

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

    🧠 Function Syntax:

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

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

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

    📄 Sheet: Delhi

    NameProductSales
    RajeshLaptop55000
    AnjaliPhone30000

    📄 Sheet: Mumbai

    NameProductSales
    VikramTablet20000
    NehaPhone25000

    📄 Sheet: Kolkata

    NameProductSales
    ArjunLaptop60000
    PriyaPhone28000

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

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

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

    📌 With Header Row Included

    If you also want the headers, you can do:

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

    This adds a custom header at the top.

    🎯 Tips for Real-World Use

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

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

    📣 Promote Your Excel Skills

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

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

    Top rated products

  • How to Highlight Odd or Even Numbers in Excel Using Conditional Formatting

    To highlight odd or even numbers in Excel, you can use Conditional Formatting with a formula. Here’s how:

    ✅ Steps to Highlight Odd Numbers:

    1. Select the range of cells you want to check.
    2. Go to the Home tab → click Conditional Formatting → choose New Rule.
    3. Select “Use a formula to determine which cells to format”.
    4. Enter the formula: =ISEVEN(A1)=FALSE (Replace A1 with the top-left cell of your selection.)
    5. Click Format, choose a color (e.g., light green), and press OK.

    ✅ Steps to Highlight Even Numbers:

    Follow the same steps, but use this formula:

    =ISEVEN(A1)=TRUE

    🧠 Explanation:

    • ISEVEN(number) returns TRUE if a number is even.
    • ISODD(number) returns TRUE if a number is odd.
    • Conditional formatting applies the format when the formula returns TRUE.

    You can use ISODD(A1) instead of ISEVEN(A1)=FALSE if you prefer.

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

  • 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

  • Excel Filter Option: Detailed Explanation with Examples

    Excel Filter Option: Detailed Explanation with Examples

    The Filter option in Excel is used to view specific rows in a dataset while hiding the rest, based on criteria you set. It’s especially useful when working with large data sets and you need to focus on certain types of data without deleting or moving anything.

    ✅ How to Apply a Filter in Excel

    1. Select the data range (including headers).
    2. Go to the Home tab or Data tab.
    3. Click on Filter (you’ll see small dropdown arrows appear in the header row).
    4. Click on the dropdown arrow in the column you want to filter.
    5. Choose:
      • Specific values to show
      • Text, Number, or Date filters (e.g., “Contains”, “Greater Than”, “Before”, etc.)

    🔍 Example 1: Filtering Text Data

    NameDepartmentCity
    AnjaliSalesMumbai
    RaviHRDelhi
    MeenaSalesMumbai
    SureshFinancePune
    NehaHRMumbai

    Task: Show only employees from the Sales department.

    Steps:

    • Apply Filter
    • Click on the dropdown in the Department column
    • Select Sales

    Result:

    NameDepartmentCity
    AnjaliSalesMumbai
    MeenaSalesMumbai

    🔢 Example 2: Filtering Numbers

    ProductUnits Sold
    A120
    B80
    C150
    D95

    Task: Show products that sold more than 100 units.

    Steps:

    • Apply Filter
    • Click on dropdown in Units Sold
    • Choose Number Filters > Greater Than > 100

    Result:

    ProductUnits Sold
    A120
    C150

    📅 Example 3: Filtering Dates

    NameJoining Date
    Aman01-Jan-2023
    Pooja15-Feb-2023
    Nikhil20-Jan-2022
    Kiran01-Apr-2023

    Task: Show people who joined in 2023.

    Steps:

    • Apply Filter
    • Click on dropdown in Joining Date
    • Choose Date Filters > After > 31-Dec-2022

    🧠 Real-Life Scenarios Where Filter is Useful

    ✅ 1. HR/Employee Records

    • Filter employees by department, city, date of joining, or performance rating.

    ✅ 2. Sales & Inventory

    • View products with stock less than a threshold.
    • Analyze sales from specific regions or sales reps.

    ✅ 3. Finance

    • Filter transactions above or below a specific amount.
    • Show only “Pending” or “Approved” expenses.

    ✅ 4. School/College Data

    • Show students from a particular grade/class.
    • Filter students who scored above 90 marks.

    ✅ 5. Customer Database

    • Target customers from a specific city or purchase history.

    💡 Bonus Tips

    • Clear Filter: Use “Clear Filter” option to remove applied filters.
    • Filter Multiple Columns: You can apply filters to multiple columns at once.
    • Use Custom Filters: Combine conditions like “greater than 100” AND “less than 200”.
    • Shortcut: Press Ctrl + Shift + L to toggle filters on or off.

    Here is your sample Excel file with filter examples

    Excel_Filter_ExamplesDownload

    Watch the Video to learn Filter

    On sale products

  • Excel Practical Practice Test

    Excel Practical Practice Test

    MS Excel Online Practice Test

    Test your Microsoft Excel skills with this free online practice test designed to assess your knowledge and practical abilities. Whether you’re a beginner or an experienced user, this quiz will challenge your understanding of formulas, functions, data handling, formatting, and more.

    ✅ Covers real-world Excel tasks
    ✅ Immediate feedback on answers
    ✅ Great for students, job seekers, and professionals
    ✅ No installation required – 100% online

    Take the test now and discover how well you know Excel! Perfect for self-evaluation, interview preparation, or brushing up on essential Excel skills.

    1 / 19

    What is the purpose of the “Define Name” feature in Excel?

    2 / 19

    After applying a filter, how can you tell if a column is being filtered?

    3 / 19

    What is the primary use of the Filter feature in Excel?

    4 / 19

    You’ve created a Pivot Table showing total sales by product. You only want to view sales for the East and West regions. What should you do?

    5 / 19

    You have sales data with columns: “Region”, “Product”, and “Sales Amount”. You want to see the total sales for each region. What should you do in a Pivot Table?

    6 / 19

    Which chart type is best suited to compare parts of a whole, such as market share?

    7 / 19

    How can you print only a specific part of your worksheet in Excel?

    8 / 19

    Which of the following combinations is often used as a more flexible alternative to VLOOKUP?

    9 / 19

    You have a table of employee data in range A2:D10. Column A contains Employee IDs, and Column C contains Salaries. What will the formula =VLOOKUP(104, A2:D10, 3, FALSE) return?

    10 / 19

    What does the Scenario Manager feature help you do?

    11 / 19

    Which of the following is the correct syntax of the PMT function?

    12 / 19

    What does =COUNTIF(A1:A10, “Ap*”) mean?

    13 / 19

    How many cells it will count

    =COUNTIF(A1:A5, “*book*”)

    A1:A5 contains: “book”, “notebook”, “pen”, “Booklet”, “paper”?

    14 / 19

    What does the formula =IF(A1=”Yes”, 1, 0) return if A1 contains the word “Yes”?

    15 / 19

    Which formula correctly uses the AND function within an IF?

    16 / 19

    What does the IF function return when the logical test is FALSE?

    17 / 19

    What does the HYPERLINK function do in Excel?

    18 / 19

    In a list of student scores in B2:B20, you want to highlight scores above 90. Which conditional formatting rule should you use?

    19 / 19

    What does the formula =SUMIF(A1:A10, “>100”) do?

    Your score is

    The average score is 42%

    0%

  • Mastering VLOOKUP and HLOOKUP in Excel: A Complete Guide with Examples

    Mastering VLOOKUP and HLOOKUP in Excel: A Complete Guide with Examples

    ✅ What is VLOOKUP in Excel?

    VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a value in the same row from another column.

    Syntax:

    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    Arguments:

    • lookup_value: The value to search for.
    • table_array: The table range to search within.
    • col_index_num: The column number in the table from which to retrieve the value.
    • range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

    ✅ VLOOKUP Example:

    Imagine this table in range A2:C6:

    Employee IDNameDepartment
    101RajHR
    102SimranIT
    103AmanMarketing
    104PreetiFinance
    105RameshAdmin

    🔍 Goal: Find the Department of Employee ID 103.

    🧮 Formula:

    =VLOOKUP(103, A2:C6, 3, FALSE)

    ✅ Output:

    Marketing

    💡Why? VLOOKUP searched for 103 in column A, found it in row 4, then returned the value in the 3rd column of that row (C4).

    ✅ What is HLOOKUP in Excel?

    HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a value in the same column from another row.

    Syntax:

    HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

    Arguments:

    • lookup_value: The value to find in the first row.
    • table_array: The range that contains the data.
    • row_index_num: The row number in the table from which to return a value.
    • range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

    ✅ HLOOKUP Example:

    Imagine this table in range A1:F3:

    ID101102103104105
    NameRajSimranAmanPreetiRamesh
    DeptHRITMarketingFinanceAdmin

    🔍 Goal: Find the Name of Employee ID 104.

    🧮 Formula:

    =HLOOKUP(104, A1:F3, 2, FALSE)

    ✅ Output:

    Preeti

    💡Why? HLOOKUP searched for 104 in row 1, found it in column E, and returned the value in the 2nd row of that column (E2).

    🆚 Key Differences: VLOOKUP vs HLOOKUP

    FeatureVLOOKUPHLOOKUP
    OrientationVertical (columns)Horizontal (rows)
    Lookup inFirst columnFirst row
    Output fromA specified columnA specified row
    Use caseWhen data is arranged verticallyWhen data is arranged horizontally

    🔄 Tips:

    • Use FALSE in range_lookup to ensure exact matches.
    • Use named ranges or TABLES for dynamic data.
    • VLOOKUP cannot look left. Use INDEX-MATCH for more flexibility.

    🔹 Job Interview Questions on VLOOKUP & HLOOKUP

    ✅ Basic Level

    1. What is the difference between VLOOKUP and HLOOKUP in Excel?
      (Expected: VLOOKUP searches vertically, HLOOKUP searches horizontally.)
    2. What does the col_index_num in VLOOKUP do?
      (Expected: It specifies the column number from which the value is returned.)
    3. What happens if range_lookup is set to TRUE vs FALSE in VLOOKUP/HLOOKUP?
      (Expected: TRUE gives approximate match, FALSE gives exact match.)
    4. Can VLOOKUP return values to the left of the lookup column? Why or why not?
      (Expected: No, because VLOOKUP can only return values from columns to the right.)
    5. Write a VLOOKUP formula to fetch the salary of Employee ID 102 from a given table.
      (Expect the candidate to form a valid VLOOKUP formula based on assumed columns.)

    ✅ Intermediate Level

    1. What error do you get if VLOOKUP cannot find the lookup value? How do you handle it?
      (Expected: #N/A error. Use IFERROR or IFNA to handle it gracefully.)
    2. What are the limitations of VLOOKUP, and how can they be overcome?
      (Expected: Can’t search left, slower in large datasets; can use INDEX-MATCH instead.)
    3. When would you prefer HLOOKUP over VLOOKUP? Give a practical example.
      (Expected: When data is structured in rows instead of columns — e.g., monthly sales in a horizontal table.)

    ✅ Advanced Level

    1. How would you dynamically look up data when the column index keeps changing?
      (Expected: Use MATCH() inside VLOOKUP or switch to INDEX-MATCH.) Example: =VLOOKUP("Product A", A1:D10, MATCH("Price", A1:D1, 0), FALSE)
    2. Can you perform a case-sensitive lookup using VLOOKUP or HLOOKUP?
      (Expected: No, they are not case-sensitive. Use INDEX, MATCH, EXACT, or array formulas for case-sensitive search.)

    Here’s your Excel practice file for VLOOKUP and HLOOKUP, complete with data and instructions:

    VLOOKUP_HLOOKUP_PracticeDownload

    📘 Contents:

    • VLOOKUP_Data: A vertical table to practice VLOOKUP.
    • HLOOKUP_Data: A horizontal table to practice HLOOKUP.
    • Instructions: A guide on how to use the file for practice.

    Watch the Video on Vlookup and Hlookup

    Related Products