Training By Himanshu

Tag: Excel Tips

  • Solve Quadratic Equations in Excel: Step-by-Step with Physics-Based Case Study

    📚 Real-Life Scenario: Ritu’s Physics Assignment

    Ritu, a B.Sc. student in Delhi University, is working on a Physics assignment involving projectile motion. The equation for the height of a projectile is:

    h(t) = -4.9t² + 19.6t + 1

    She needs to find when the object hits the ground — that is, when h(t) = 0.

    This leads to a quadratic equation, and she wants to solve it in Excel quickly using formulas instead of manual calculation.

    🧮 Standard Quadratic Equation Format

    A quadratic equation is always of the form:

    ax² + bx + c = 0

    The formula to find the roots is:

    x = [-b ± √(b² - 4ac)] / 2a

    To find real roots, you must check the discriminant (D):

    D = b² - 4ac
    • If D > 0: Two distinct real roots
    • If D = 0: One repeated real root
    • If D < 0: No real roots (they are imaginary)

    ✅ Step-by-Step: Identify Real Roots in Excel

    Let’s say:

    • a is in cell A1
    • b is in cell B1
    • c is in cell C1

    For example:

    • A1 = -4.9
    • B1 = 19.6
    • C1 = 1

    Step 1: Calculate the Discriminant

    In cell D1:

    =B1^2 - 4*A1*C1

    This gives the discriminant (D)

    Step 2: Check if Roots Are Real

    In cell E1 (interpret the discriminant):

    =IF(D1<0, "No Real Roots", IF(D1=0, "One Real Root", "Two Real Roots"))

    Step 3: Calculate the Real Roots (If Any)

    If D1 is greater than or equal to 0:

    Root 1:

    =(-B1 + SQRT(D1)) / (2*A1)

    Root 2:

    =(-B1 - SQRT(D1)) / (2*A1)

    Wrap these in an IF to avoid errors when roots are imaginary.

    📌 Application Example (Ritu’s Case)

    With:

    • a = -4.9
    • b = 19.6
    • c = 1

    She enters these values into Excel and finds:

    • Discriminant (D):
      19.6² - 4*(-4.9)*1 = 384.16 + 19.6 = 403.76
    • Result:
      Two real roots exist.
    • Root 1 ≈ 4.00 seconds (time when object hits the ground)
    • Root 2 ≈ -0.05 seconds (not valid in real-world time)

    📌 So Ritu uses only the positive root as her real-world answer.

    🎓 Want to Learn More Excel Tricks Like This?

    If you’re solving real-world problems like Ritu or building student projects, you’ll love this step-by-step Excel training:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Learn:

    • Excel formulas & problem-solving
    • Advanced functions for analysis
    • Charts, simulations, and VBA
    • Case studies like finance, physics, and stats

    🎯 Enroll Now & Learn Excel the Smart Way

    Best selling products

  • 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

  • What is the STOCKHISTORY Function?

    The STOCKHISTORY function in Excel 365 allows you to pull historical stock prices (or other financial instruments) directly from Excel’s data service — without needing external plugins or manual downloads.

    📌 Available only in:

    • Microsoft Excel 365 (and Excel for the web)
    • Not available in Excel 2019 or earlier versions

    🔧 Syntax of STOCKHISTORY

    STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property1], [property2], ...)

    📘 Arguments Explained:

    ArgumentDescription
    stockStock ticker symbol or company name. For example: "TCS.NS", "AAPL"
    start_dateThe date to start pulling data from (required).
    end_dateOptional. If omitted, only data from start_date is returned.
    intervalOptional. Frequency of data: 0 (daily), 1 (weekly), 2 (monthly). Default is daily.
    headersOptional. 0 = no header, 1 = headers shown (default), 2 = headers and instrument info.
    property1, property2,...Optional. Choose what data you want (default is Date and Close price). Options include: 0=Date, 1=Close, 2=Open, 3=High, 4=Low, 5=Volume

    ✅ Example Usage

    1. Daily Stock Prices for TCS for 1 Month:

    =STOCKHISTORY("TCS.NS", "2024-05-01", "2024-05-31")

    Returns:

    DateClose
    01-May-243425.5
    02-May-243450.0
    ……

    2. Monthly Closing Price for Apple (AAPL):

    =STOCKHISTORY("AAPL", "2024-01-01", "2024-06-01", 2)

    Here, 2 means monthly interval.

    3. Custom Properties (Open, High, Low, Close, Volume):

    =STOCKHISTORY("RELIANCE.NS", "2024-06-01", "2024-06-10", 0, 1, 2, 3, 4, 1, 5)

    This returns:

    • Date
    • Open
    • High
    • Low
    • Close
    • Volume

    🛑 Notes and Limitations:

    • Requires Internet access.
    • May not work for all international tickers (like small-cap or regional stock exchanges).
    • Ticker symbols for Indian stocks typically end with .NS (for NSE) or .BO (for BSE), e.g., INFY.NS, RELIANCE.BO.
    • Excel might return an error if the ticker or date range is invalid.
    • Weekend and holiday data won’t be shown.

    🧠 Use Cases:

    • Create a dynamic stock price tracker.
    • Build an automated portfolio dashboard.
    • Perform technical analysis on historical prices.
    • Use with charts for visualizing trends.

    🎓 Want to Learn More About Smart Excel Features?

    If you’re interested in mastering powerful Excel tools like STOCKHISTORY, dynamic charts, PivotTables, functions, and automation, check out this highly practical course:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Learn:

    • Advanced Excel functions
    • Real-world automation & dashboards
    • Data analysis tools
    • Bonus: Introduction to VBA & macros

    🎯 Click here to enroll now!

    Top 10 STOCKHISTORY Function Questions and Answers for Excel 365

    ✅ 1. What is the primary use of the STOCKHISTORY function in Excel 365?

    A. Import current stock prices
    B. Retrieve historical stock data
    C. Display live news feed
    D. Generate financial reports automatically

    ✅ Correct Answer: B

    ✅ 2. Which version of Excel supports the STOCKHISTORY function?

    A. Excel 2016
    B. Excel 2019
    C. Excel 365
    D. Excel 2010

    ✅ Correct Answer: C

    ✅ 3. What is the default interval used by STOCKHISTORY if not specified?

    A. Weekly
    B. Monthly
    C. Yearly
    D. Daily

    ✅ Correct Answer: D

    ✅ 4. What does this formula return?

    =STOCKHISTORY("INFY.NS", "2024-05-01", "2024-05-10")

    A. Current price of INFY
    B. Historical closing prices between May 1 and May 10
    C. All financial data of INFY
    D. Only stock volumes for INFY

    ✅ Correct Answer: B

    ✅ 5. Which property number is used to retrieve the “Volume” data in STOCKHISTORY?

    A. 0
    B. 1
    C. 3
    D. 5

    ✅ Correct Answer: D

    ✅ 6. In the formula below, what does the last “2” represent?

    =STOCKHISTORY("AAPL", "2023-01-01", "2023-12-01", 2)

    A. Volume
    B. Monthly interval
    C. Header type
    D. Data column index

    ✅ Correct Answer: B

    ✅ 7. Which of the following is NOT a valid argument for the STOCKHISTORY function?

    A. Stock ticker
    B. Start date
    C. Currency symbol
    D. Property list

    ✅ Correct Answer: C

    ✅ 8. What happens if you try to use STOCKHISTORY in Excel 2019?

    A. It works partially
    B. It shows the latest stock price
    C. It returns an error
    D. It pulls data only for NSE stocks

    ✅ Correct Answer: C

    ✅ 9. What does the header argument “2” do in the STOCKHISTORY function?

    A. Omits column headers
    B. Displays only date and close price
    C. Adds instrument info and column headers
    D. Displays ticker symbol in formula bar

    ✅ Correct Answer: C

    ✅ 10. Which of the following tickers is valid for retrieving Indian stock data via STOCKHISTORY?

    A. “RELIANCE”
    B. “TCS.IN”
    C. “TCS.NS”
    D. “BOM.TCS”

    ✅ Correct Answer: C

    On sale products

  • How to Create a User Defined Function in Excel to Identify Triangle Types

    🧠 Storytime: Why Rohan and Meera Needed a Triangle Function in Excel

    Rohan and Meera are engineering students in Pune. While working on a school project about geometry and architecture, they had to classify different types of triangles based on side lengths.

    They had a long list of side measurements in Excel. Typing the triangle types manually was slow and error-prone.

    Meera asked, “Can’t we just create a formula in Excel that tells us if the triangle is Equilateral, Isosceles, or Scalene?”

    Rohan replied, “Excel has IF and nested conditions, but it’ll get messy. Let’s write a User Defined Function using VBA!”

    Thus began their journey into VBA.

    🔧 What is a User Defined Function (UDF) in Excel?

    A User Defined Function is a custom function written in VBA (Visual Basic for Applications) that works like a built-in Excel formula.

    With a UDF, you can extend Excel’s capabilities beyond standard formulas.

    🧮 Goal: Create a Function to Determine Triangle Type

    Based on the lengths of the three sides, the function should return:

    • Equilateral – All three sides are equal.
    • Isosceles – Any two sides are equal.
    • Scalene – All sides are different.
    • Not a Triangle – The side lengths don’t form a valid triangle.

    🛠 Step-by-Step: Creating the UDF in Excel

    ✅ Step 1: Open the VBA Editor

    1. Press Alt + F11 in Excel.
    2. In the VBA Editor, go to Insert > Module.
    3. A new module window opens.

    ✅ Step 2: Paste the VBA Code

    Function TriangleType(a As Double, b As Double, c As Double) As String
    ' Check if the sides can form a triangle
    If a + b <= c Or a + c <= b Or b + c <= a Then
        TriangleType = "Not a Triangle"
    ElseIf a = b And b = c Then
        TriangleType = "Equilateral"
    ElseIf a = b Or b = c Or a = c Then
        TriangleType = "Isosceles"
    Else
        TriangleType = "Scalene"
    End If
End Function

    ✅ Step 3: Save and Return to Excel

    • Press Ctrl + S and close the VBA Editor.
    • Make sure your file is saved as .xlsm (Macro-enabled workbook).

    📊 Step 4: Use the Function in Excel

    In your worksheet, enter side lengths in three cells (say A2, B2, and C2), and in D2 write:

    =TriangleType(A2, B2, C2)

    ✅ It will return one of:

    • “Equilateral”
    • “Isosceles”
    • “Scalene”
    • “Not a Triangle”

    💡 Example:

    ABCType
    555Equilateral
    668Isosceles
    754Scalene
    123Not a Triangle

    📘 Bonus: Learn More with a Complete Excel Course!

    Just like Rohan and Meera used Excel creatively, you can too!

    📌 If you want to learn Excel from basic to advanced, including formulas, charts, data tools, and VBA, check out:

    🎓 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Learn practical Excel skills
    ✔️ Master formulas, charts, PivotTables, VBA & more
    ✔️ Ideal for students, professionals, entrepreneurs

    👉 Enroll Now and level up your career with Excel mastery.

    On sale products

  • Create Venn Diagrams in Excel Easily – SmartArt & Shape Methods

    Creating a Venn diagram in Excel is possible, though Excel doesn’t have a built-in Venn chart type. However, you can create one manually using Shapes or with the help of SmartArt. Here’s a step-by-step guide for both methods:

    ✅ Method 1: Using SmartArt (Quick and Easy)

    This is ideal for simple, 2- or 3-circle Venn diagrams for concept representation.

    Steps:

    1. Open Excel and go to a blank worksheet.
    2. Click on the Insert tab in the ribbon.
    3. In the Illustrations group, click SmartArt.
    4. In the dialog box, select Relationship from the left panel.
    5. Choose Basic Venn and click OK.
    6. The Venn diagram will appear. You can:
      • Click on each circle to enter text.
      • Use the SmartArt Design tools to format it.
      • Resize, recolor, and reposition circles as needed.

    🟢 Great for presentations, concept explanations, or comparisons.

    ✅ Method 2: Using Shapes (For More Customization)

    This method lets you control the overlap and data-driven appearance.

    Steps:

    1. Go to the Insert tab > Shapes.
    2. Choose the Oval shape.
    3. Draw a circle on the worksheet (hold Shift while dragging for a perfect circle).
    4. Copy and paste the circle to create two or more.
    5. Drag the circles so they overlap like a Venn diagram.
    6. Right-click each circle > Format Shape:
      • Set Transparency (e.g., 30-50%) to make overlaps visible.
      • Choose different Fill Colors for each circle.

    (Optional) Add Text:

    • Insert Text Boxes inside each area of the diagram to represent categories or data points.

    💡 Tip: Use Group (Ctrl+G) to keep the whole diagram together.

    ⚠️ Excel Limitation:

    These methods are visual only—Excel won’t calculate intersections or set logic automatically like specialized tools (e.g., R, Python, or dedicated Venn chart generators).

    If you want to create a data-driven Venn diagram with set values and intersections calculated, you’d need:

    • PowerPoint or Word (with SmartArt)
    • External tools like Lucidchart, Canva, or web-based Venn generators
    • Or, use Excel VBA with shape manipulation (advanced)

    🎓 Want to Learn More Smart Excel Tricks?

    If you’re enjoying these productivity tips in Excel, you’ll love the complete Excel training course:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    📘 Learn:

    • Advanced Excel charts & visualizations
    • Pivot Tables, Formulas, Data Tools
    • Excel Automation with Macros & VBA
    • Real-world projects and case studies

    👉 Perfect for beginners to professionals.
    🎯 Click here to enroll now!

    On sale products

  • How to Quickly Insert Unique Sequence Numbers in Excel

    Whether you’re managing data, creating reports, or organizing lists — inserting unique serial numbers is a common and essential task in Excel. Below are 5 easy methods to do it effectively.

    ✅ 1. Fill Handle (Drag Method) – Best for Short Lists

    Steps:

    1. In A1, type 1; in A2, type 2.
    2. Select both cells.
    3. Drag the fill handle (bottom-right corner) down as far as needed.

    👉 Excel auto-extends the sequence: 3, 4, 5...

    ✅ 2. Fill Series (For Larger Lists)

    Steps:

    1. In A1, type 1.
    2. Go to Home > Editing Group > Fill > Series.
    3. Choose:
      • Columns (or Rows)
      • Step Value: 1
      • Stop Value: (e.g., 1000)
    4. Click OK.

    🚀 Instantly generates hundreds or thousands of sequence numbers!

    ✅ 3. ROW() Formula (Dynamic Numbering)

    Use this when data is added/deleted frequently.

    Formula Example in A2:
    =ROW()-1 (if your data starts at row 2)

    Customize by adjusting based on your starting row.
    e.g., =ROW()-4 if starting from row 5.

    📌 Benefits:

    • Auto-adjusts when you add/remove rows
    • Works well with filters/sorting

    ✅ 4. Power Query (Advanced Users)

    If you’re importing data or cleaning up large datasets:

    1. Load data into Power Query.
    2. Go to Add Column > Index Column > From 1.
    3. Click Close & Load.

    💡 Perfect for automated data workflows.

    ✅ 5. Using VBA (For Automation Lovers)

    If you frequently need sequence numbers, use this macro:

    vbaCopyEditSub AddSerialNumbers()
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        Cells(i, 1).Value = i - 1
    Next i
End Sub

    📌 How to Use:

    1. Press Alt + F11 to open the VBA Editor.
    2. Insert a new Module.
    3. Paste the code.
    4. Run the macro.

    ⚙️ It will insert serial numbers in Column A, starting from row 2.

    🎓 Want to Learn Excel Step-by-Step, From Basics to Advanced?

    If you found these methods helpful, imagine what you can do with structured, hands-on Excel training!

    🔥 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Covers:

    • Excel formulas & functions (VLOOKUP, IF, INDEX/MATCH)
    • Pivot Tables & Charts
    • Data Analysis & Automation
    • VBA Basics
    • Real-world case studies

    📦 100% Self-paced
    📥 Downloadable resources
    💡 Perfect for students, professionals, business owners

    👉 Enroll Now:
    https://trainingbyhimanshu.in/product/mastering-ms-excel-a-comprehensive-training-course-for-excel-proficiency/

    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

  • What Is the Quick Access Toolbar in Excel?

    The Quick Access Toolbar (QAT) is a small, customizable toolbar located above or below the Ribbon in Microsoft Excel. It allows you to add your most-used commands so they’re always easily accessible, no matter which tab you’re on.

    📍 Where to Find It

    By default, you’ll find it at the top-left corner of the Excel window, right above the File tab and Ribbon.

    You can move it below the Ribbon if you prefer.

    🛠️ Why Use the Quick Access Toolbar?

    • Saves time by giving 1-click access to commonly used actions
    • Works in all Excel tabs (you don’t need to switch tabs to find a command)
    • Fully customizable — you can add, remove, or reorder commands

    ✅ How to Customize the Quick Access Toolbar

    🔹 Step 1: Open the Customization Menu

    • Click the downward arrow icon at the right end of the Quick Access Toolbar
    • Or right-click on any command in the Ribbon and choose “Add to Quick Access Toolbar”

    🔹 Step 2: Choose Built-in Commands

    From the dropdown list, you can quickly add:

    • Save
    • Undo
    • Redo
    • Quick Print
    • Email
    • Sort Ascending/Descending
    • More Commands…

    🔹 Step 3: Add More Commands (Advanced)

    1. Click on “More Commands…”
    2. In the Excel Options window that opens:
      • On the left side: Choose commands to add
      • On the right side: See your current toolbar items
    3. You can choose from:
      • Popular Commands
      • Commands Not in the Ribbon
      • All Commands
      • Macros (if you have any)
    4. Select a command and click Add >>
    5. Click OK to apply

    🔄 Move or Reset the Toolbar

    🔁 Move Below the Ribbon:

    • Click the dropdown arrow → Show Below the Ribbon

    🔁 Reset to Default:

    • Right-click on the toolbar → Reset Quick Access Toolbar

    🎯 Practical Examples

    Example 1: Add “Sort A to Z” Button

    • Go to the Data tab → Right-click “Sort A to Z” → Choose “Add to Quick Access Toolbar”

    Example 2: Add a Macro Button

    • Customize → Choose “Macros” from dropdown → Select your macro → Add it
    • Optionally, change the icon or name for clarity

    🔐 Tip: Use Keyboard Shortcuts with QAT

    Each Quick Access Toolbar command gets a keyboard shortcut like:

    Alt + 1, Alt + 2, etc.
    (depending on the position of the item in the toolbar)

    So if “Save” is the first item, you can press Alt + 1 to save instantly.

    📝 Summary

    FeatureBenefit
    Add Custom Commands1-click access to frequent tools
    Always VisibleNo need to switch Ribbon tabs
    Keyboard FriendlyUse Alt + Number shortcuts
    Supports MacrosAdd your own automated tasks

    💡 Pro Tip

    You can export your Quick Access Toolbar settings and import them on another PC. This is useful if you use Excel across devices or in teams.

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

  • 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

  • Understanding Autofill Series and Justify Option in Excel with Examples

    Understanding Autofill Series and Justify Option in Excel with Examples

    ✅ Autofill Series in Excel

    🔍 What is Autofill?

    Autofill is a feature in Excel that allows users to automatically fill cells with data that follows a pattern or series, such as numbers, dates, days, months, or even custom lists.

    🔹 How to Use Autofill:

    1. Type the starting value in a cell.
    2. Drag the fill handle (small square at the bottom-right of the cell) across or down to fill other cells.
    3. Excel detects the pattern and fills accordingly.

    🔄 Common Series You Can Autofill:

    TypeExample InputAutofill Result
    Numbers1, 21, 2, 3, 4, …
    Dates1-Jan1-Jan, 2-Jan, 3-Jan, …
    DaysMondayMonday, Tuesday, …
    MonthsJanJan, Feb, Mar, …
    Text + NumbersItem1Item1, Item2, …

    🛠️ Customizing Series:

    • Go to Home > Fill > Series for more control.
    • Options: Linear, Growth, Date, AutoFill, etc.

    ✅ Example 1: Linear Series

    • Type 2 in A1, then 4 in A2.
    • Select A1:A2 and drag down.
    • Excel will fill: 2, 4, 6, 8, 10…

    ✅ Example 2: Days of the Week

    • Type Monday in A1, drag down.
    • Excel fills: Monday, Tuesday, Wednesday…

    ✅ Example 3: Custom List

    • Go to File > Options > Advanced > Edit Custom Lists
    • Add a custom list like: “Bronze, Silver, Gold, Platinum”
    • Now you can Autofill this sequence.

    ✅ Justify Option in Excel

    🔍 What is Justify?

    The Justify feature in Excel is used to realign and reflow long text entries across multiple rows so that it fits within a specified column width.

    🔹 How to Use Justify:

    1. Type a long sentence or paragraph in one cell.
    2. Select a range of empty cells in a single column (vertical).
    3. Go to Home > Fill > Justify.

    Excel breaks the text and distributes it across the selected rows, wrapping the words neatly.

    📌 Important Notes:

    • Works only with text in one column.
    • The column must be wide enough, and the destination cells must be empty.
    • It doesn’t wrap inside a cell but spreads across multiple cells vertically.

    ✅ Example:

    Let’s say A1 contains:

    "Excel Justify option is useful for breaking long text into multiple lines within one column."

    Select A1:A4 → Go to Home > Fill > Justify.

    Result:

    A1: Excel Justify option is
A2: useful for breaking long
A3: text into multiple lines
A4: within one column.

    This is useful for cleaning up or displaying long data entries in a more readable format.

    🧠 Summary:

    FeaturePurposeExample Use Case
    AutofillFill cells automatically in a patternFill dates, numbers, or custom lists
    JustifyReflow long text across rows in one columnCleanly break long text into readable parts

    Watch the Video for Autofill Series and Justify options

    On sale products