Training By Himanshu

Tag: Learn Excel

  • Top 10 Excel Functions Every Data Analyst Must Master

    When Rohan, a 26-year-old commerce graduate from Pune, started preparing for his first data analyst interview, he quickly realized one thing – Excel is not just a spreadsheet tool, it’s a career-making skill.

    He had always used Excel for basic sums and formatting, but during mock interviews, he froze when asked,

    “Can you combine INDEX and MATCH to find a sales figure for a product in a given month?”

    That day, Rohan decided – No more guesswork. I will master the top Excel functions recruiters expect.
    Here’s what he learned, with examples from his practice sessions.

    1. VLOOKUP / XLOOKUP – Rohan’s ‘Data Detective’ Tool

    One day, Rohan had two datasets – one with Product Names, another with Sales Values.
    Instead of scrolling endlessly, he used:

    =XLOOKUP("Mango Juice", A2:A100, B2:B100, "Not Found")

    Result: Sales value for Mango Juice in seconds.
    Lesson: Lookup functions save hours in data matching.

    2. INDEX + MATCH – Rohan’s Upgrade

    During an interview test, the product name was in column C, and sales were in column A.
    VLOOKUP couldn’t help (it needs the lookup column first).
    Rohan used:

    =INDEX(A2:A100, MATCH("Mango Juice", C2:C100, 0))

    Lesson: INDEX+MATCH works in any direction and is interview gold.

    3. TEXT Functions – Cleaning Rohan’s Messy Data

    His dataset had customer IDs like " AB1234 " with spaces.
    He cleaned it using:

    =TRIM(A2)

    And extracted first 2 letters for state code:

    =LEFT(A2, 2)

    Lesson: TEXT functions like LEFT, RIGHT, MID, TRIM, and LEN are must-haves for messy datasets.

    4. IF + IFS – Decision Maker

    When given sales targets, Rohan categorized them:

    =IF(B2>=100000, "Top Performer", "Needs Improvement")

    For multiple conditions:

    =IFS(B2>=100000, "Top Performer", B2>=50000, "Average", TRUE, "Low")

    Lesson: IF helps classify data instantly.

    5. SUMIF / SUMIFS – Finding Patterns

    To know the total sales for “Mango Juice” in the “East” region:

    =SUMIFS(Sales, Product, "Mango Juice", Region, "East")

    Lesson: SUMIFS is perfect for quick conditional aggregations.

    6. COUNTIF / COUNTIFS – Counting What Matters

    In one dataset, Rohan needed to know how many orders were above ₹5,000:

    =COUNTIF(Sales, ">5000")

    Lesson: COUNT functions are quick ways to spot trends in large datasets.

    7. FILTER – Rohan’s Shortcut to Relevant Data

    Instead of applying Excel’s manual filter, Rohan extracted all sales for the “North” region with:

    =FILTER(A2:D100, Region="North")

    Lesson: Dynamic, criteria-based extraction beats manual filtering.

    8. UNIQUE – Finding Distinct Customers

    When asked for the number of unique buyers, Rohan did:

    =UNIQUE(CustomerName)

    Lesson: UNIQUE quickly deduplicates lists for better analysis.

    9. Date Functions – Time Travel in Excel

    Rohan needed monthly trends. He used:

    =TEXT(OrderDate, "MMM-YYYY")

    For month-end date:

    =EOMONTH(OrderDate, 0)

    Lesson: Date functions help slice and dice time-based data.

    10. Power Query + Power Pivot – Rohan’s Secret Weapon

    By now, Rohan could clean data in Power Query, load millions of rows, and use DAX for calculated measures.
    In one interview, he impressed the panel by transforming raw CSV files into a dashboard-ready table in 10 minutes.

    Rohan’s Takeaway

    “Excel isn’t about knowing formulas by heart—it’s about knowing which function to use when, and how to combine them.”

    Master these 10 functions, and you’re not just prepared for a data analyst job—you’re prepared for real-world problem solving.

    Top rated products

  • How Mastering MIS Can Fast-Track Your Career in the Data-Driven Economy

    When Rahul graduated with a degree in commerce, like many others, he thought he’d land a decent analyst job right away. But after six months of applying to roles and facing rejection after rejection, he realized something crucial: having a degree wasn’t enough. Employers were looking for real-world skills—especially in handling data, building reports, and automating business processes.

    What he was missing was expertise in MIS (Management Information Systems)—the language of modern business decisions.

    📈 The Rising Demand for MIS Professionals

    In a world where 90% of the data that exists was generated in the last two years alone, the ability to manage, interpret, and present that data has become a core business function. According to a McKinsey report, data-driven organizations are 23 times more likely to acquire customers, and 19 times more likely to be profitable.

    That kind of impact is not possible without people who can build and manage the systems that handle data—MIS professionals.

    From startups to multinational corporations, MIS has become the backbone of:

    • Business Reporting & Dashboards
    • Automated Workflows
    • Data-Driven Decision Making
    • Inventory & HR Management
    • Financial and Operational Analysis

    And yet, there’s a shortage of skilled professionals who can do this efficiently. A 2023 Naukri.com insights report revealed that MIS Executives and Data Analysts were among the top 10 most in-demand non-technical roles in India, with salaries starting from ₹3.5 LPA and reaching ₹10+ LPA with experience and expertise.

    👨‍💻 Rahul’s Turning Point: Learning What Industry Really Needs

    Instead of applying blindly, Rahul took a step back and enrolled in a comprehensive MIS course focused on the practical skills that companies actually hire for—Microsoft Excel (advanced level), Macros (VBA), MS Access, and SQL.

    Within three months:

    ✅ He was creating automated Excel dashboards
    ✅ Writing SQL queries to manage business data
    ✅ Linking data between Access and Excel for seamless reporting
    ✅ Presenting structured insights in interviews confidently

    Shortly after completing his course, Rahul landed an MIS Executive role at a mid-size logistics company. Within a year, he was promoted to Senior Analyst, driving process automation and saving hundreds of man-hours for his team.

    🔍 What Does the Course Include?

    The Complete MIS Training Program is built for learners like Rahul—people who want real results.

    • 🎥 16.5 hours of practical, step-by-step video content
    • 📂 26 downloadable resources, exercises, and templates
    • 🧠 Focus on business use-cases, not just tools
    • 🏅 Certificate of Completion that adds weight to your resume and LinkedIn
    • 👨‍🏫 Real-world simulations based on industry challenges

    Whether you’re a fresher, career-switcher, or someone in a support role looking to grow, MIS is a skill that opens doors across industries—from manufacturing to finance, logistics to healthcare, and IT to FMCG.

    📊 Why Excel, Access, Macros, and SQL?

    These tools are more than just software—they’re the core of modern business operations.

    • Excel remains the most-used business analysis tool worldwide.
    • Macros (VBA) allow automation that saves hours of manual effort.
    • Access helps in managing relational databases without needing deep coding knowledge.
    • SQL is the backbone of querying structured data, essential for any analyst role.

    Together, they form a toolkit that employers across sectors actively seek.

    💬 Hear From Past Learners

    “I had no idea how powerful Excel could be until I learned automation with Macros. The dashboards I built helped me get a 30% hike in my last appraisal.” – Sneha M., MIS Analyst at an eCommerce company

    “The course bridged the gap between my academic knowledge and what companies actually want. The best investment I made after graduation.” – Arun K., Business Associate at a FinTech startup

    🌱 Future-Proof Your Career

    As automation and data analysis become non-negotiable in the business world, roles that once required manual reporting or entry-level data work are being transformed. Companies want people who can manage data flows, automate reports, and build decision-ready dashboards.

    The good news? These are learnable skills, and you don’t need to be a programmer to get started.

    🎯 Ready to Take the Next Step?

    Just like Rahul, you can move from uncertainty to confidence. Whether you’re just starting out or looking to grow in your current role, mastering MIS tools can be a career-defining move.

    👉 Explore the Course & Enroll Now

  • How to Use TOCOL and TOROW Functions in Excel (With Examples)

    Excel 365 and Excel 2021 introduce powerful dynamic array functions like TOCOL and TOROW, which help you reshape arrays into a single column or row effortlessly. Let’s explore how they work and when to use them.

    🔷 1. TOCOL Function – Convert to Column

    📌 Purpose:

    TOCOL transforms a 2D array or table into a single vertical list.

    🧮 Syntax:

    excelCopyEditTOCOL(array, [ignore], [scan_by_column])
    ParameterDescription
    arrayThe range to convert
    ignore0 = none, 1 = ignore blanks, 2 = ignore errors
    scan_by_columnTRUE = by column (default), FALSE = by row

    📊 Example:

    ABC
    123
    456
    excelCopyEdit=TOCOL(A1:C2)

    Result:

    CopyEdit1  
4  
2  
5  
3  
6

    With blank cells ignored:

    excelCopyEdit=TOCOL(A1:C2, 1)

    🔷 2. TOROW Function – Convert to Row

    📌 Purpose:

    TOROW turns a 2D array into a single horizontal list.

    🧮 Syntax:

    excelCopyEditTOROW(array, [ignore], [scan_by_column])

    📊 Example:

    Using the same data:

    excelCopyEdit=TOROW(A1:C2)

    Result:

    CopyEdit1   4   2   5   3   6

    Row-wise scan:

    excelCopyEdit=TOROW(A1:C2, 0, FALSE)

    Result:

    CopyEdit1   2   3   4   5   6

    ✅ Why Use TOCOL/TOROW?

    • Flatten 2D ranges for lookup or processing
    • Prepare lists for filtering or advanced formulas
    • Save time over manual copy-paste or TRANSPOSE hacks

    🎓 Take Your Excel Skills to the Next Level!

    Want to master functions like TOCOL, TOROW, XLOOKUP, FILTER, TEXTSPLIT, and more?

    🚀 Join my best-selling Excel course:
    👉 Mastering MS Excel – A Comprehensive Training Course

    ✅ Available in both Online & Pen Drive formats
    📈 Suitable for students, professionals & business users

    Top rated products