Training By Himanshu

Tag: excel functions guide

  • Top 10 Excel Functions Every MIS Executive Must Know for Reporting, Automation and Data Analysis

    In today’s corporate environment, Top 10 Excel Functions Every MIS Executive Must Know is not just a topic—it is a core requirement for anyone working in reporting, data handling, and business analysis. MIS executives deal with large datasets, monthly reports, dashboards, and decision-making tools. Without mastering Excel functions, handling such responsibilities becomes slow and error-prone.

    This guide explains the most powerful Excel functions every MIS professional should use daily, along with real-life office use cases, formulas, and practical insights.

    Why Excel Functions Are Critical for MIS Executives

    MIS (Management Information System) roles are heavily data-driven. A typical MIS executive:

    • Works with thousands to lakhs of data rows
    • Generates daily, weekly, and monthly reports
    • Tracks KPIs, sales, inventory, and performance
    • Automates repetitive reporting tasks

    According to industry estimates:

    • MIS professionals save up to 70% time using advanced Excel functions
    • Error reduction improves by 40–60% with automated formulas
    • Companies rely on Excel for over 80% of internal reporting tasks

    Top 10 Excel Functions Every MIS Executive Must Know

    Below are the most essential functions with real-life use cases.

    1. VLOOKUP – Data Retrieval Made Easy

    FunctionReal-Life Use Case
    VLOOKUPFetch employee details, product price, or GST data

    Formula:

    =VLOOKUP(A2,Sheet2!A:B,2,FALSE)

    Use Case:

    If you have employee IDs in one sheet and details in another, VLOOKUP helps retrieve the information instantly.

    2. INDEX + MATCH – Advanced Lookup Combination

    FunctionReal-Life Use Case
    INDEX + MATCHFlexible data lookup in large MIS reports

    Formula:

    =INDEX(B:B,MATCH(A2,A:A,0))

    Why Important:

    • Works faster than VLOOKUP in large datasets
    • Allows left and right lookup
    • Preferred in professional MIS reporting

    3. SUMIFS – Conditional Data Summation

    FunctionReal-Life Use Case
    SUMIFSSales summary by region, product, or date

    Formula:

    =SUMIFS(B:B,A:A,"North")

    Use Case:

    Calculate total sales only for a specific region or category.

    4. COUNTIFS – Data Counting with Conditions

    FunctionReal-Life Use Case
    COUNTIFSCount orders, employees, or transactions based on criteria

    Formula:

    =COUNTIFS(A:A,"Sales",B:B,">50000")

    Use Case:

    Count how many employees achieved sales above a certain target.

    5. IF Function – Decision Making

    FunctionReal-Life Use Case
    IFPerformance evaluation, status tracking

    Formula:

    =IF(B2>=50000,"Achieved","Not Achieved")

    Use Case:

    Used in dashboards to show target achievement status.

    6. IFERROR – Clean Reports Without Errors

    FunctionReal-Life Use Case
    IFERRORRemove #N/A or #DIV/0 errors

    Formula:

    =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found")

    Use Case:

    Prevents errors from appearing in reports shared with management.

    7. CONCAT / TEXTJOIN – Data Combination

    FunctionReal-Life Use Case
    CONCATCombine names, addresses, or codes

    Formula:

    =CONCAT(A2," ",B2)

    Use Case:

    Combine first name and last name into a full name column.

    8. LEFT, RIGHT, MID – Text Extraction

    FunctionReal-Life Use Case
    LEFT/RIGHT/MIDExtract codes, IDs, or numbers

    Formula:

    =LEFT(A2,4)

    Use Case:

    Extract year or department code from employee ID.

    9. NETWORKDAYS – Working Days Calculation

    FunctionReal-Life Use Case
    NETWORKDAYSSalary calculation, attendance tracking

    Formula:

    =NETWORKDAYS(A2,B2)

    Use Case:

    Calculate the number of working days between two dates.

    10. FILTER – Dynamic Data Extraction (Modern Excel)

    FunctionReal-Life Use Case
    FILTERCreate dynamic MIS reports

    Formula:

    =FILTER(A2:C100,B2:B100="Sales")

    Use Case:

    Extract only relevant data without manual filtering.

    How MIS Executives Use These Functions in Real Life

    Daily Reporting

    • Use SUMIFS + COUNTIFS to generate daily sales reports
    • Use IF to highlight performance

    Monthly Dashboard

    • Use INDEX + MATCH for dynamic dashboards
    • Use FILTER for real-time updates

    Data Cleaning

    • Use IFERROR + TRIM + LEFT/RIGHT to clean imported data

    Automation

    • Combine multiple functions to automate repetitive tasks

    Key Benefits of Learning These Excel Functions

    • Reduce report preparation time by 50–70%
    • Improve data accuracy significantly
    • Enhance decision-making speed
    • Increase job opportunities in MIS, accounting, and analytics

    Professionals who master these functions often move into roles like:

    • MIS Analyst
    • Data Analyst
    • Business Analyst
    • Reporting Specialist

    Common Mistakes MIS Executives Should Avoid

    1. Using VLOOKUP Instead of INDEX + MATCH

    VLOOKUP has limitations and can slow down large files.

    2. Not Using IFERROR

    Error values reduce report quality.

    3. Manual Calculations

    Always use formulas to avoid mistakes.

    4. Poor Data Structure

    Unorganized data reduces formula efficiency.

    Pro Tips to Master Excel Faster

    • Practice with real MIS reports
    • Learn shortcut keys to save time
    • Combine multiple functions
    • Use pivot tables along with formulas
    • Focus on automation techniques

    Frequently Asked Questions (FAQs)

    1. Which Excel functions are most important for MIS executives?

    The most important functions are VLOOKUP, INDEX + MATCH, SUMIFS, COUNTIFS, IF, and IFERROR.

    2. Is VLOOKUP still useful for MIS jobs?

    Yes, but INDEX + MATCH is more powerful and flexible for advanced reporting.

    3. How long does it take to learn Excel for MIS roles?

    With consistent practice, basic proficiency can be achieved in 15–30 days, while advanced skills may take 2–3 months.

    4. Can Excel functions automate MIS reports?

    Yes, combining functions like SUMIFS, IF, and FILTER can fully automate reports.

    5. What is the difference between SUMIF and SUMIFS?

    SUMIF works with one condition, while SUMIFS handles multiple conditions.

    6. Are Excel skills enough for MIS jobs?

    Excel is the foundation, but knowledge of dashboards, VBA, and basic SQL adds strong value.

    7. Which function is best for handling errors?

    IFERROR is the best function to handle and clean errors in reports.

    Conclusion

    Understanding the Top 10 Excel Functions Every MIS Executive Must Know can completely transform your efficiency and career growth. These functions are not just formulas—they are tools that help you automate work, reduce errors, and create impactful business reports.

    If you consistently practice these functions and apply them in real-life scenarios, you can quickly become a highly valuable professional in any organization.

    Learn MIS Excel with Real Projects

    If you want to master Excel, automation, dashboards, VBA, and SQL with practical office use cases, you can enroll in this complete training program:

    Master MIS, Excel, VBA, SQL and Automation with Real-Time Projects

    This course is designed for beginners as well as working professionals who want job-ready skills.

    Disclaimer

    This article is for educational purposes only. Excel functions and features may vary depending on the software version. Users are advised to verify formulas before applying them in business or financial reporting.

  • Top 10 Excel Interview Questions with Practical Answers for Freshers and Professionals (2026 Guide)

    Top 10 Excel Interview Questions with Practical Answers is one of the most searched topics among job seekers preparing for roles in MIS, accounting, data analysis, and operations. Excel is a core skill in over 85% of entry-level and mid-level job roles in India, making it essential to prepare thoroughly for Excel-related interview questions.

    In this detailed guide, you will learn the most commonly asked Excel interview questions along with practical answers, examples, and tips to help you confidently clear interviews.

    Why Excel Interview Questions Matter

    Employers test Excel skills because:

    • It is used daily in business operations
    • Helps in data analysis and reporting
    • Improves productivity
    • Reduces manual work

    Key Areas Tested in Interviews

    AreaFocus
    FormulasIF, VLOOKUP, SUMIFS
    Data HandlingSorting, filtering
    AnalysisPivot Tables
    AutomationBasic VBA knowledge

    1. What is VLOOKUP and How Does It Work?

    Practical Answer

    VLOOKUP is used to search for a value in the first column of a table and return a corresponding value from another column.

    Example

    If you have employee data:

    • Search Employee ID
    • Return Employee Name

    Formula Structure

    VLOOKUP(lookup_value, table_array, col_index, FALSE)

    Tip

    Mention that VLOOKUP works left to right only.

    2. Difference Between VLOOKUP and INDEX MATCH

    Practical Answer

    INDEX MATCH is more flexible than VLOOKUP.

    Comparison

    VLOOKUPINDEX MATCH
    Searches left to rightCan search in any direction
    Breaks if column changesMore stable
    Slower on large dataFaster performance

    3. What is Pivot Table?

    Practical Answer

    A Pivot Table is used to summarize large datasets quickly.

    Example

    You can:

    • Calculate total sales
    • Group data by region

    Key Benefit

    Reduces analysis time from hours to minutes.

    4. What is IF Function?

    Practical Answer

    IF function performs logical tests and returns values based on conditions.

    Example

    =IF(A1>50,”Pass”,”Fail”)

    Use Case

    Used in performance evaluation and decision-making.

    5. What is Conditional Formatting?

    Practical Answer

    It highlights cells based on conditions.

    Example

    • Highlight sales above target
    • Mark low scores

    Benefit

    Makes data easy to understand visually.

    6. What are Absolute and Relative References?

    Practical Answer

    • Relative reference changes when copied
    • Absolute reference remains fixed

    Example

    TypeExample
    RelativeA1
    Absolute$A$1

    7. What is Pivot Chart?

    Practical Answer

    A Pivot Chart is a visual representation of Pivot Table data.

    Use Case

    • Sales trends
    • Performance comparison

    8. What is Data Validation?

    Practical Answer

    It restricts input values in a cell.

    Example

    • Dropdown lists
    • Number limits

    Benefit

    Prevents data entry errors.

    9. What is Excel Table?

    Practical Answer

    Excel Table is a structured data format that automatically expands.

    Benefits

    • Auto formatting
    • Dynamic formulas
    • Easy filtering

    10. What is Flash Fill?

    Practical Answer

    Flash Fill automatically fills data based on patterns.

    Example

    Extract first name from full name using Ctrl + E.

    Benefit

    Saves significant time in data cleaning.

    Practical Tips to Answer Excel Questions

    1. Explain with Examples

    Always give real-life examples.

    2. Mention Use Cases

    Explain where you used the function.

    3. Be Clear and Simple

    Avoid complex explanations.

    Common Interview Mistakes

    • Memorizing answers without understanding
    • Not practicing formulas
    • Ignoring practical examples
    • Lack of confidence

    How to Prepare for Excel Interviews

    1. Practice Daily

    Work on real datasets.

    2. Learn Shortcuts

    Improves speed and efficiency.

    3. Build Projects

    Create dashboards and reports.

    4. Revise Basics

    Strong fundamentals are essential.

    Real Interview Scenario

    An interviewer may ask:
    “Create a report showing total sales by region.”

    Expected approach:

    • Use Pivot Table
    • Apply filters
    • Present summary

    This tests practical knowledge, not just theory.

    Skills Employers Look For

    SkillImportance
    Data AnalysisHigh
    Formula KnowledgeHigh
    AccuracyCritical
    SpeedImportant

    Career Opportunities with Excel Skills

    • MIS Executive
    • Data Analyst
    • Accountant
    • Operations Executive

    Excel proficiency can increase salary potential by 20–40%.

    Frequently Asked Questions (FAQ)

    1. What are the most common Excel interview questions?

    VLOOKUP, Pivot Tables, IF function, and data validation.

    2. Is Excel important for jobs?

    Yes, it is required in most business roles.

    3. How can I prepare for Excel interviews?

    Practice formulas, create projects, and revise concepts.

    4. What level of Excel is required?

    Basic to intermediate for most roles.

    5. Are practical questions asked in interviews?

    Yes, many interviews include real-time tasks.

    6. Which Excel function is most important?

    VLOOKUP and Pivot Tables are widely used.

    7. Can beginners crack Excel interviews?

    Yes, with proper practice and understanding.

    Conclusion

    Preparing for Top 10 Excel Interview Questions with Practical Answers can significantly improve your chances of getting hired. Excel is a fundamental skill that employers value across industries.

    By understanding concepts, practicing regularly, and applying real-world examples, you can confidently handle any Excel-related interview question.

    Learn Excel with Practical Training

    If you want to master Excel, dashboards, automation, and real-world projects, you can explore a complete job-oriented training program here:

    Join Excel MIS Reporting Course

    This course helps you become job-ready with practical skills used in real companies.

    Disclaimer

    This article is for educational purposes only. Interview questions may vary depending on company and role.

  • How to Create Dynamic Dropdown Lists in Excel Using OFFSET and COUNTA: Complete Step-by-Step Guide with Examples, Tables, and Advanced Tips

    In modern Excel-based data management, dynamic dropdown lists play a crucial role in improving accuracy, efficiency, and user experience. Static dropdowns often become outdated when new items are added. Dynamic dropdowns solve this problem by automatically expanding or shrinking based on the dataset. One of the most powerful and widely used methods to create a dynamic dropdown list in Excel is the combination of the OFFSET function and the COUNTA function.

    This detailed guide explains the complete process of creating dynamic dropdowns using OFFSET and COUNTA. It covers formulas, examples, data validation steps, troubleshooting, and practical use cases. Whether you’re an Excel beginner or an advanced analyst, this article will help you master dynamic lists with clarity and confidence.

    Why Dynamic Dropdowns Are Important

    Dynamic dropdowns are essential for data entry, reporting, dashboards, and templates. Their advantages include:

    • Automatically adapting when new items are added
    • Reducing errors caused by outdated dropdown options
    • Saving time by avoiding manual updates
    • Maintaining data consistency
    • Making workbooks scalable and professional

    Studies show that dynamic lists can reduce data entry time by nearly 30 percent in frequently updated sheets.

    Understanding the OFFSET Function

    OFFSET returns a reference to a range that is offset from a starting point. Its structure is:

    OFFSET(reference, rows, cols, [height], [width])

    Parameters Explained

    • reference: Starting cell
    • rows: Number of rows to move from the reference
    • cols: Number of columns to move
    • height: Number of rows the returned range should cover
    • width: Number of columns the range should include

    Example

    OFFSET(A1, 0, 0, 5, 1) returns A1:A5.
    This formula helps create ranges that expand dynamically.

    Understanding the COUNTA Function

    COUNTA counts non-empty cells.
    Example: COUNTA(A1:A10) returns the number of filled cells.
    This becomes powerful when combined with OFFSET to adjust the height of the dropdown list.

    Creating a Dynamic Dropdown Using OFFSET + COUNTA

    Below is the complete step-by-step explanation.

    Step 1: Prepare Your List

    Assume your list is in Column A starting from A2. Example values:

    • Apple
    • Mango
    • Banana
    • Orange
    • Grapes

    These five items will form the initial dropdown.

    Step 2: Create the Dynamic Range Formula

    Use the formula:

    =OFFSET($A$2, 0, 0, COUNTA($A$2:$A$100), 1)

    Explanation:

    • Starts from A2
    • Height will change based on how many items are filled
    • Maximum range limit is A100 (can be A1000 or more depending on expected data)

    If you add new values, the height automatically increases.

    Step 3: Create a Named Range

    1. Go to Formulas tab
    2. Select Name Manager
    3. Click “New”
    4. Enter a name such as ProductList
    5. In Refers To box, paste the dynamic formula
    6. Click OK

    Now, ProductList is a fully dynamic named range.

    Step 4: Apply Data Validation

    1. Select the cell where dropdown is required
    2. Go to Data tab
    3. Click Data Validation
    4. Choose List
    5. Type =ProductList
    6. Click OK

    Your dropdown is now dynamic. Any new item added in column A automatically appears in the dropdown.

    Example Table for Understanding

    Table 1: Understanding the OFFSET + COUNTA Setup

    ElementDescription
    Starting CellA2
    Maximum RangeA2:A100
    Dynamic Formula=OFFSET($A$2,0,0,COUNTA($A$2:$A$100),1)

    Real-Life Examples Where Dynamic Dropdowns Are Useful

    1. Inventory Management

    When new products are added:

    • ProductList expands automatically
    • No need to modify data validation

    2. Employee Lists

    HR departments often update employees’ names. Dynamic lists reduce repeated manual updates.

    3. Dashboard Filters

    Dynamic dropdowns synchronise with dynamic charts and pivot tables.

    4. Monthly Reporting

    Items like departments, branches, projects, or cost centers continuously change. Dynamic lists simplify report setup.

    Advanced Techniques Using OFFSET + COUNTA

    1. Dynamic Dropdown with No Blank Cells

    If there are blank cells in between, use:
    =OFFSET($A$2,0,0,COUNTA($A$2:$A$100)-COUNTBLANK($A$2:$A$100),1)

    2. Dropdown with Sorted Dynamic List

    Sort the range and the dropdown updates instantly.

    3. Dependent Dynamic Dropdowns

    Dynamic dropdowns can also be used to create dependent or cascading lists.
    Example: Selecting a category dynamically filters its subcategory list.
    This becomes powerful when combined with INDIRECT and dynamic named ranges.

    4. Dynamic Dropdown Across Multiple Sheets

    You can even place the list on a hidden sheet for cleaner dashboards.

    Troubleshooting Common Issues

    Dynamic dropdowns may sometimes not work as expected. Here are common problems and fixes:

    1. Formula Returns Error

    Cause: Extra blank rows or incorrect range
    Fix: Check COUNTA range size

    2. Dropdown Shows Blank Options

    Cause: Hidden blank rows within range
    Fix: Clean data or use advanced formula

    3. Data Validation Doesn’t Accept Named Range

    Cause: Name contains space or invalid characters
    Fix: Rename without spaces

    4. Dropdown Doesn’t Update

    Cause: Named range not refreshed
    Fix: Reopen workbook or finalize formula
    Statistics show that nearly 25 percent of errors occur due to wrong reference points inside OFFSET.

    Alternative Methods to Create Dynamic Lists

    Although OFFSET + COUNTA is powerful, other methods exist:

    1. Using Excel Tables

    Tables automatically expand
    Formula-free
    Easy to use

    2. Using INDEX + MATCH

    Example dynamic range:
    =$A$2:INDEX($A$2:$A$100,COUNTA($A$2:$A$100))

    3. Using INDIRECT

    Helpful for dependent lists, but more complex

    OFFSET remains popular due to flexibility and ease of use, especially in older Excel versions.

    Performance Consideration

    OFFSET is a volatile function, meaning it recalculates every time Excel refreshes.
    In large workbooks:

    • May slightly slow calculations
    • Better to limit ranges (A2:A500 instead of A2:A5000)
    • Use INDEX alternative if workbook exceeds 50,000 rows

    Research indicates that volatile functions make up nearly 10 percent of performance issues in heavy Excel dashboards.

    Example Calculation Insight

    If your list has 12 items:
    COUNTA returns 12
    Height becomes 12
    OFFSET returns A2:A13
    Dropdown instantly updates without any manual changes.

    If you add a 13th item, the range automatically becomes A2:A14.

    Best Practices for Dynamic Dropdowns

    1. Keep the list clean without blank spaces
    2. Use separate sheet for lists to avoid clutter
    3. Give meaningful names to dynamic ranges
    4. Use limited maximum ranges to improve performance
    5. Protect sheets to prevent accidental formula damage
    6. Combine with conditional formatting to highlight updates
    7. Always test dropdown after adding values
    8. Document formulas for future users

    Professionals using dynamic lists in their workflow report a consistent improvement in accuracy and productivity.

    Conclusion

    Dynamic dropdowns using OFFSET and COUNTA are a powerful way to automate and enhance data entry in Excel. This method adapts instantly to new entries, eliminates manual maintenance, and supports scalable reporting, making it ideal for business users, analysts, accountants, educators, and administrators. Understanding OFFSET, COUNTA, and named ranges opens the door to advanced Excel capabilities, including dependent lists and interactive dashboards.

    By following the detailed steps, formulas, and best practices in this guide, users can build efficient, long-lasting, and flexible dropdown systems that maintain high professional standards.

    Disclaimer

    This article is intended for educational and informational purposes only. All examples and explanations are based on general Excel functions and features. Users should verify formulas based on their specific Excel version and data structure.

  • 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