Training By Himanshu

Tag: Excel Formulas

  • Top 10 Ways to Clean Data in Excel Easily (With Examples)

    Data cleaning is crucial when working with large datasets in Excel. Raw data often contains errors like extra spaces, duplicates, inconsistent formatting, or missing values. Cleaning data ensures accurate analysis, professional reports, and better decision-making. Here’s a step-by-step guide to the top 10 ways to clean data in Excel with real examples.

    1. Remove Extra Spaces with TRIM Function

    Extra spaces often appear when importing data from other sources. These spaces can cause formulas to fail or make data look inconsistent.

    How to Apply:

    1. Suppose cell A1 contains " John Doe " (with spaces at start and end).
    2. Use the formula: =TRIM(A1)
    3. Excel removes all leading, trailing, and extra spaces between words.

    Example:

    OriginalCleaned
    ” John Doe ““John Doe”

    2. Convert Text to Numbers

    Sometimes numeric values are stored as text, which can break calculations.

    How to Apply:

    1. Suppose cell B1 has "100" stored as text.
    2. Use the formula: =VALUE(B1)
    3. Excel converts text to a number that can be used in calculations.

    Example:

    OriginalConverted
    “100”100

    Alternative: Select the column → Click Data > Text to Columns → Finish. This also converts text numbers into actual numbers.

    3. Remove Duplicates

    Duplicate entries can skew analysis and reports.

    How to Apply:

    1. Select the dataset.
    2. Go to Data → Remove Duplicates.
    3. Choose the columns to check duplicates.
    4. Click OK.

    Example:

    NameCity
    John DoeDelhi
    Jane SmithMumbai
    John DoeDelhi

    ✅ Now only unique entries remain.

    4. Use Find and Replace for Bulk Changes

    Correct common errors or format data quickly.

    How to Apply:

    1. Press Ctrl + H.
    2. In Find What, type the incorrect data (e.g., “Indai”).
    3. In Replace With, type the correct data (e.g., “India”).
    4. Click Replace All.

    Example:

    OriginalCorrected
    IndaiIndia

    This method also works for symbols, extra characters, or formatting changes.

    5. Standardize Text Case (PROPER, UPPER, LOWER)

    Inconsistent capitalization can make data look unprofessional.

    Formulas:

    • =PROPER(A1) → Capitalizes first letter of each word.
    • =UPPER(A1) → Converts to uppercase.
    • =LOWER(A1) → Converts to lowercase.

    Example:

    OriginalProper CaseUpper CaseLower Case
    john doeJohn DoeJOHN DOEjohn doe

    6. Handle Missing Data

    Missing values can affect calculations and charts.

    Methods:

    1. Replace with 0: =IF(A1="","0",A1)
    2. Replace with average: =IF(A1="",AVERAGE($A$1:$A$100),A1)

    Example:

    ValueCleaned
    100100
    0

    7. Text-to-Columns for Splitting Data

    Useful when multiple values are in a single column (e.g., Name, City, State).

    How to Apply:

    1. Select the column.
    2. Go to Data → Text to Columns.
    3. Choose Delimited → Select delimiter (comma, space, etc.).
    4. Click Finish.

    Example:

    OriginalNameCityState
    John Doe, Delhi, DLJohn DoeDelhiDL

    8. Use SUBSTITUTE for Text Errors

    Replace unwanted characters, symbols, or words automatically.

    Formula:

    =SUBSTITUTE(A1,"-","")

    Example:

    OriginalCleaned
    123-456-78901234567890

    9. Use Flash Fill for Quick Formatting

    Automatically fills a column based on the pattern you provide.

    How to Apply:

    1. Type the desired output in one cell.
    2. Press Ctrl + E to auto-fill the rest.

    Example:

    OriginalFirst Name
    John DoeJohn
    Jane SmithJane

    ✅ Flash Fill extracts first names automatically.

    10. Data Validation to Prevent Future Errors

    Prevent users from entering invalid data in a column.

    How to Apply:

    1. Select the column.
    2. Go to Data → Data Validation.
    3. Set criteria (e.g., numbers between 1–100, date range, dropdown list).

    Example:

    • Prevents typing letters in a numeric score column.
    • Creates dropdown menus for cities or product categories.

    Conclusion

    Cleaning data in Excel is essential for accurate reporting, analysis, and decision-making. By mastering these 10 methods—TRIM, Remove Duplicates, Flash Fill, Data Validation, and more—you can save time and avoid errors.

    ✅ Pro Tip: Combine methods like TRIM + Remove Duplicates + Data Validation for maximum efficiency.

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

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

    100 Excel Shortcuts with Explanations

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

    🔹 Basic Shortcuts

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

    🔹 Navigation Shortcuts

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

    🔹 Data Entry & Editing

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

    🔹 Formatting Shortcuts

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

    🔹 Selection Shortcuts

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

    🔹 Formula Shortcuts

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

    🔹 Worksheet Shortcuts

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

    🔹 Advanced & Miscellaneous

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

    🔹 Time-Saving Favorites

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

    🔹 Final 10 Power Shortcuts

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

    ✅ Final Tip

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

    Subscribe to our newsletter!

    [newsletter_form type=”minimal”]

    Featured products

  • Top 25 Excel Formulas Every Accountant Should Know (With Clear Examples)

    In today’s business world, accountants rely heavily on Microsoft Excel to manage financial data, prepare reports, and analyze numbers quickly. While anyone can enter data into Excel, mastering the right formulas is what makes an accountant truly efficient and accurate. From simple calculations like SUM and AVERAGE to advanced ones like VLOOKUP, IF, and INDEX-MATCH, these formulas save time, reduce errors, and improve decision-making.

    In this guide, we’ll explore the Top 25 Excel formulas every accountant must know, along with practical examples to help you apply them in real-life accounting tasks.

    Below, I use a simple sample table called Transactions (Excel Table) with columns:
    Date | Voucher | Account | Customer | Amount | Tax | Status | Salesperson

    Tip: Turn your data into a Table with Ctrl + T and use structured references (e.g., Transactions[Amount]).

    Employee_Sample_DataDownload

    1) SUM

    What it does: Adds numbers.

    =SUM(Transactions[Amount])

    Quickly totals all amounts.

    2) SUMIFS

    What it does: Sum with multiple conditions (e.g., date range + account).

    =SUMIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Date], ">="&DATE(2025,4,1), Transactions[Date], "<="&DATE(2025,6,30))

    Use case: Q1 sales only; or sum by customer & status.

    3) COUNTIFS

    What it does: Counts rows meeting multiple criteria.

    =COUNTIFS(Transactions[Status], "Paid", Transactions[Account], "Sales")

    How many paid sales invoices?

    4) AVERAGEIFS

    What it does: Average with multiple criteria.

    =AVERAGEIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Status], "Paid")

    Average paid invoice value.

    5) IF

    What it does: Logical test → value if true/false.

    =IF([@Status]="Overdue","Follow-up","OK")

    Flags overdue invoices.

    6) IFS

    What it does: Chain multiple conditions neatly.

    =IFS([@Amount]>=100000,"High",[@Amount]>=25000,"Medium",TRUE,"Low")

    7) IFERROR

    What it does: Handles errors gracefully.

    =IFERROR([@[Amount]]/[@[Tax]],0)

    Avoids #DIV/0! when tax is zero.

    8) XLOOKUP

    What it does: Modern, flexible lookup (left/right, exact by default).

    =XLOOKUP("CUST-007", Customers[CustID], Customers[GSTIN], "Not found")

    Also return multiple columns by selecting a multi-column return array.

    9) VLOOKUP (Legacy but common)

    What it does: Vertical lookup (be careful with column index).

    =VLOOKUP("CUST-007", Customers!A:H, 5, FALSE)

    Prefer XLOOKUP where available.

    10) INDEX + MATCH

    What it does: Powerful two-step lookup (works leftward; great for 2D lookups).

    =INDEX(Rates[Rate], MATCH([@Account], Rates[Account], 0))

    Two-way example (row & column):

    =INDEX(PivotArea, MATCH("Sales", RowLabels, 0), MATCH("Apr-2025", ColLabels, 0))

    11) SUMPRODUCT

    What it does: Conditional math without helper columns; weighted averages.

    =SUMPRODUCT((Transactions[Account]="Sales")*(Transactions[Status]="Paid")*Transactions[Amount])

    Weighted average tax rate:

    =SUMPRODUCT(Transactions[Amount], Transactions[Tax]) / SUM(Transactions[Amount])

    12) ROUND, ROUNDUP, ROUNDDOWN

    What they do: Control rounding for reports, invoices, GST.

    =ROUND([@Amount]*1.18, 0)      // nearest rupee
=ROUNDUP([@Amount]*1.18, 0)    // always up
=ROUNDDOWN([@Amount]*1.18, 0)  // always down

    13) ABS

    What it does: Absolute value—useful for variance and adjustments.

    =ABS([@Amount]-[@Budget])

    14) EOMONTH & EDATE

    What they do: Month math—closing, aging buckets.

    =EOMONTH([@Date], 0)            // month-end of transaction month
=EDATE([@Date], 3)              // +3 months

    15) DATE, YEAR, MONTH, DAY

    What they do: Build and dissect dates (reporting, grouping).

    =DATE(2025,4,1)
=YEAR([@Date])    // 2025
=MONTH([@Date])   // 4
=DAY([@Date])     // 1

    16) DATEDIF

    What it does: Precise gaps (undocumented but reliable).

    =DATEDIF([@JoiningDate], TODAY(), "Y")   // years of service

    Other units: "M", "D", "YM" (months ignoring years), "MD".

    17) NETWORKDAYS / NETWORKDAYS.INTL

    What they do: Business days between dates (exclude weekends/holidays).

    =NETWORKDAYS([@InvoiceDate], [@DueDate], HolidayList[Date])

    NETWORKDAYS.INTL lets you define weekend pattern (e.g., Friday–Saturday).

    18) WORKDAY / WORKDAY.INTL

    What they do: Add business days to a date (promised date / SLAs).

    =WORKDAY([@InvoiceDate], 7, HolidayList[Date])   // due date after 7 workdays

    19) TEXT

    What it does: Format numbers/dates to text (report labels, exports).

    =TEXT([@Date], "dd-mmm-yyyy")
=TEXT([@Amount], "₹#,##0.00")

    20) TEXTJOIN / CONCAT

    What they do: Build strings (invoice titles, addresses).

    =TEXTJOIN(", ", TRUE, [@Customer], [@City], [@State])

    Skips blanks with the TRUE argument.

    21) FILTER (Dynamic arrays)

    What it does: Extract rows matching criteria—live query!

    =FILTER(Transactions, (Transactions[Account]="Sales")*(Transactions[Status]="Paid"))

    Great for creating dynamic sub-ledgers.

    22) UNIQUE

    What it does: Distinct lists (customers, accounts) for validation and pivots.

    =UNIQUE(Transactions[Customer])

    23) SUBTOTAL

    What it does: Aware of filters; ignores hidden rows (choose function code 9/109 for SUM).

    =SUBTOTAL(109, Transactions[Amount])   // SUM visible only

    24) NPV, IRR, PMT (Finance Trio)

    What they do: Core finance math for accountants.

    • NPV – Net Present Value:
    =NPV(10%, C2:C7) + C1

    (10% discount rate; C1 is initial outflow if entered as a positive value—add it separately.)

    • IRR – Internal Rate of Return:
    =IRR(C1:C7)
    • PMT – Loan EMI:
    =PMT(10%/12, 60, -500000)

    (10% annual, 60 months, ₹5,00,000 principal.)

    For irregular timings, use XNPV/XIRR.

    25) SORT

    What it does: Sort ranges dynamically (often used with FILTER/UNIQUE).

    =SORT(FILTER(Transactions, Transactions[Status]="Unpaid"), 1, 1)

    Sorts by first column ascending.

    Practical Mini-Scenarios

    A) Aging Bucket (30/60/90+)

    =IFS([@DaysDue]<=30,"0–30",[@DaysDue]<=60,"31–60",[@DaysDue]<=90,"61–90",TRUE,"90+")

    B) Month-End Provisioning

    =IF(EOMONTH([@Date],0)=TODAY(),"Provision","")

    C) Sales by Rep (Dynamic report)

    =LET(
 data, Transactions,
 sales, FILTER(data, data[Account]="Sales"),
 SUMIFS(sales[Amount], sales[Salesperson], H2)
)

    (Using LET to make it readable; optional but powerful.)

    Common Pitfalls & Pro Tips

    • Dates: Use DATE(yyyy,mm,dd) inside criteria (avoid text dates).
    • SUMIFS text criteria: Use operators with & → ">="&DATE(2025,4,1).
    • Rounding: Always round before tax filings/exports to prevent paise mismatches.
    • Dynamic Arrays: If results “spill,” ensure cells below/right are empty.
    • Lookups: Prefer XLOOKUP with a clear not-found message: =XLOOKUP(A2, Map[Code], Map[Name], "No match")

    Quick Reference (What to use when)

    • Conditional totals/counts: SUMIFS, COUNTIFS, SUMPRODUCT
    • Lookups: XLOOKUP (or INDEX+MATCH)
    • Dates & working days: EOMONTH, EDATE, NETWORKDAYS, WORKDAY
    • Cleanup/formatting: TEXT, TEXTJOIN, rounding functions
    • Dynamic reporting: FILTER, UNIQUE, SORT, SUBTOTAL
    • Finance: NPV, IRR, PMT

    Featured products

  • Top 20 Excel Tricks That Will Make You Work Faster

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

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

    1. Use Flash Fill for Instant Data Entry

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

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

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

    Instead of dragging the mouse, use:

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

    3. Turn Numbers into Charts in Seconds

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

    4. Paste Special (Values, Formats, Operations)

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

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

    5. Insert Today’s Date & Time Instantly

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

    6. Use Conditional Formatting for Insights

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

    7. Freeze Panes for Easy Navigation

    Working on long spreadsheets?

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

    8. Quickly Remove Duplicates

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

    9. Use Text to Columns

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

    10. VLOOKUP (Still a King!)

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

    11. XLOOKUP (The Modern Alternative)

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

    12. Use FILTER Function

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

    13. Quick AutoSum with Alt + =

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

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

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

    15. Power Query for Data Cleaning

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

    16. Use Named Ranges

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

    17. Keyboard Shortcuts You Must Know

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

    18. IF Function for Logic

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

    19. Use PivotTables for Instant Summaries

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

    20. Protect Sheets and Cells

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

    ✅ Final Thoughts

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

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

    Office Productivity Courses

    Recommended Laptops

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

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

    👤 Who Should Use This?

    This list is ideal for:

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

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

    🟩 Section 1: Basic Excel Skills

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

    🟨 Section 2: Formulas and Functions

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

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

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

    🟦 Section 4: Charts and Visualizations

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

    🟫 Section 5: Pivot Tables & Data Analysis

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

    🟥 Section 6: Advanced Excel

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

    🔵 Section 7: Macros and VBA

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

    🟣 Section 8: Macros and VBA (Continued)

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

    🔶 Section 9: Error Handling and Troubleshooting

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

    🔷 Section 10: Excel Productivity Tips

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

    ⚫ Section 11: Scenario-Based & Practical Questions

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

    🟤 Section 12: Bonus & Conceptual Questions

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

    🎓 Final Tips for Excel Interview Preparation

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

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


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

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

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

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

    Featured products

  • MIS Executive Job Analysis: What Companies Are Really Looking For

    Here’s a detailed job market analysis for the MIS Executive role, based on real listing

    🧩 Key Responsibilities Across Companies

    From startups to giants like Axis Bank, here’s what employers expect from an MIS Executive:

    AreaResponsibilities
    Data Management– Collect, clean & validate data- Maintain live databases (like HRMS or Org Charts)- Ensure data accuracy and integrity
    Reporting– Prepare Daily/Weekly/Monthly MIS reports- Design dashboards & data summaries- Present KPIs (Sales, Inventory, HR, etc.)
    Excel Proficiency– Use advanced formulas (VLOOKUP, HLOOKUP, SUMIF, COUNTIF)- Create Pivot Tables & Charts- Automate reports with Macros
    Cross-functional Coordination– Work with HR, Sales, Ops for inputs- Help in audits & compliance reporting
    Visualization & Insights– Track anomalies & trends- Suggest areas of improvement

    💼 Job Titles

    • MIS Executive
    • MIS Reporting Analyst
    • Data Coordinator
    • Excel Reporting Specialist

    💰 Salary Insights

    ExperienceSalary Range (LPA)
    0–1 Years₹1.75 – ₹3 LPA (Aarti, Axis Bank)
    1–6 Years₹3.25 – ₹4.25 LPA (Bigbasket)

    💡 Tip: The salary varies based on Excel skill level, automation ability, and domain knowledge (retail, HR, finance).

    🎯 Must-Have Skills (From Job Listings)

    ✅ Technical

    • Microsoft Excel (VLOOKUP, HLOOKUP, Pivot Tables, SUMIF, COUNTIF)
    • Excel Automation using Macros (VBA – sometimes optional)
    • Dashboard Creation
    • Basic Data Visualization
    • HRMS, Google Sheets (for HR/Org roles)

    ✅ Soft Skills

    • Attention to detail
    • Communication with cross-teams
    • Analytical thinking
    • Time management for regular reporting

    🎤 Common Interview Questions (and How to Prepare)

    TypeSample QuestionWhat They’re Testing
    Excel Skills“What’s the difference between VLOOKUP and INDEX-MATCH?”Advanced formula knowledge
    Practical“How would you create a monthly sales report with trends?”Real-world Excel reporting
    Scenario“What if a team gives you inconsistent data every week?”Problem-solving & communication
    Tech“Can you automate a daily report?”Macros / Power Query (if applicable)
    Behavioral“Have you ever spotted an anomaly in data?”Attention to detail & impact

    📘 How to Prepare for the MIS Executive Role

    1. Master Excel Thoroughly

    Don’t just “know” Excel. Learn to solve business problems using Excel. Practice:

    • Creating dashboards with Pivot Tables & Charts
    • Writing nested formulas
    • Automating monthly reports
    • Simulating HR or sales data reports

    2. Build Sample Projects

    • Inventory Tracker
    • Employee Attendance Dashboard
    • Sales Performance Analysis
    • HR Org Chart Maintenance (Google Sheets + Excel hybrid)

    3. Be Interview-Ready

    • Prepare 2–3 real examples of Excel work
    • Explain how you improved speed or accuracy
    • Learn to explain technical formulas in simple terms

    💡 Your Path to Becoming an MIS Pro Starts Here…

    If you’re serious about landing an MIS Executive job, Excel is not optional—it’s your core skillset.

    🎓 Master Excel 365 – From Beginner to Advanced is a complete, job-oriented course to take you from basic to pro in just 11.5 hours.

    ✅ Includes:

    • Real-life reporting scenarios
    • VLOOKUP, Pivot Table, Macros, Charts
    • Downloadable resources
    • Certificate of Completion
    • Only ₹299

    🚀 Whether you’re a fresher or upskilling for a promotion—this course will make you confident, interview-ready, and Excel-savvy.

    Top rated products

  • How to Highlight Entire Rows Based on Multiple Conditions in Excel

    To highlight entire rows based on multiple cell values in Excel, you can use Conditional Formatting with a custom formula. This is especially useful when you want to visually differentiate rows meeting specific conditions.

    ✅ Example Scenario:

    You have a table with columns: Name, Department, and Status.
    You want to highlight entire rows where:

    • Department is “Sales”
      AND
    • Status is “Active”

    🔍 Step-by-Step Guide:

    1. Select Your Data Range

    For example, if your data is in A2:C100, select A2:C100
    (Always start from the top-left cell of your data range.)

    2. Go to Conditional Formatting

    • Click on the Home tab.
    • Click Conditional Formatting → New Rule.
    • Choose “Use a formula to determine which cells to format.”

    3. Enter the Formula

    Assuming:

    • Department is in Column B
    • Status is in Column C
    • The first row of data starts from Row 2

    Use this formula:

    =AND($B2="Sales", $C2="Active")

    ✅ Explanation:

    • $B2 locks the column so Excel evaluates the correct column as it scans across the row.
    • The row number 2 matches the top row of your selection.
    • AND() ensures both conditions are satisfied.

    4. Set the Format

    • Click Format, choose a fill color (e.g., light yellow), bold text, or border.
    • Click OK.

    5. Apply and Done!

    Now all rows where Department = Sales and Status = Active will be highlighted.

    🧠 Tip:

    You can modify the logic:

    • To use OR instead of AND: =OR($B2="Sales", $C2="Active")
    • For number-based conditions, like: =AND($B2="Sales", $C2>80)

    Best selling 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

  • Excel Tables Masterclass: 14 Powerful Tips to Organize, Analyze & Automate Your Data

    Excel Tables are an often-overlooked but game-changing feature for anyone working with structured data. Whether you’re managing sales reports, employee databases, or project trackers, turning your data into a table gives you clarity, structure, automation, and style—all in a few clicks.

    Let’s dive into what makes Excel Tables so powerful and explore 13 expert tips to help you become a Data Guru.

    🔹 1. Instantly Format Your Data with Built-In Table Styles

    Creating a well-styled table is effortless in Excel. Select your data and press Ctrl + T to convert it into a table. Then, go to the Home → Format as Table section to choose from various pre-built styles.

    🎨 Want something custom? Head to the Table Design tab, where you can create your own color themes for headers, alternating rows, and more.

    🔹 2. Zebra Striping Without Extra Work

    Alternating row colors—commonly called “zebra lines”—are automatically applied when you use Excel Tables. This improves readability and removes the need for manual formatting or conditional formatting rules.

    To toggle this feature:

    • Go to the Table Design tab
    • Check or uncheck Banded Rows or Banded Columns

    🔹 3. Built-In Filters and Sorting Per Table

    Each table comes with independent filter and sort buttons at the top of every column. Even if you have multiple tables on the same sheet, each one gets its own filter set—something standard ranges can’t offer.

    🔍 Use these filters to analyze specific segments of your data in seconds.

    🔹 4. Add Slicers for Visual Filtering

    Slicers aren’t just for PivotTables. You can also use them with Excel Tables for interactive filtering.

    To add a slicer:

    • Select the table → Go to Insert or Table Design → Insert Slicer
    • Choose the field you want to filter by (e.g., Department)

    Now, your table updates dynamically as you click through the slicer buttons.

    🔹 5. Say Goodbye to A1:B10, Hello to Structured References

    One of the biggest advantages of Excel Tables is structured referencing. Instead of cryptic cell references like =B2*C2, you can use meaningful formulas like:

    excelCopyEdit=[@Quantity]*[@Price]

    Structured references are self-updating—when you add or remove rows, your formulas remain accurate.

    🔹 6. Effortless Calculated Columns

    Need a new column for a bonus, tax, or score calculation? Just type your formula into the first cell of the column. Excel will:

    • Automatically fill the rest
    • Apply formatting
    • Adjust if the table grows or shrinks

    Example:

    excelCopyEdit=[@Salary]*0.10

    Boom—you just created a Bonus column!

    🔹 7. Total Row for Quick Summaries

    Want a quick SUM, AVERAGE, MAX, or COUNT? Turn on the Total Row from the Table Design tab. A new row appears at the bottom where you can choose the summary type for each column.

    This is a non-destructive way to analyze data on the fly.

    🔹 8. Need to Revert? Convert Table Back to Range

    If you ever want to convert your table back to a normal range:

    • Go to the Table Design tab
    • Click Convert to Range

    Excel will retain your data and formatting but remove table behavior and structured references.

    🔹 9. Create PivotTables in One Click

    Excel Tables are PivotTable-ready. Select any cell in the table, go to Insert → PivotTable, and you’re ready to analyze your data.

    As your table grows, the PivotTable will stay connected—no need to manually update ranges.

    🔹 10. Publish Tables to SharePoint (For Corporate Use)

    Working in an enterprise setting? You can publish your table to a SharePoint List for organization-wide sharing. This is great for leaderboard displays, project trackers, or shared employee directories.

    📌 Requires SharePoint integration with Excel.

    🔹 11. Print Only the Table—Not the Whole Sheet

    Want to print just the table and nothing else?

    • Select any cell in the table
    • Press Ctrl + P
    • Under Print Settings, choose “Print Selected Table”

    Perfect for clean printouts without adjusting margins or page breaks.

    🔹 12. Transform Tables with Power Query

    Want to clean, reshape, or merge table data from multiple sources? Just click:
    Data → Get & Transform → From Table/Range

    Power Query will treat your table as a data source. You can:

    • Remove duplicates
    • Split columns
    • Filter, group, and aggregate
    • Merge with other tables

    It’s a visual way to perform advanced data manipulation without formulas or VBA.

    🔹 13. Link Multiple Tables via Relationships

    Excel allows you to connect multiple tables (like relational databases) using the Data Model. Once linked, you can:

    • Build complex PivotTables using fields from different tables
    • Avoid using VLOOKUP or XLOOKUP
    • Create cleaner, more modular workbooks

    Use the Relationships button under the Data tab to define your connections.

    🔹 14. Use Excel Tables as Dynamic Data Validation Lists

    Excel Tables can power drop-down menus that automatically update when you add or remove list items.

    👉 Scenario:

    You have a table named ProductList with a column called ProductName. You want to create a drop-down list that always reflects the current list of products.

    🛠️ Steps:

    1. Define a named range using: excelCopyEdit=ProductList[ProductName]
    2. Use Data → Data Validation
      Choose “List” and enter: excelCopyEdit=ProductList[ProductName]

    ✅ Now your dropdown menu stays in sync with your table — no manual updates needed!

    🔁 Great for forms, dynamic dashboards, or preventing data entry errors.

    🧠 Final Thoughts: Why Tables Should Be Your Default Structure

    Excel Tables offer:

    • Clean formatting
    • Dynamic ranges
    • Auto formulas
    • Seamless integration with charts, pivots, and slicers
    • Stronger data modeling

    Yet many users ignore them. Don’t be that user.

    Tables are your gateway to Excel mastery—and when combined with tools like Power Query and VBA, they become even more powerful.

    🚀 Take It to the Next Level with Excel VBA Automation

    If you’re enjoying the structure and automation of Excel Tables, you’ll love what VBA (Visual Basic for Applications) can do. Imagine:

    • Creating tables from raw data automatically
    • Adding calculated columns with one click
    • Exporting filtered reports via email or PDF
    • Automating Power Query tasks and refreshing PivotTables

    🎓 Mastering Excel Automation – Excel VBA Training Course

    ✅ Course Highlights:

    • 42 concise and practical videos
    • 4 hours 8 minutes of hands-on training
    • Beginner-friendly, project-based approach
    • Lifetime access for just ₹441 (original price ₹1,299)

    🔗 👉 Enroll Now and Unlock Excel’s Full Potential

    Best selling products

  • How to Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel 365 with Real-World Examples

    📘 Overview of the Functions

    These text functions are new in Excel 365 and Excel 2021, part of the dynamic array functions family. They are useful for splitting or extracting parts of text based on delimiters (like commas, spaces, hyphens, etc.).

    🔹 1. TEXTBEFORE

    ✅ Purpose:

    Extracts the text before a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    You have email addresses in a list and want to extract usernames (text before @).

    🧪 Example:

    excelCopyEdit=TEXTBEFORE("john.doe@gmail.com", "@")

    ➡️ Result: john.doe

    🔹 2. TEXTAFTER

    ✅ Purpose:

    Extracts the text after a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    From an email, you want to extract just the domain name.

    🧪 Example:

    excelCopyEdit=TEXTAFTER("john.doe@gmail.com", "@")

    ➡️ Result: gmail.com

    🔹 3. TEXTSPLIT

    ✅ Purpose:

    Splits a text string into rows or columns using one or more delimiters.

    📘 Syntax:

    excelCopyEdit=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

    🔧 Scenario:

    You have full names like "John,Doe" and want to split them into first name and last name in two columns.

    🧪 Example:

    excelCopyEdit=TEXTSPLIT("John,Doe", ",")

    ➡️ Result:

    AB
    JohnDoe

    🔄 Combined Real-World Example

    🎯 Scenario:

    You have a product list like this:

    arduinoCopyEdit"SKU123-Apple-Fruit"
"SKU456-Banana-Fruit"

    And you want to extract:

    ABCD
    SKU456-Banana-FruitSKU456BananaFruit

    🧪 Formulas:

    To get the SKU:

    excelCopyEdit=TEXTBEFORE(A1, "-")

    To get the Fruit Name:

    excelCopyEdit=TEXTSPLIT(TEXTAFTER(TEXTBEFORE(A1,"-Fruit"), "-"), "-")

    To get the Category:

    excelCopyEdit=TEXTAFTER(A1, "-", 2)

    📝 Summary Table

    FunctionUse Case ExampleDescription
    TEXTBEFORETEXTBEFORE("file.docx", ".")Returns "file" before .
    TEXTAFTERTEXTAFTER("file.docx", ".")Returns "docx" after .
    TEXTSPLITTEXTSPLIT("John,Doe", ",")Splits into "John" and "Doe"

    ✅ Bonus: Why Use These?

    • Avoids complex combinations of LEFT, RIGHT, FIND, and LEN
    • Works dynamically on arrays and ranges
    • Simplifies text cleaning and parsing in data analysis

    Top rated products

  • Excel 365 VALUETOTEXT Function Explained: Syntax, Examples, and Use Cases

    🔤 VALUETOTEXT Function in Excel 365 – Explained in Detail

    ✅ What is VALUETOTEXT?

    The VALUETOTEXT function in Excel 365 converts any value — number, text, logical value, or error — into a text string.

    It is particularly useful when you want to ensure data types are consistent, especially when working with dynamic arrays, formulas, or combining different data types into text outputs.

    📘 Syntax

    =VALUETOTEXT(value, [format])
    ArgumentDescription
    valueThe value or range you want to convert to text
    format (optional)Format type: 0 for concise (default), 1 for strict

    🧩 Format Options

    • 0 (Concise) – Outputs text without quotes, more human-readable.
    • 1 (Strict) – Outputs text with quotes, useful for programming/debugging.

    📌 Examples

    ✅ Example 1: Convert a number to text

    =VALUETOTEXT(123)

    Result: "123"

    ✅ Example 2: Convert boolean to text

    =VALUETOTEXT(TRUE)

    Result: "TRUE"

    ✅ Example 3: Convert a text value (with default format)

    =VALUETOTEXT("Excel")

    Result: "Excel" (No quotes in the result because default format is concise)

    ✅ Example 4: Use strict formatting

    =VALUETOTEXT("Excel", 1)

    Result: "\"Excel\"" (Quotes included)

    ✅ Example 5: Convert a formula result

    =VALUETOTEXT(A1+B1)

    If A1 = 10 and B1 = 20, result: "30"

    🧠 Usage with Arrays

    If you use VALUETOTEXT on an array, it returns each item as a text string, making it useful for debugging array formulas.

    =VALUETOTEXT({1,2,3})

    Result: { "1", "2", "3" } (array of text)

    🎯 When Should You Use VALUETOTEXT?

    • ✅ When building dynamic labels, tooltips, or messages with text + values
    • ✅ When converting numeric output to text format for export
    • ✅ For debugging dynamic array formulas
    • ✅ To standardize data type for further text manipulation or functions like TEXTJOIN, CONCAT, etc.

    ⚠️ Important Notes

    • VALUETOTEXT is available only in Excel 365 and Excel 2021.
    • It’s similar to TEXT, but simpler and doesn’t require a number format.
    • Different from VALUE, which converts text to a number (opposite functionality).

    🔁 Comparison: VALUETOTEXT vs TEXT

    FeatureVALUETOTEXTTEXT
    Converts to text?✅ Yes✅ Yes
    Requires format?❌ No✅ Yes (number format)
    Works on arrays?✅ Yes✅ Yes (limited)
    Output formattingBasic text conversionCustom number/text format

    Best selling products

  • Top New Functions in Excel 2021 Explained with Examples: XLOOKUP, FILTER, SORT & More

    🆕 New Functions Introduced in Excel 2021 — Explained with Usefulness

    Microsoft Excel 2021 brought a significant upgrade by introducing several dynamic array functions and smarter lookup and filtering tools. These functions were previously exclusive to Microsoft 365 users but are now part of the perpetual Excel 2021 version, helping users automate tasks, reduce formula complexity, and improve data analysis.

    🔍 1. XLOOKUP

    Purpose: To search for a value in a column or row and return a corresponding value from another column or row.

    Syntax:

    excelCopyEdit=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    Usefulness:

    • Replaces older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH.
    • No need to worry about column numbers or data being sorted.
    • Supports exact, approximate, and wildcard matching.
    • Works both vertically and horizontally.

    Example:

    excelCopyEdit=XLOOKUP("Apple", A2:A100, B2:B100, "Not Found")

    This looks for “Apple” in column A and returns the value from column B in the same row.

    🔢 2. XMATCH

    Purpose: Returns the relative position of a value within a range, similar to MATCH but more versatile.

    Syntax:

    excelCopyEdit=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    Usefulness:

    • Supports reverse search and wildcard matching.
    • Useful for locating the position of a value in arrays, which can then be used with INDEX or CHOOSE.

    Example:

    excelCopyEdit=XMATCH(50, A1:A10)

    Returns the position of 50 in the range A1:A10.

    🧹 3. FILTER

    Purpose: Extracts only the data that meets certain criteria from a range.

    Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])

    Usefulness:

    • Dynamically displays filtered data in a separate area.
    • Great for dashboards, reporting, or conditional data extraction.
    • Automatically expands or contracts based on the filter results.

    Example:

    excelCopyEdit=FILTER(A2:B10, B2:B10="North")

    Returns only rows where the second column has “North” as the value.

    🔢 4. SORT

    Purpose: Sorts a range or array in ascending or descending order.

    Syntax:

    excelCopyEdit=SORT(array, [sort_index], [sort_order], [by_col])

    Usefulness:

    • Unlike traditional sort, it doesn’t affect the original data.
    • Automatically updates when the source data changes.
    • Useful in dynamic dashboards and data tables.

    Example:

    excelCopyEdit=SORT(A2:B10, 2, -1)

    Sorts the range A2:B10 by the second column in descending order.

    🔢 5. SORTBY

    Purpose: Sorts a range or array based on the values in another array.

    Syntax:

    excelCopyEdit=SORTBY(array, by_array1, [sort_order1], ...)

    Usefulness:

    • More flexible than SORT, as it lets you sort by related fields not in the output.
    • Excellent for sorting one table based on another column or lookup.

    Example:

    excelCopyEdit=SORTBY(A2:B10, C2:C10, 1)

    Sorts A2:B10 based on values in C2:C10 in ascending order.

    🔄 6. UNIQUE

    Purpose: Extracts a list of unique values from a range or array.

    Syntax:

    excelCopyEdit=UNIQUE(array, [by_col], [exactly_once])

    Usefulness:

    • Removes duplicates quickly and dynamically.
    • Especially useful for creating drop-down lists or summary views.

    Example:

    excelCopyEdit=UNIQUE(A2:A100)

    Returns a list of unique values from column A.

    🔢 7. SEQUENCE

    Purpose: Generates a list of sequential numbers in an array format.

    Syntax:

    excelCopyEdit=SEQUENCE(rows, [columns], [start], [step])

    Usefulness:

    • Helpful for creating index numbers, date sequences, or testing data structures.
    • Can generate both 1D and 2D arrays.

    Example:

    excelCopyEdit=SEQUENCE(5,1,10,2)

    Generates 5 numbers starting from 10, increasing by 2 (i.e., 10, 12, 14, 16, 18).

    🎲 8. RANDARRAY

    Purpose: Returns an array of random numbers.

    Syntax:

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [integer])

    Usefulness:

    • Generate sample data for testing or simulations.
    • Can return decimal or whole numbers.
    • Recalculates with every workbook change unless frozen with F9.

    Example:

    excelCopyEdit=RANDARRAY(5, 2, 1, 100, TRUE)

    Generates a 5×2 array of random whole numbers between 1 and 100.

    📦 9. LET

    Purpose: Assigns names to calculation results to reuse within a formula, improving performance and readability.

    Syntax:

    excelCopyEdit=LET(name1, value1, calculation)

    Usefulness:

    • Makes complex formulas more readable.
    • Optimizes performance by computing once and reusing.

    Example:

    excelCopyEdit=LET(x, A1+10, x*2)

    Calculates A1 + 10 once, stores it as x, and then returns x * 2.

    📘 Bonus: Other Useful Functions from Excel 2019 Now Common in Excel 2021

    While not brand-new to Excel 2021, the following were improved and widely adopted:

    • TEXTJOIN – Joins multiple text items with a delimiter.
    • IFS – Replaces complex nested IF formulas.
    • SWITCH – Easier alternative to multiple IF statements for fixed-value cases.

    ⚠️ Not Available in Excel 2021

    Some advanced functions are only available in Microsoft 365 (not Excel 2021), such as:

    • TEXTSPLIT
    • DROP, TAKE, VSTACK, HSTACK
    • WRAPROWS, WRAPCOLS
    • TOCOL, TOROW
    • MAP, REDUCE, SCAN, BYROW, BYCOL

    These are part of the Lambda family and advanced array manipulations introduced later in Excel 365.

    Top rated products