Training By Himanshu

Tag: excel for beginners to advanced

  • 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

  • How to Create a Pivot Table from a Pivot Table in Excel – Step-by-Step Guide with Example

    📊 Pivot Table from a Pivot Table – A Deep Dive

    When working with complex datasets in Excel, Pivot Tables are often your best friend. They allow you to summarize, analyze, and explore data with just a few clicks. But sometimes, your analysis doesn’t stop at the first summary. You may find yourself needing a second layer of analysis based on your already-pivoted data.

    That’s when you enter the world of creating a Pivot Table from a Pivot Table—a surprisingly useful yet underused Excel technique that every data professional should know.

    Let’s understand it clearly, with practical steps and a real-world example.

    🤔 What Does “Pivot Table from a Pivot Table” Mean?

    The phrase “Pivot Table from a Pivot Table” refers to the process of creating a new Pivot Table based not on raw or source data, but on a summarized Pivot Table. In simpler terms, it’s like building a summary on top of a summary.

    But why would you do this?

    • To simplify overly complex original data
    • To avoid loading large raw data repeatedly
    • To perform higher-level aggregation or trend analysis
    • To share summarized data without revealing the original source

    This method is particularly useful when working with massive data files, collaborating across teams, or preparing executive-ready summaries that need to be clear, clean, and to the point.

    📌 Example – Let’s Walk Through It

    Let’s say you have sales data from multiple regions over several months, something like this:

    MonthRegionProductSales
    Jan-2025EastA1200
    Jan-2025WestB950
    Feb-2025EastA1800
    Feb-2025WestB1150
    …………

    You create a Pivot Table that summarizes Sales by Region and Month. Great—now you have a monthly report by region.

    But what if you now want to:

    • Compare quarterly totals by region?
    • Calculate the average monthly sales?
    • Find the maximum or minimum monthly sale per region?

    You could try adjusting your original Pivot Table. But that might clutter things, or break your formatting, or disturb other connected visuals. Instead, you can create a Pivot Table from your first Pivot Table—a clean, layered approach.

    🛠️ Steps to Create a Pivot Table from a Pivot Table

    ✅ Step 1: Create Your First Pivot Table

    Insert your first Pivot Table using the original data source. For example:

    • Rows: Region
    • Columns: Month
    • Values: Sum of Sales

    This gives you a grid of summarized sales values.

    ✅ Step 2: Copy the Pivot Table

    Select the entire Pivot Table (not the raw data) and copy it.

    ✅ Step 3: Paste Values

    In a new sheet or new location, Paste as Values (Home > Paste > Paste Special > Values). Now you have a static version of the Pivot Table, without the Pivot functionality.

    ✅ Step 4: Create a New Pivot Table

    Now, select this pasted range and go to:

    • Insert > Pivot Table
    • Choose to insert it into a new worksheet

    Voilà! You’ve now created a Pivot Table from a Pivot Table.

    You can now group months into quarters, find averages, or create custom summary reports—without ever touching your original dataset.

    🧠 Why Is This Useful?

    1. Performance: Large datasets slow down workbooks. Using a Pivot Table from a Pivot Table reduces calculation time.
    2. Security: You can share summaries without exposing raw data.
    3. Focus: Keeps reports cleaner by avoiding multiple layers in a single Pivot Table.
    4. Flexibility: You can rearrange or restructure your new Pivot Table freely.

    Many users of Excel—even intermediate ones—don’t realize this trick exists. But once you try creating a pivot table from a pivot table, you’ll appreciate how it simplifies multi-step analysis.

    🎓 Want to Go Further?

    This is just one of many powerful techniques covered in Excel 365.

    If you’re someone who enjoys learning through hands-on, real-world examples, and wants to improve your skills for office, business, or freelancing work, there’s a structured way to do it. A highly-rated online course, “Microsoft Excel 365 – From Beginner to Advanced”, walks you through 85+ video lessons—from basics to dashboards to techniques like creating a pivot table from a pivot table.

    Whether you’re looking to:

    • Master formulas and functions
    • Build interactive dashboards
    • Analyze data using Pivot Tables and charts
    • Work smarter with Excel’s latest features

    This course helps you get there—step by step.

    👉 Explore the Excel 365 course and see how far you can go with the right guidance.

    📌 Final Thoughts

    Creating a pivot table from a pivot table is a smart and scalable technique to handle second-level analysis in Excel. It gives you clarity, flexibility, and speed—all while keeping your data organized.

    Once you get comfortable with this method, you’ll find new ways to simplify your reporting process and build more insightful reports with ease.

    Top rated products