25 Excel Formulas Every Indian Business Owner Needs

Q: What formula do I use to track the MSME 45-day payment rule?

Use `=NETWORKDAYS(invoice_date, TODAY())` to count business days since the invoice. Flag any value over 45 with conditional formatting (red fill). This covers the MSMED Act + Section 43B(h) requirement.

Last updated: 27 June 2026

You do not need to know 500 Excel functions to run a business — you need 25. These formulas handle everything a typical Indian SMB does in Excel: GST calculations, invoice totals, payroll breakups, stock alerts and cash flow summaries. Each example below uses an Indian business scenario so you can copy and adapt immediately.

Key takeaways

SUM, SUMIF and SUMIFS handle 80% of all business totals — by date, customer, product or GST rate.
VLOOKUP (or the newer XLOOKUP) is the engine behind every GST rate lookup and item master reference.
IF and IFS decide between CGST/SGST and IGST automatically based on the buyer's state.
ROUND, ROUNDDOWN and ROUNDUP are mandatory for tax amounts — rounding errors on GST invoices can trigger notices.
IFERROR wraps every lookup so your sheet never shows #N/A to a customer or your CA.
DATE functions (DATE, EOMONTH, EDATE, NETWORKDAYS) track due dates: TDS by the 7th, GSTR-1 by the 11th.

Fact box. A typical GST invoice in Excel uses at least 8 formulas: VLOOKUP (rate lookup), IF (IGST vs SGST), multiplication (taxable × rate), ROUND (tax amount), SUM (line totals), CONCATENATE (invoice number), TODAY (invoice date), and IFERROR (error guard). Mastering these eight takes under an hour and makes every invoice correct by construction.

How to use this list

The 25 formulas are grouped by business task. For each formula you get:

What it does in one line
An example using real Indian SMB data
A tip on where to use it

Group 1: Totals and aggregation (SUM family)

1. SUM

Adds a range. The foundation of every financial sheet.

=SUM(D2:D100)          → total of all values in column D

2. SUMIF

Adds values that meet one condition.

=SUMIF(B2:B100,"Sharma Traders",D2:D100)
→ total sales to Sharma Traders only

3. SUMIFS

Adds values that meet multiple conditions.

=SUMIFS(E2:E100, B2:B100,"Sharma Traders", C2:C100,"Apr-2026")
→ sales to Sharma Traders in April 2026

Use for: rate-wise GST totals for GSTR-1 (sum taxable value where GST rate = 18%), debtor ageing, monthly sales by product.

Group 2: Lookup formulas (the GST rate engine)

4. VLOOKUP

Looks up a value in the first column of a table and returns a value from another column.

=VLOOKUP(A2, RateMaster!A:C, 3, FALSE)
→ looks up the HSN code in A2 in your rate master sheet and returns the GST rate

5. XLOOKUP (Excel 2019+)

The modern replacement for VLOOKUP — looks in any column, returns any column, handles missing values cleanly.

=XLOOKUP(A2, RateMaster!A:A, RateMaster!C:C, "Not found")
→ same lookup, no column-number counting, built-in error handling

6. INDEX + MATCH

For older Excel (2010–2016) where XLOOKUP is unavailable — gives the same flexibility.

=INDEX(RateMaster!C:C, MATCH(A2, RateMaster!A:A, 0))

Tip: Keep a dedicated "Rate Master" sheet with HSN codes, item names, and GST rates. All invoice sheets look up from here. When a GST rate changes, update one cell, and every invoice updates automatically.

Group 3: Decision formulas (IGST vs CGST/SGST)

7. IF

Returns one value if a condition is true, another if false. For GST, the total tax is 18% either way — an intra-state (same-state) sale splits it into CGST 9% + SGST 9%, while an inter-state sale charges the full 18% as IGST. So calculate each head with its own formula driven by the same IF:

CGST  =IF(F2="Maharashtra", D2*9%, 0)
SGST  =IF(F2="Maharashtra", D2*9%, 0)
IGST  =IF(F2="Maharashtra", 0, D2*18%)
→ same-state sale fills CGST 9% + SGST 9% (18% total); inter-state fills IGST 18%

Both branches now levy the same 18% total — only the split between Centre and State changes. See the full CGST/SGST/IGST formula guide ».

8. IFS (Excel 2019+)

Tests multiple conditions without nesting IFs.

=IFS(E2=0%, 0, E2=5%, D2*5%, E2=18%, D2*18%, E2=40%, D2*40%, TRUE, "Check rate")
→ calculates tax based on the rate in E2, covering all current GST slabs

The trailing TRUE, "Check rate" is a catch-all: without it, a rate not in the list (say a stray 12%) returns #N/A instead of a readable flag.

9. AND / OR

Used inside IF to combine conditions.

=IF(AND(C2="MH", B2="B2B"), "CGST+SGST", "IGST")

Tip: For GST invoice tax split, put a "Supply Type" column: "Intra-state" or "Inter-state". Then a single IF drives the entire CGST/SGST vs IGST column. Current GST slabs are Nil / 5% / 18% / 40% (the 12% and 28% slabs were abolished 22 September 2025).

Group 4: Rounding (mandatory for tax)

10. ROUND

Rounds to n decimal places. Use for all tax amounts.

=ROUND(D2*18%, 2)     → tax rounded to paise

11. ROUNDDOWN

Always rounds toward zero — useful for computing the taxable base.

=ROUNDDOWN(C2*0.18, 2)

12. INT

Removes the decimal — useful for per-unit pricing in whole rupees.

=INT(A2/B2)           → rate per unit, truncated to whole rupees

Fact box. GST rules require tax amounts to be rounded to the nearest rupee or paise as per the invoice. Unrounded amounts (e.g. ₹12.3456 instead of ₹12.35) can cause GSTR-1 vs purchase register mismatches for your buyer and trigger ITC queries. Always wrap tax calculations in =ROUND(…, 2).

Group 5: Text and reference formulas

13. CONCATENATE / &

Joins text strings — used to build invoice numbers, challan references, narrative descriptions.

="INV/"&YEAR(TODAY())&"/"&TEXT(A2,"00000")
→ builds INV/2026/00042 from row number 42

14. TEXT

Formats a number or date as text.

=TEXT(B2,"DD-MMM-YYYY")   → "27-Jun-2026"
=TEXT(C2,"##,##,##0.00")  → Indian number format with paise

15. LEFT / MID / RIGHT

Extract parts of a text string — useful for parsing GSTINs or HSN codes.

=LEFT(A2,2)    → extracts state code from a 15-digit GSTIN (first 2 digits = state)
=MID(A2,3,10)  → extracts PAN from GSTIN (characters 3–12)

16. TRIM

Removes extra spaces — the most underused formula. Copied data from portals often has hidden spaces that break lookups.

=TRIM(A2)

Group 6: Date and due-date formulas

17. TODAY / NOW

Returns today's date or current date-time. Handy on a blank invoice template so the date pre-fills — but =TODAY() is volatile and re-reads the system date every time the file opens, so once an invoice is finalised, replace it with a fixed date (press Ctrl+;) so the printed invoice date never shifts.

=TODAY()

18. DATE

Builds a date from year, month, day components.

=DATE(2026, 4, 7)    → 7 April 2026 (TDS due date for March deductions)

19. EOMONTH

Returns the last day of a month, or a month offset.

=EOMONTH(A2, 0)      → last day of the month in A2
=EOMONTH(A2, 1)+11   → 11th of the following month (GSTR-1 due date)

20. EDATE

Adds or subtracts months from a date — for payment terms.

=EDATE(A2, 1)        → one month after invoice date (Net-30 approximate)

21. NETWORKDAYS

Counts working days between two dates — relevant for MSME 45-day payment tracking and Final Settlement under Labour Codes.

=NETWORKDAYS(B2, TODAY())   → working days since invoice date; flag if > 45

Tip: Under the MSMED Act and Section 43B(h) of the Income Tax Act, payments to Micro and Small enterprises must be settled within 45 days (or the agreed credit period if shorter). NETWORKDAYS helps you flag overdue vendor bills automatically.

Group 7: Counting and error handling

22. COUNTIF / COUNTIFS

Counts cells meeting conditions — useful for invoice audits.

=COUNTIF(F2:F200,"Unpaid")    → number of unpaid invoices
=COUNTIFS(C:C,"Sharma",F:F,"Unpaid")   → unpaid invoices for one customer

23. COUNTA

Counts non-empty cells — quick way to count invoice rows without numbering them.

=COUNTA(A2:A1000)   → number of invoices entered

24. IFERROR

Wraps a formula and returns a friendly value if it errors — essential on any sheet a client or CA will see.

=IFERROR(VLOOKUP(A2,RateMaster!A:C,3,FALSE),"Rate not found")

25. ISBLANK / ISTEXT / ISNUMBER

Validation helpers — check whether a required cell is filled in.

=IF(ISBLANK(B2),"⚠ GSTIN missing","OK")

Putting it together: a 5-formula invoice line

A single invoice line in a properly built Excel invoice uses these five formulas in sequence:

Column	Formula	Purpose
GST Rate	`=IFERROR(VLOOKUP(B2,RateMaster!A:C,3,0),"?")`	Look up rate by HSN
Taxable Value	`=ROUND(D2*E2,2)`	Qty × Rate
Tax Amount	`=ROUND(F2*G2,2)`	Taxable × GST%
CGST/IGST split	`=IF(SupplyType="Intra",H2/2,H2)`	Split or full
Line Total	`=F2+H2`	Taxable + Tax

Build this once as a template (free to download from this site, no sign-up) and reuse it for every invoice.

How Ankeshan helps: Ankeshan embeds a live GST rate table and auto-fills the rate lookup, IGST/CGST/SGST split and invoice number sequence for you — all inside Excel, without leaving the workbook. It is launching soon; join the waitlist.

Frequently asked questions

What is the most useful formula for GST invoicing in Excel? VLOOKUP (or XLOOKUP) is the engine — it looks up the GST rate for an item from a rate master, so you never hard-code a rate. Pair it with IF to split CGST/SGST vs IGST, and ROUND to get the tax amount right.

Is VLOOKUP or XLOOKUP better? XLOOKUP is better in every way — it looks in any column, returns any column, handles missing values without IFERROR, and is faster on large tables. If your Excel version is 2019 or Microsoft 365, use XLOOKUP. For Excel 2016 and earlier, use VLOOKUP or INDEX+MATCH.

How do I show amounts in Indian number format (lakhs, crores) in Excel? Use a custom number format: select the cell, press Ctrl+1, choose Custom, and type ##,##,##0.00. This gives you 1,23,456.78 instead of 123,456.78.

What formula do I use to track the MSME 45-day payment rule? Use =NETWORKDAYS(invoice_date, TODAY()) to count business days since the invoice. Flag any value over 45 with conditional formatting (red fill). This covers the MSMED Act + Section 43B(h) requirement.

Why is IFERROR so important? Without IFERROR, a missing HSN code or a blank row causes #N/A errors to cascade through your sheet. IFERROR catches the error and shows a readable message instead, keeping the sheet clean for printing or sharing.

Sources

Microsoft Support — Excel function reference (support.microsoft.com)
CBIC — GST invoice rules, CGST Rules 2017 (cbic-gst.gov.in)
Ministry of MSME — MSMED Act 45-day payment provision and Section 43B(h), Income Tax Act

General information only, not professional advice. Verify tax rates and legal thresholds on official portals before use. Reviewed by a Chartered Accountant; last updated 27 June 2026.