DEV Community: Ashish Srivastava

How to Add an Order Status Dropdown That Colours Itself

Ashish Srivastava — Wed, 21 Oct 2026 15:47:53 +0000

Subtitle: Data validation, conditional formatting, counts per status, a "stuck" flag, and a check that catches statuses typed by hand

Written with AI assistance. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

A status column is the simplest way to know where every order is. It's also where small sheets go wrong: "Shipped", "shipped", "Shipped " with a trailing space and "Sent" all mean the same thing to you, and four different things to a formula.

Here's how to lock the column to a short list, colour each row by its status, count what's in each stage, and flag orders that have sat still too long. It uses data validation, conditional formatting, COUNTIF and SUMIFS, which work the same way in Excel and Google Sheets.

1. The status list

Make a tab called Lists and type your stages in A2:A6:

New, Packed, Shipped, Delivered, Cancelled

Keep it short. Five stages you actually use beat twelve you don't.

2. The dropdown

Orders from row 5: A Order ID, B Customer, C Amount, D Status, E Status date (the day the status last changed). Put an As of date in B2 (=TODAY() works).

Select D5:D104, then:

Excel: Data → Data Validation → Allow: List → Source: =Lists!$A$2:$A$6. On the Error Alert tab, keep Stop.
Google Sheets: Data → Data validation → Add rule → Dropdown (from a range) → Lists!A2:A6 → "Reject the input".

New entries now have to come from the list. But a dropdown doesn't clean rows that were already there, and pasted values can slip past it, so section 5 adds a check.

3. Colour each row by status

Select A5:G104 and add one conditional formatting rule per status, each with a custom formula:

Rule formula	Suggested fill
`=$D5="Delivered"`	light green
`=$D5="Shipped"`	light blue
`=$D5="Cancelled"`	grey
`=AND($D5="Packed",$F5>2)`	light red (stuck, see below)

The $ before D and F keeps every cell in the row looking at the status column. Without it, only the status cell changes colour.

4. Days in status and a "stuck" flag

F5, days since the status last changed:

=IF(E5="","",$B$2-E5)

G5, a flag column:

=IF(D5="","No status",IF(COUNTIF(Lists!$A$2:$A$6,D5)=0,"Not on list",IF(AND(D5="Packed",F5>2),"Stuck",IF(AND(D5="Shipped",F5>7),"Stuck",""))))

Set your own limits: here an order packed for more than 2 days, or shipped for more than 7 without being marked delivered, gets flagged. Remember to update the status date whenever you change the status.

5. Counts per status, and the check

Put the five stages in J5:J9, then:

Cell	Formula
K5: Orders	`=COUNTIF($D$5:$D$104,J5)`
L5: Amount	`=SUMIFS($C$5:$C$104,$D$5:$D$104,J5)`
K11: Status not on the list	`=SUMPRODUCT(($D$5:$D$104<>"")*(COUNTIF(Lists!$A$2:$A$6,$D$5:$D$104)=0))`
K12: Orders with no status	`=COUNTIFS($A$5:$A$104,"<>",$D$5:$D$104,"")`
K13: Accounted for	`=SUM(K5:K9)+K11+K12`
K14: All orders	`=COUNTA($A$5:$A$104)`
K15: Check	`=IF(K13=K14,"OK","CHECK")`

Fill K5 and L5 down to row 9. K11 and K12 are the useful ones: they show orders that the per-status counts silently skip. The check in K15 confirms every order is either in a stage or listed as a problem.

6. What the sample showed

Seven made-up orders, as of 21 Oct 2026:

Order	Status	Days in status	Flag
O-101	Delivered	11
O-102	Shipped	4
O-103	Packed	5	Stuck
O-104	New	1
O-105	Cancelled	9
O-106	"shipped " (typed, trailing space)	2	Not on list
O-107	(blank)	(blank)	No status

Counts: one order in each of the five stages (New 650, Packed 500, Shipped 800, Delivered 1,200, Cancelled 300), plus 1 not on the list and 1 with no status. 7 accounted for out of 7 orders, so the check said OK.

The typed "shipped " is the reason for K11. COUNTIF ignores capital letters, but not the trailing space, so that order would have been missing from the Shipped count without the check. Retype it from the dropdown and it moves into Shipped.

7. Test it before you trust it

Add rows you know the answer to, then confirm:

a status pasted in with a trailing space (should say "Not on list")
an order with no status (should say "No status" and count in K12)
a Packed order with a status date 3+ days old (should say "Stuck" and turn red)

Then delete them.

Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker

About me, and a disclosure: I'm Ashish, a Kolkata-based Excel and Google Sheets builder with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.

How to Set Up Payment Due Date Reminders in a Spreadsheet

Ashish Srivastava — Wed, 21 Oct 2026 15:47:32 +0000

Subtitle: Due dates that skip weekends and holidays, working days left, and a "Remind now" flag with WORKDAY and NETWORKDAYS

Written with AI assistance. I edited it. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

Chasing an invoice after it's late is awkward. A polite nudge two or three days before the due date is easy, and it often means you never have to chase at all. The problem is remembering which invoices are close.

Here's a small sheet that does the remembering. It works out each due date from the payment terms, moves it off weekends and holidays, counts the working days left, and flags the ones to remind today. WORKDAY and NETWORKDAYS exist in every current version of Excel and in Google Sheets.

1. The invoice list

Invoices from row 5: A Invoice date, B Customer, C Amount, D Terms (days, e.g. 30), E Paid (type Y when the money arrives). Put an As of date in B2. =TODAY() works; I used a fixed date so the results below don't change.

List your holidays in L5:L10 (one date per cell). Leave spare blank cells: the formulas ignore them.

2. The due date

F5, the plain due date:

=IF(OR(A5="",D5=""),"",A5+D5)

G5, moved to the next working day if it lands on a weekend or holiday:

=IF(F5="","",WORKDAY(F5-1,1,$L$5:$L$10))

The F5-1 then +1 working day trick is the useful bit: a weekday due date stays the same, but a Saturday becomes Monday, and a holiday becomes the next working day. Format F and G as dates.

3. Days left, in calendar and working days

H5, calendar days left (blank once paid):

=IF(OR(G5="",E5="Y"),"",G5-$B$2)

I5, working days left (negative when overdue):

=IF(H5="","",IF(H5>=0,NETWORKDAYS($B$2,G5,$L$5:$L$10)-1,-(NETWORKDAYS(G5,$B$2,$L$5:$L$10)-1)))

NETWORKDAYS counts both end dates, so the -1 makes "due today" read 0. The two branches keep the sign right on each side of the due date. Format H and I as Number, not Date, or a 5 shows up as a date in 1900.

4. The reminder column

J5:

=IF(E5="Y","Paid",IF(G5="","No terms",IF(H5<0,"Overdue",IF(I5<=3,"Remind now","OK"))))

Change the 3 to suit your customers. Then add a conditional formatting rule on the row (Format → Conditional formatting, custom formula =$J5="Remind now") to colour it, so the list reads at a glance.

5. A summary for Monday morning

Cell	Formula
N5: Remind now	`=COUNTIF(J5:J104,"Remind now")`
N6: Overdue	`=COUNTIF(J5:J104,"Overdue")`
N7: Amount overdue	`=SUMIFS(C5:C104,J5:J104,"Overdue")`
N8: Amount to remind about	`=SUMIFS(C5:C104,J5:J104,"Remind now")`
N9: Invoices with no terms	`=COUNTIF(J5:J104,"No terms")`

N9 is the check. An invoice with no terms has no due date, so it would never be flagged. If N9 isn't 0, fill in the terms.

6. What the sample showed

Seven made-up invoices, as of Wednesday 21 Oct 2026, with Friday 23 Oct listed as a holiday:

Customer	Due	Due (working day)	Days left	Working days left	Reminder
Asha Traders	1 Oct	1 Oct	(blank)	(blank)	Paid
Bose & Co	Sat 10 Oct	Mon 12 Oct	-9	-7	Overdue
Dutta Mart	Sat 24 Oct	Mon 26 Oct	5	2	Remind now
Hari Mills	22 Oct	22 Oct	1	1	Remind now
Ezra Textiles	21 Oct	21 Oct	0	0	Remind now
Chandra Stores	9 Nov	9 Nov	19	12	OK
Faiz Stores	(blank)	(blank)	(blank)	(blank)	No terms

Summary: 3 to remind (500 + 650 + 300 = 1,450), 1 overdue (800), 1 with no terms.

Dutta Mart is the interesting row: five calendar days left looks comfortable, but with a weekend and a holiday in between, it's only two working days.

7. Test it before you trust it

Add rows you know the answer to, then confirm:

a due date on a Saturday (should move to Monday)
a due date on one of your holidays (should move to the next working day)
an invoice due today (should show 0 and "Remind now")
an invoice with blank terms (should show "No terms" and count in N9)

Then delete them.

ChatGPT prompts for small business marketing: 12 fill-in templates for posts, product copy and offers

Ashish Srivastava — Wed, 21 Oct 2026 08:42:08 +0000

Asking ChatGPT to "write a post for my shop" usually gets you something generic. The fix isn't a magic phrase; it's giving the model the same details you'd give a freelance copywriter: who you sell to, what makes you different, the tone you want and the format you need. Below are 12 fill-in prompts for everyday small business marketing, plus a simple way to make the results sound like you.

Note: AI output can be wrong or overconfident. Read everything before you post it, check any facts, prices or claims yourself, and never publish health, legal or financial claims you can't back up.

Step 1: Write a reusable "business brief"

Before any prompt, keep a short brief in a notes file and paste it at the top of every chat:

Business: [what you sell], based in [city/online]
Customers: [who buys, e.g. working parents aged 25-40]
Why us: [2-3 real differences, e.g. same-day dispatch, handmade]
Tone: [e.g. friendly, plain, no hype, no emojis]
Avoid: [words or claims you never want used]

This single step does more for quality than any clever prompt. Fill the brackets with real details, not adjectives like "high quality".

Step 2: Social media posts

1. Product spotlight post

Using the brief above, write 3 short Instagram captions (under 60 words each) for [product]. Mention [one specific detail]. End each with a simple call to action: [visit link in bio / DM to order].

2. Behind-the-scenes post

Write a 4-sentence post about how we [make/pack/source] [product]. Keep it honest and specific, no exaggeration. Suggest one photo idea to go with it.

3. Weekly content ideas

Give me 10 post ideas for next week for a [type of business]. Mix: 3 product, 3 helpful tips, 2 behind-the-scenes, 2 questions to the audience. One line each.

4. Turning a customer question into a post

A customer asked: "[question]". Write a helpful short post answering it in plain language, then a one-line mention of [product] at the end.

Step 3: Product descriptions

5. Product page description

Write a product description for [product] in 3 parts: a 1-sentence hook, 4 bullet points of features with the benefit of each, and a short "good to know" line covering [size/care/delivery]. Under 120 words. Only use these facts: [list facts].

The "only use these facts" line matters. Without it, the model may invent materials, sizes or guarantees.

6. Marketplace version

Rewrite this description for [Amazon/Etsy/Meesho/other] style: a title under [N] characters with the main search phrase "[phrase]" near the start, then 5 bullets. Description: [paste].

7. Shorter variants for testing

Give me 5 alternative one-line product headlines for [product], each with a different angle: price, speed, quality, gift, problem solved.

Step 4: Offers and promotions

8. Festival or seasonal offer

We're running [X% off / free delivery] on [products] from [date] to [date] for [Diwali/end of season/other]. Write: 1 WhatsApp broadcast message (under 50 words), 1 Instagram caption, and 1 banner headline (under 8 words). Mention the end date clearly.

9. Bundle pitch

We're selling [item A] + [item B] together for [price] instead of [separate price]. Write a short, honest explanation of who this bundle suits and why, in under 70 words.

10. Win-back message

Write a friendly message to customers who haven't ordered in [N] months. Offer [incentive]. No guilt-tripping, under 60 words.

Step 5: Reviews and trust

11. Asking for a review

Write a short, polite message asking a customer who bought [product] last week for a review. Make it easy to ignore, with no pressure, and include where to leave it: [link/platform].

Never ask AI to write reviews for you. Fake reviews break most platforms' rules and the law in many places.

12. Replying to a negative review

A customer left this review: "[paste]". Write a calm, non-defensive public reply under 80 words that acknowledges the issue, says what we'll do [action], and invites them to contact us at [channel].

Step 6: Make the output sound like you

Three habits help:

Give an example. Paste one or two posts you've written yourself and add: "Match the tone and length of these."
Ask for options, then combine. Request 3 to 5 versions and stitch the best lines together.
Edit with follow-ups. "Shorter." "Less salesy." "Remove the exclamation marks." "Use simpler words." Small follow-ups beat starting over.

Step 7: Keep your prompts organised

Once you have prompts that work, save them. A simple spreadsheet works well: columns for Category (Social, Product, Offer, Reviews), Prompt name, Prompt text, and Notes on what to fill in. Filter by category when you need one. Over a few weeks you'll build a small library that suits your business, and writing a week of posts takes minutes instead of an evening.

Common mistakes

No details. Generic input gives generic output. Fill every bracket.
Posting without reading. Check prices, dates and any claim before it goes out.
Letting it invent facts. Tell it to use only the facts you list.
Same tone everywhere. A WhatsApp broadcast and a product page need different lengths and styles; say which one you want.

Wrapping up

Good small business marketing prompts are really just good briefs: who you are, who you sell to, what you want and in what format. Start with the business brief, use the 12 templates above, and keep the ones that work.

This article was drafted with AI tools. The prompts are general starting points; always review AI output before using it.

Freelancer invoice tracker in Excel: track clients, projects and who still owes you

Ashish Srivastava — Wed, 21 Oct 2026 08:41:58 +0000

Most freelancers start with invoices scattered across email, a notes app and a bank statement. That works until you have five clients and can't say who has paid. A simple freelancer invoice tracker in Excel fixes that: one list of clients, one list of projects, one log of invoices, and a summary that shows what's outstanding. This guide builds one step by step.

Note: this covers spreadsheet layout and formulas only. How you charge tax, what an invoice must show and how you report income depend on where you live. It is not tax or legal advice.

Step 1: A Clients sheet

Make a sheet called Clients with these columns:

Col	Field
A	Client name
B	Contact person
C	Email
D	Payment terms (days), e.g. `15` or `30`
E	Currency
F	Notes

Select the range and press Ctrl+T to make it an Excel Table, then name it tblClients under Table Design > Table Name. Tables grow on their own, so new clients appear in dropdowns without editing ranges.

Keep one row per client. If the same client appears as "Acme" and "Acme Pvt Ltd", your totals will split, so pick one spelling and stick to it.

Step 2: A Projects sheet

On a Projects sheet:

Col	Field
A	Project ID, e.g. `P-001`
B	Client (dropdown)
C	Project name
D	Billing type: `Fixed` or `Hourly`
E	Rate or fixed fee
F	Start date
G	Status: `Active`, `Paused`, `Done`

For the Client dropdown, select column B and use Data > Data Validation > List with the source =INDIRECT("tblClients[Client name]"). Do the same for Status with a typed list: Active,Paused,Done.

Make this a Table too (tblProjects).

Step 3: An optional Time Log

If you bill by the hour, add a TimeLog sheet: A = date, B = project ID, C = hours, D = description. Then on the Projects sheet add column H, Hours logged:

=SUMIFS(tblTime[Hours],tblTime[Project ID],[@[Project ID]])

And column I, Billable so far:

=IF([@[Billing type]]="Hourly",[@Hours logged]*[@Rate],[@Rate])

Now each hourly project shows what you could invoice today.

Step 4: The Invoices log

This is the core of the tracker. Make an Invoices sheet:

Col	Field
A	Invoice no.
B	Invoice date
C	Client (dropdown)
D	Project ID
E	Amount
F	Due date
G	Amount paid
H	Paid date
I	Balance
J	Status
K	Days overdue

Fill the due date from the client's payment terms:

F2: =IF(B2="","",B2+IFERROR(INDEX(tblClients[Payment terms (days)],MATCH(C2,tblClients[Client name],0)),30))

If the client isn't found, it falls back to 30 days.

Balance:

I2: =IF(E2="","",E2-N(G2))

Status:

J2: =IF(E2="","",IF(I2<=0,"Paid",IF(TODAY()>F2,"Overdue",IF(N(G2)>0,"Part-paid","Open"))))

Days overdue:

K2: =IF(J2="Overdue",TODAY()-F2,"")

Turn it into a Table (tblInvoices) so these formulas fill down automatically for every new row.

Step 5: Highlight what needs chasing

Select the Status column and add Home > Conditional Formatting > Highlight Cells Rules > Text that Contains:

Overdue â†’ light red fill
Part-paid â†’ light yellow fill
Paid â†’ light green fill

For a stronger signal, select the whole table and add a formula rule =$K2>30 with bold red text. Anything more than a month late jumps out.

Step 6: A one-glance Summary sheet

Make a Summary sheet with a client list in column A (you can type it, or in Excel 365/2021 use =SORT(UNIQUE(tblInvoices[Client]))). Then:

B2 (Invoiced):     =SUMIFS(tblInvoices[Amount],tblInvoices[Client],A2)
C2 (Received):     =SUMIFS(tblInvoices[Amount paid],tblInvoices[Client],A2)
D2 (Outstanding):  =B2-C2
E2 (Overdue):      =SUMIFS(tblInvoices[Balance],tblInvoices[Client],A2,tblInvoices[Status],"Overdue")

Above the table, add three headline numbers:

Total outstanding:  =SUM(tblInvoices[Balance])
Overdue invoices:   =COUNTIF(tblInvoices[Status],"Overdue")
Paid this month:    =SUMIFS(tblInvoices[Amount paid],tblInvoices[Paid date],">="&EOMONTH(TODAY(),-1)+1,tblInvoices[Paid date],"<="&EOMONTH(TODAY(),0))

UNIQUE and SORT need Excel 365 or Excel 2021. The other formulas work in older desktop versions too.

Step 7: A weekly 10-minute routine

The tracker only helps if it stays current. A simple habit:

Every time you send an invoice, add a row to Invoices straight away.
Every time money lands, fill in Amount paid and Paid date.
Once a week, filter Status to Overdue and send a polite reminder to each client on the list.
Once a month, check the Summary sheet for clients who are slow to pay, and consider shorter terms or an advance for their next project.

Common mistakes

Typing client names by hand. Use the dropdown so the SUMIFS totals match.
Overwriting formulas. Lock the Balance, Status and Days overdue columns with Review > Protect Sheet, leaving the input columns unlocked.
Mixing currencies in one total. If you bill in more than one currency, add a converted-amount column or keep a separate summary per currency.
Deleting paid invoices. Keep them. Your paid history is what tells you which clients are reliable.

Wrapping up

With four sheets (Clients, Projects, Invoices and Summary) you get a freelancer invoice tracker that shows who owes you, how late they are, and what came in this month, without any paid software. Start with the Invoices log alone if that's all you need, and add the rest as your client list grows.

This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax or legal advice.

Salary slip format in Excel (India): a printable layout you fill from one register

Ashish Srivastava — Wed, 21 Oct 2026 03:40:05 +0000

If you run a small shop, office or studio in India, staff will sooner or later ask for a salary slip, for a bank loan, a rental agreement or a visa file. You don't need payroll software for that. A clear salary slip format in Excel, filled automatically from a simple monthly register, does the job. This guide focuses on the slip itself: what to put on it, how to lay it out and how to fill it with formulas.

Note: which deductions apply (PF, ESI, professional tax, TDS and so on) depends on your state, headcount and current rules. This article covers spreadsheet layout only. It is not tax, payroll or legal advice; check the rules with an accountant or the official sources.

What a typical salary slip shows

Most slips have the same five blocks:

Header: business name, address, "Salary slip for October 2026"
Employee details: name, employee ID, designation, department, date of joining, bank account (last 4 digits only), PAN or UAN if you record them
Attendance: working days in the month, days paid, leave taken
Earnings and deductions: two columns side by side
Net pay: in figures and in words, plus a line such as "This is a computer-generated slip"

Step 1: The data the slip reads from

Keep two sheets:

Employees: A = Emp ID, B = Name, C = Designation, D = Department, E = Date of joining, F = Bank (last 4)
Register: one row per employee per month. A = Month (first day, e.g. 01-10-2026), B = Emp ID, C = Working days, D = Days paid, E = Basic, F = HRA, G = Other allowances, H = Overtime/bonus, I = Gross, Jâ€“L = deductions, M = Total deductions, N = Net pay

The slip never holds numbers of its own. It only looks them up, so a mistake fixed in the register fixes the slip.

Step 2: Lay out the slip

On a Slip sheet, use columns B to E on an A4 portrait page:

B2:E2 merged: =Settings!B2 (business name), bold, centred
B3:E3 merged: address
B4:E4 merged: ="Salary slip for "&TEXT($C$6,"mmmm yyyy")

Two inputs at the top:

C6: Month (a dropdown of months from the register)
C7: Emp ID (a dropdown from the Employees list)

Then the employee details in rows 9â€“12, labels in B and D, values in C and E.

Step 3: Pull employee details

C9:  =IFERROR(INDEX(Employees!$B:$B,MATCH($C$7,Employees!$A:$A,0)),"")
E9:  =IFERROR(INDEX(Employees!$C:$C,MATCH($C$7,Employees!$A:$A,0)),"")
C10: =IFERROR(INDEX(Employees!$D:$D,MATCH($C$7,Employees!$A:$A,0)),"")
E10: =IFERROR(TEXT(INDEX(Employees!$E:$E,MATCH($C$7,Employees!$A:$A,0)),"dd-mm-yyyy"),"")
C11: =IFERROR("XXXX"&INDEX(Employees!$F:$F,MATCH($C$7,Employees!$A:$A,0)),"")

Step 4: Pull the month's figures

Each money field needs a lookup on two conditions: the month and the employee. SUMIFS is the simplest way and works in older Excel too:

=SUMIFS(Register!$E:$E,Register!$A:$A,$C$6,Register!$B:$B,$C$7)

That gives Basic. Change only the first range for each field: $F:$F for HRA, $G:$G for allowances, $H:$H for overtime, $J:$J to $L:$L for deductions, $D:$D for days paid.

Lay earnings in B:C and deductions in D:E on the same rows, so the slip reads across:

Earnings	Amount	Deductions	Amount
Basic	â€¦	Deduction 1	â€¦
HRA	â€¦	Deduction 2	â€¦
Allowances	â€¦	Advance recovered	â€¦
Overtime / bonus	â€¦
Gross	`=SUM(C15:C18)`	Total deductions	`=SUM(E15:E17)`

Name the deduction rows to match what you actually deduct. Leave out rows that don't apply to you rather than showing zeros.

Step 5: Net pay in figures and words

C21: =C19-E19
B22: ="Net pay: Rupees "&TEXT(C21,"#,##,##0")&" only"

The #,##,##0 format gives Indian lakh-style grouping (1,25,000). Excel has no built-in function for Indian number words, so type the words if you need them in full.

Step 6: Catch blanks before printing

Add a check cell outside the print area:

=IF(COUNTIFS(Register!$A:$A,$C$6,Register!$B:$B,$C$7)=0,"No register row for this month",
 IF(COUNTIFS(Register!$A:$A,$C$6,Register!$B:$B,$C$7)>1,"Duplicate rows: check register","OK"))

Use Conditional Formatting to turn it red when it isn't "OK".

Step 7: Print a slip for each person

Select B2:E24, Page Layout > Print Area > Set Print Area.
Page Setup > Fit to 1 page, portrait, A4.
Pick the month in C6, then change C7 for each employee and File > Export > Create PDF.
Name files like Slip_2026-10_E003.pdf.

For a few staff this takes a couple of minutes a month.

Tips

Protect the slip sheet (Review > Protect Sheet, leaving C6 and C7 unlocked) so formulas don't get typed over.
Don't store full bank or ID numbers in a file you email around.
Keep old register rows. They're your history if anyone asks for a past slip. This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax, payroll or legal advice.

GST invoice format in Excel: a simple layout with automatic CGST/SGST or IGST

Ashish Srivastava — Wed, 21 Oct 2026 03:39:50 +0000

Plenty of small businesses in India make GST invoices in Excel instead of paying for billing software. That works fine if the sheet is laid out sensibly: one place for your details, one list of customers and items, and an invoice sheet that pulls from both. This guide walks through a practical GST invoice format in Excel, step by step.

Note: what a GST invoice must contain, which rate applies and how place of supply works depend on your business and current rules. This article covers spreadsheet layout and formulas only. It is not tax or legal advice; check the requirements with your CA or the official GST sources.

Step 1: A Settings sheet

Make a sheet called Settings and fill in the details that appear on every invoice:

Cell	Value
B2	Business name
B3	Address
B4	Your state
B5	Your GSTIN
B6	Invoice prefix, e.g. `INV-`
B7	Bank details for payment

Every invoice formula points at these cells, so if your address changes you edit one cell.

Step 2: Customers and Items lists

On a Customers sheet: A = customer name, B = GSTIN (blank if unregistered), C = state, D = billing address.

On an Items sheet: A = item or service, B = HSN/SAC code, C = unit price, D = GST rate (for example 18%).

Turn both into Excel Tables (Ctrl+T). Tables grow automatically, so new customers and items show up in your dropdowns without editing ranges.

Keep state names consistent. The tax split compares text, so "Karnataka" and "Karnatka" count as different states. A dropdown from a fixed state list avoids this.

Step 3: The invoice header

On the Invoice sheet, lay out the top block:

Seller block: =Settings!B2, =Settings!B3, ="GSTIN: "&Settings!B5
Invoice number (F3): type a running number in F2 (e.g. 41) and use =Settings!B6&TEXT(F2,"0000") to get INV-0041
Invoice date (F4): type the date; format as dd-mm-yyyy
Customer (B8): a dropdown via Data > Data Validation > List pointing at the customer name column

Pull the customer's details from the list:

B9:  =IFERROR(INDEX(Customers!$D:$D,MATCH($B$8,Customers!$A:$A,0)),"")
B10: =IFERROR(INDEX(Customers!$B:$B,MATCH($B$8,Customers!$A:$A,0)),"")
B11: =IFERROR(INDEX(Customers!$C:$C,MATCH($B$8,Customers!$A:$A,0)),"")

B9 is the address, B10 the customer GSTIN and B11 the place of supply (state).

Step 4: The line-item table

Start the item rows at row 15 with these columns:

Col	Field
A	S. No.
B	Item (dropdown from Items)
C	HSN/SAC
D	Qty
E	Rate
F	Taxable value
G	GST rate
H	CGST
I	SGST
J	IGST
K	Line total

Lookups for code, rate and GST rate:

C15: =IF($B15="","",IFERROR(INDEX(Items!$B:$B,MATCH($B15,Items!$A:$A,0)),""))
E15: =IF($B15="","",IFERROR(INDEX(Items!$C:$C,MATCH($B15,Items!$A:$A,0)),""))
G15: =IF($B15="","",IFERROR(INDEX(Items!$D:$D,MATCH($B15,Items!$A:$A,0)),""))
F15: =IF($B15="","",ROUND(D15*E15,2))

Step 5: Let Excel choose CGST/SGST or IGST

Compare your state with the customer's state. Same state: split the tax in half. Different state: all of it is IGST.

H15: =IF($B15="","",IF($B$11=Settings!$B$4,ROUND(F15*G15/2,2),0))
I15: =IF($B15="","",IF($B$11=Settings!$B$4,ROUND(F15*G15/2,2),0))
J15: =IF($B15="","",IF($B$11<>Settings!$B$4,ROUND(F15*G15,2),0))
K15: =IF($B15="","",F15+H15+I15+J15)

Copy row 15 down for as many lines as you need (say 15 rows). These are plain IF and INDEX/MATCH formulas, so they work in older Excel versions as well as Excel 365.

Step 6: Totals and amount in words

Below the items:

Taxable total: =SUM(F15:F29)
CGST total:    =SUM(H15:H29)
SGST total:    =SUM(I15:I29)
IGST total:    =SUM(J15:J29)
Grand total:   =ROUND(SUM(K15:K29),0)

Excel has no built-in function for amounts in Indian words. Either type the words, or show the figure with lakh-style grouping:

="Rupees "&TEXT(K31,"#,##,##0")&" only"

Add a short tax summary by rate if you use more than one rate, with SUMIF:

=SUMIF($G$15:$G$29,18%,$F$15:$F$29)

Step 7: Keep a register of every invoice

Before you clear the invoice for the next one, copy its key values (number, date, customer, taxable value, CGST, SGST, IGST, total) into an Invoices register sheet, one row per invoice. That register is what you'll use for monthly totals and for chasing payments. A pivot table on it (Rows: month; Values: sums of each tax column) gives a quick monthly summary.

Step 8: Print or save as PDF

Select the invoice area, Page Layout > Print Area > Set Print Area, set Fit to 1 page wide, and use File > Export > Create PDF. Name the file with the invoice number so it's easy to find later.

Common mistakes

Hard-coding 18% in formulas. Keep rates in the Items list.
Typing customer details by hand each time. That's where GSTIN typos creep in.
Overwriting the last invoice without logging it. Always add it to the register first.
Rounding only the grand total. Round each tax line too, so totals match line by line.
Reusing invoice numbers. A COUNTIF on the register's number column (=COUNTIF(A:A,A2)>1) flags duplicates. This article was drafted with AI tools. It's general spreadsheet guidance for Excel, not tax or legal advice.

How to Build a Monthly Sales Summary with SUMIFS (No Pivot Table)

Ashish Srivastava — Wed, 21 Oct 2026 03:34:55 +0000

Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

Pivot tables are great until you need the summary to sit in a fixed layout, update the moment a row is added, or be readable by someone who's never clicked "Refresh". A small SUMIFS table does the same job in plain formulas you can audit cell by cell.

Everything here uses standard functions available in both Excel and Google Sheets (SUMIFS, COUNTIFS, EDATE, SUM, SUMPRODUCT, ISNUMBER), with no macros.

1. The order list

One row per order, from row 5, columns A–D: Date, Product, Amount, Status (Completed or Refunded). Use a dropdown for Status so it's always spelled the same way.

2. The month column

In G5, type the first day of your first month, e.g. 1 Jan 2026. Below it (G6, fill down for as many months as you need):

=EDATE(G5,1)

EDATE adds whole months, so you always land on the 1st, even after February. Format the column as mmm yyyy so it reads "Jan 2026" while staying a real date underneath.

3. Sales and orders per month

Sales (H5, fill down):

=SUMIFS($C$5:$C$204,$A$5:$A$204,">="&G5,$A$5:$A$204,"<"&EDATE(G5,1),$D$5:$D$204,"Completed")

Orders (I5):

=COUNTIFS($A$5:$A$204,">="&G5,$A$5:$A$204,"<"&EDATE(G5,1),$D$5:$D$204,"Completed")

The window is "on or after the 1st, and before the 1st of next month". That's why 31 January counts in January and 1 February counts in February. It also stays right if your dates ever carry a time of day, which a <= end-of-month test would get wrong.

4. Average order, refunds, running total, change

Column	Formula (row 5)	Note
J: Avg order	`=IF(I5=0,"",H5/I5)`	blank instead of #DIV/0! in an empty month
K: Refunds	same as Sales, with `"Refunded"` instead of `"Completed"`	kept separate, so sales stay sales
L: Running total	`=SUM($H$5:H5)`	the locked start makes it grow as you fill down
M: vs last month (from row 6)	`=IF(H5=0,"",H6/H5-1)`	format as %; blank when last month was zero

5. The check cells (the part pivot tables don't give you)

All completed sales in the list (H12):

=SUMIFS($C$5:$C$204,$D$5:$D$204,"Completed")

Check (H13):

=IF(ROUND(SUM(H5:H10)-H12,2)=0,"OK","CHECK")

If the months don't add up to the whole list, some sales fell outside every month. There are two usual reasons, so count both:

Dates typed as text (H14). These look like dates but no date test can match them:

=SUMPRODUCT(($A$5:$A$204<>"")*NOT(ISNUMBER($A$5:$A$204)))

Dates outside your months (H15):

=COUNTIFS($A$5:$A$204,"<"&G5)+COUNTIFS($A$5:$A$204,">="&EDATE(G10,1))

In my sample list I planted one of each: a date pasted as the text 2026-03-15, and an order dated 1 July in a January–June summary. The check showed CHECK, and each counter showed 1. Retype the text date as a real date and extend the months, and it goes back to OK.

6. What the sample came out as

Fourteen made-up orders, Jan–Jun 2026:

Month	Sales	Orders	Avg	Refunds	Running
Jan	66	2	33	0	66
Feb	66	2	33	27	132
Mar	66	2	33	0	198
Apr	93	3	31	0	291
May	0	0	(blank)	0	291
Jun	39	1	39	27	330

March shows only 2 orders because the text-date order on 15 March was skipped, which is exactly what the check cell is there to catch. These are demo numbers, not anyone's real sales.

7. Test it before you trust it

Add rows you know the answer for, and confirm each lands where it should:

the last day of a month and the first day of the next
a refunded order (goes to Refunds, not Sales)
a month with no orders (shows 0 and a blank average, not an error)
one date typed as text, which should trip the check

Then delete them and use it with real data.

About me, and a disclosure: I'm Ashish, based in Kolkata, with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.

How to Catch Duplicate Orders and Misspelled Customer Names in a Spreadsheet

Ashish Srivastava — Wed, 21 Oct 2026 03:34:39 +0000

Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).

Most wrong spreadsheet totals aren't formula bugs. They're data bugs: the same order typed twice, "Asha Traders" in one row and "Asha Trader" in the next, or a stray space at the end of a name. Your per-customer totals then quietly split in two, and nothing turns red.

Here are three checks you can add to any order list. They use only standard functions that exist in both Excel and Google Sheets (TRIM, COUNTIF, SUMPRODUCT, IF), with no macros.

1. The order list

One row per order, starting at row 5:

Column	What goes in it
A	Order ID
B	Order date
C	Customer (as typed)
D	Amount
E	Customer, cleaned (formula)
F	Repeat ID (formula)
G	Order key (formula)
H	Possible duplicate (formula)
I	Name check (formula)

Put your list of real customer names in K5:K20, spelled once, correctly.

2. Clean the name: TRIM

=IF(A5="","",TRIM(C5))

TRIM removes spaces at the start and end and squeezes double spaces in the middle down to one. So " Chandra Stores " becomes "Chandra Stores" and "Bose & Co" becomes "Bose & Co". Point every total or lookup at column E, never at the raw column C.

3. Same order ID twice

=IF(A5="","",IF(COUNTIF($A$5:A5,A5)>1,"Repeat ID",""))

The trick is the range $A$5:A5: the start is locked and the end grows as you fill down. Each row counts only the rows above and including itself, so the first ORD-202 stays clean and only the second one is flagged. You know which row to look at, instead of both rows lighting up.

4. Same order typed twice under different IDs

Sometimes the ID is different but it's clearly the same order: same customer, same day, same amount. Build a key (G5):

=IF(A5="","",E5&"|"&B5&"|"&D5)

The date joins as its underlying number (e.g. 46266), which is fine for matching. Then flag the repeat (H5):

=IF(A5="","",IF(SUMPRODUCT(--($G$5:G5=G5))>1,"Possible duplicate",""))

Why SUMPRODUCT instead of COUNTIF here? COUNTIF treats * and ? in the text as wildcards, and long text can trip it up. A plain = comparison avoids both. It's also case-insensitive, so asha traders and Asha Traders with the same date and amount get flagged.

It says possible on purpose. Two genuine orders can match, so treat the flag as "look at this", not "delete this". Same customer and same day but a different amount is not flagged.

5. Names that aren't on your list

=IF(A5="","",IF(COUNTIF($K$5:$K$20,E5)=0,"Not in list",""))

This catches "Asha Trader" (missing s) before it becomes a separate customer in your totals.

Better still, stop typos at the door with a dropdown on column C:

Excel: select the column → Data → Data Validation → Allow: List → Source =$K$5:$K$20.
Google Sheets: select the column → Data → Data validation → Add rule → Dropdown (from a range) → K5:K20.

Keep the "Not in list" check anyway. It catches anything pasted in, and rows typed before the dropdown existed.

6. A small summary and one check cell

Cell	Formula
Repeat IDs	`=COUNTIF($F$5:$F$104,"Repeat ID")`
Possible duplicates	`=COUNTIF($H$5:$H$104,"Possible duplicate")`
Names not in list	`=COUNTIF($I$5:$I$104,"Not in list")`
Total, all rows	`=SUM($D$5:$D$104)`
Total, flagged rows left out	`=SUMPRODUCT($D$5:$D$104($F$5:$F$104="")($H$5:$H$104=""))`
Check (N10)	`=IF(N5+N6+N7=0,"OK","CHECK")`

(N5, N6 and N7 are the first three cells.) The two totals side by side show how much money is sitting in suspicious rows.

On my ten sample orders, each check caught exactly the row it should:

Repeat IDs: 1
Possible duplicates: 1
Names not in list: 1
Total of all rows: 6,950; with the flagged rows left out: 5,450
The check cell showed CHECK

Your numbers will differ. What matters is that each planted problem gets caught and nothing else does.

7. Test it with planted mistakes

Before trusting it, add rows you know are wrong and confirm each gets flagged:

Planted row	Expected
Name with extra spaces	cleaned, no flag
Same order ID twice	second one: Repeat ID
Same customer, date and amount, different capitals	second one: Possible duplicate
Same customer and date, different amount	no flag
Misspelled customer	Not in list
Completely blank row	stays blank

Then delete the planted rows and fill the formulas down 100 rows in advance.

How to make a 30-day social media content calendar in Excel

Ashish Srivastava — Tue, 20 Oct 2026 18:23:14 +0000

Most small shops don't stop posting because they lack ideas. They stop because each day starts from a blank page. A 30-day calendar in Excel fixes that: you plan themes once, and every day already has a prompt waiting. Here's how to build one where you change one start date and everything else follows.

Settings

On a sheet called Settings:

B1: Start date (e.g. 01-11-2026)
A4:A10: seven weekly themes, one per weekday (for example: Product spotlight, Behind the scenes, Customer question, Tip, Offer, Story, Weekly recap)

Sheet: Calendar

Headers in row 1: Day, Date, Weekday, Theme, Post idea, Caption no., Caption, Platform, Format, Status, Hashtags.

Day (A2:A31): type 1 in A2, then =A2+1 in A3 and fill down to A31.

Date (B2):

=Settings!$B$1+A2-1

Weekday (C2):

=TEXT(B2,"dddd")

Theme (D2), cycling through your seven themes:

=INDEX(Settings!$A$4:$A$10,MOD(A2-1,7)+1)

Change the start date and every date, weekday and theme moves with it.

A caption bank

On a sheet called Captions, column A = number (1, 2, 3â€¦), column B = theme, column C = caption text with blanks to fill, e.g. "Our [product] is back in stock. Which colour should we restock first?"

On the Calendar, Caption no. (F2) can start as the day number, =A2, and you overwrite it when you want a different one. Then Caption (G2):

=IFERROR(INDEX(Captions!$C:$C,MATCH(F2,Captions!$A:$A,0)),"")

Want the caption to match the day's theme automatically? In Excel 365/2021:

=IFERROR(INDEX(FILTER(Captions!$C$2:$C$500,Captions!$B$2:$B$500=D2),ROUNDUP(A2/7,0)),"")

This picks the 1st caption for that theme in week 1, the 2nd in week 2, and so on.

Dropdowns

Platform: Instagram, Facebook, WhatsApp Status, YouTube Shorts, Other
Format: Photo, Carousel, Reel, Story, Text
Status: Idea, Drafted, Scheduled, Posted, Skipped

Select each column, Data > Data Validation > List, and type the values separated by commas.

Make today stand out

Select A2:K31, Home > Conditional Formatting > New Rule > Use a formula:

=$B2=TODAY()

Pick a light fill. A second rule, =AND($B2<TODAY(),$J2<>"Posted",$J2<>"Skipped"), in light red, shows days you missed.

Track how you're doing

A small summary above or beside the calendar:

Posted:  =COUNTIF(J2:J31,"Posted")
Planned: =COUNTA(B2:B31)
Rate:    =IF(COUNTIF(B2:B31,"<="&TODAY())=0,"",COUNTIF(J2:J31,"Posted")/COUNTIF(B2:B31,"<="&TODAY()))

The rate only counts days that have already arrived, so it doesn't punish you for the future.

If you want to note results, add Reach and Messages columns and fill them a day or two after posting. After a month, sort by Messages to see which themes start conversations.

Batch it

The calendar works best if you spend one hour a week on it: on Sunday, read the next seven rows, take the photos, write the captions, and set Status to Drafted or Scheduled. Then each day is just "post the thing".

Reusing it next month

When the 30 days are up, right-click the Calendar tab, Move or Copy > Create a copy, and rename the copy with the month (for example "Cal 2026-11"). On the new copy, change Settings!B1 to the next start date, clear the Status, Reach and Messages columns, and you're ready. If you keep notes of which caption numbers did well, overwrite the Caption no. column on the new copy to repeat the winners on different days. Old copies stay as a record, so you can see over a few months which themes and formats get replies and which ones you keep skipping.

Writing better fill-in captions

Captions with a question at the end ("Which one would you pick?") tend to start more conversations than plain announcements. Keep each caption short, put the blank where the product name goes, and write two or three versions for each theme so the calendar doesn't repeat itself every week. Twenty captions per theme is plenty to start.

Common mistakes

Typing dates by hand. One start date plus formulas means you can reuse the calendar every month.
Too many platforms. Start with one or two you actually use.
Hashtags pasted the same every day. Keep a few sets per theme in Settings and rotate them.
Deleting last month. Copy the sheet first; you can reuse what worked. This article was drafted with AI tools. It's general guidance, not marketing or legal advice.

How to track multiple income streams in one Excel dashboard

Ashish Srivastava — Tue, 20 Oct 2026 18:23:06 +0000

Once you have more than one small income source (a digital product, some freelance work, affiliate links, a newsletter) it gets hard to answer simple questions. Which one actually earns? Is this month better than last? Am I near my goal? Payment dashboards each show their own slice. One Excel workbook can pull it together.

Sheet 1: Streams

A short list of your income sources:

Col	Field
A	Stream name (e.g. "Template shop", "Client work")
B	Type (dropdown: Product, Service, Affiliate, Ads, Other)
C	Platform
D	Currency
E	Active? (Yes/No)

Name the stream list range: select A2:A50, type StreamList in the Name Box (left of the formula bar). You'll use it for dropdowns.

Sheet 2: Income log

One row per payout or sale:

Col	Field
A	Date
B	Stream (dropdown from StreamList)
C	Description
D	Currency
E	Gross amount
F	Fees
G	Net
H	Rate to base currency
I	Net in base currency
J	Month

Dropdown on B: Data > Data Validation > List, source =StreamList.

Formulas:

G2: =IF($A2="","",E2-N(F2))
I2: =IF($A2="","",G2*IF(H2="",1,H2))
J2: =IF($A2="","",TEXT(A2,"yyyy-mm"))

Type the rate yourself (what you actually received), and leave it blank when the currency already matches your base. Don't add USD and GBP amounts directly.

Sheet 3: Expenses (optional but honest)

Same shape: Date, Stream, Description, Amount in base currency, Month. Tools, ads and subscriptions often quietly eat a "passive" stream. Tracking them per stream shows real profit.

Sheet 4: Dashboard

Put the month you want to look at in B1 (e.g. 2026-10). List streams in A4 down. Then:

Income this month: =SUMIFS(Log!$I:$I,Log!$B:$B,$A4,Log!$J:$J,$B$1)
Expenses:          =SUMIFS(Exp!$D:$D,Exp!$B:$B,$A4,Exp!$E:$E,$B$1)
Profit:            =B4-C4
Share of income:   =IF(SUM($B$4:$B$20)=0,"",B4/SUM($B$4:$B$20))

Previous month for comparison, from the month in B1:

=TEXT(EDATE(DATEVALUE($B$1&"-01"),-1),"yyyy-mm")

Put that in B2 and copy the income formula pointing at B2 instead of B1. A change column, =IF(E4=0,"",B4/E4-1), formatted as %, shows growth per stream.

Goal tracking

In a Goals cell (say Dashboard!H1) type your monthly target. Then:

Progress: =IF($H$1=0,"",SUM(B4:B20)/$H$1)

Format as %, and add Conditional Formatting > Data Bars for a quick visual.

A chart that's actually useful

Make a pivot table from the Log: Rows = Month, Columns = Stream, Values = Sum of Net in base. Insert a stacked column chart from it. You'll see at a glance whether growth comes from one stream or several, and which months were carried by a single sale.

Year-to-date view

Monthly numbers bounce around, especially for products where one launch week can be most of the year. Add a year-to-date column to the Dashboard with the year in B3 (e.g. 2026):

=SUMIFS(Log!$I:$I,Log!$B:$B,$A4,Log!$A:$A,">="&DATE($B$3,1,1),Log!$A:$A,"<="&DATE($B$3,12,31))

Sort streams by this column at least once a quarter. It's the fairest way to decide where your next hours should go, and which stream has quietly stopped earning.

Keep payout proof

Add a Reference column to the Log for the payout or transaction ID. When a platform total and your bank don't agree, the reference lets you match them one by one instead of guessing.

Common mistakes

Logging gross only. Platform and payment fees can be significant; log them.
Mixing currencies. Always convert in a separate column with a rate you entered.
Calling it passive when it isn't. Add a "Hours spent" column to the log if you want to see income per hour.
Retyping stream names. Dropdowns keep the dashboard sums correct.

A monthly routine

On the 1st: export payouts from each platform, paste them into the Log, check the rates, change B1 to the new month. Fifteen minutes gives you one honest picture of all your income.
This article was drafted with AI tools. It's general spreadsheet guidance, not financial, tax or legal advice.

How to track export shipment documents and payments in Excel (for small exporters)

Ashish Srivastava — Tue, 20 Oct 2026 18:17:32 +0000

A small export order involves a lot of paperwork that has to match: proforma invoice, commercial invoice, packing list, shipping bill, bill of lading or airway bill, certificate of origin, and the payment terms you agreed. When you're handling your first few shipments, the risk isn't usually a single big mistake. It's a document nobody chased, or a payment date nobody noticed. A simple Excel tracker helps you see what's still open.

Note: which documents you need depends on the product, the destination, the Incoterm and your buyer's bank. This article covers spreadsheet mechanics only. It is not export, customs, tax or legal advice; confirm requirements with your CHA, freight forwarder, bank or the official sources.

Sheet 1: Shipments

One row per shipment:

Col	Field
A	Shipment ID (e.g. EXP-001)
B	Buyer
C	Country
D	Invoice no.
E	Invoice date
F	Currency
G	Invoice value
H	Incoterm (dropdown: EXW, FOB, CIF, DAPâ€¦)
I	Advance received
J	Balance due
K	Balance due date
L	Docs complete?
M	Payment status

Balance (J2):

=IF($A2="","",G2-N(I2))

Payment status (M2):

=IF($A2="","",IF(J2<=0,"Received",IF(AND(K2<>"",TODAY()>K2),"Overdue","Open")))

Keep currency as its own column and never add USD and EUR invoices together. Totals per currency use SUMIFS with a currency condition.

Sheet 2: Document checklist

This is the useful part. Make a long table with one row per shipment Ã— document:

Col	Field
A	Shipment ID
B	Document
C	Status (dropdown: Pending, Done, NA)
D	Date done
E	Note (who has it, file name)

For each new shipment, paste your standard document list (the 10â€“15 you normally need) and set the ones that don't apply to NA. "NA" matters: it's the difference between "we decided this isn't needed" and "we forgot".

Add Data Validation on column C: Data > Data Validation > List, Pending,Done,NA.

Linking the two sheets

Back on Shipments, column L counts what's still pending:

=IF($A2="","",COUNTIFS(Docs!$A:$A,$A2,Docs!$C:$C,"Pending"))

Then a readable label in a new column N:

=IF($A2="","",IF(COUNTIFS(Docs!$A:$A,$A2)=0,"No checklist",IF(L2=0,"Ready","HOLD: "&L2&" pending")))

The "No checklist" case catches a shipment where you forgot to add document rows at all, which would otherwise show 0 pending and look ready.

Colour column N with conditional formatting: text containing "HOLD" in red, "Ready" in green.

Costs and contribution

Add cost columns to Shipments: freight, insurance, CHA/forwarder charges, bank charges. Then:

Total cost:   =SUM(P2:S2)
Contribution: =IF($A2="","",G2-T2)

Keep costs in the same currency as the invoice, or add an explicit rate column you type yourself. Don't let a formula guess an exchange rate; the rate you actually got from the bank is the one that matters.

A weekly view

On a Start sheet, a few counts tell you where to look:

Shipments on HOLD:  =COUNTIF(Shipments!N:N,"HOLD*")
Overdue payments:   =COUNTIF(Shipments!M:M,"Overdue")
Due in next 7 days: =COUNTIFS(Shipments!K:K,">="&TODAY(),Shipments!K:K,"<="&TODAY()+7,Shipments!J:J,">0")

Reusing your document list

Typing 14 document names for every shipment gets old fast. Keep a DocList sheet with your standard list in column A and, in column B, a short note on when each one applies (for example "only if buyer asks", "only for LC payments"). For a new shipment, copy A2:A15, paste it into the Docs sheet, and fill the Shipment ID beside each row by typing it once and double-clicking the fill handle.

If you ship to two very different markets, keep two lists side by side (say "Standard" and "LC shipments") and copy the one that fits. Over time the notes column becomes your own record of what each buyer and bank actually asked for, which is worth more than any generic list.

Protect the formulas

Select the formula columns (balance, status, pending count, contribution), shade them light grey, then Format Cells > Protection and keep them locked while unlocking the input columns. Review > Protect Sheet with no password stops accidental overwrites without getting in your way.

Common mistakes

Invoice and packing list disagree on quantity or weight. Add a check column comparing them if you type both.
Ticking "Done" with no file. Use the Note column to record where the document is saved.
One sheet per shipment. It looks tidy but you can't count across shipments. Use one long checklist table.
Deleting old shipments. Filter them; next year's buyer often wants the same documents as last year's. This article was drafted with AI tools. It's general spreadsheet guidance, not export, customs, tax or legal advice.

How to track customer orders and payments in Excel (free template approach)

Ashish Srivastava — Tue, 20 Oct 2026 18:17:23 +0000

If you sell on WhatsApp, Instagram or at a stall, orders tend to live in chats and payments in your bank app. The question "who still owes me, and for what?" ends up taking half an hour. One Excel table with a few formulas answers it in seconds. Here's how to set it up.

Make it a real Excel Table

Type these headers in row 1, select them plus a few blank rows, and press Ctrl+T (tick "My table has headers"). Name the table Orders in Table Design > Table Name.

Field	What goes in
Order date	Date
Order no.	e.g. 1001, 1002
Customer	Name
Phone	Optional
Item	What they bought
Qty	Number
Rate	Price per unit
Amount	Formula
Paid	Amount received so far
Balance	Formula
Pay mode	Dropdown: Cash, UPI, Bank, Card
Delivery	Dropdown: Pending, Shipped, Delivered
Status	Formula

Tables are worth it because formulas fill down automatically when you add a row, and references read like words.

The formulas

Amount:

=[@Qty]*[@Rate]

Balance:

=[@Amount]-N([@Paid])

Status:

=IF([@Amount]=0,"",IF([@Balance]<=0,"Paid",IF(N([@Paid])>0,"Part-paid","Unpaid")))

Days since order (useful for chasing):

=IF([@Balance]>0,TODAY()-[@[Order date]],"")

Dropdowns

Select the Pay mode column, Data > Data Validation > List, and type Cash,UPI,Bank,Card. Do the same for Delivery. Consistent values are what make the summaries below work.

Colour what needs attention

Select the Status column, Home > Conditional Formatting > Highlight Cells Rules > Equal To: "Unpaid" red, "Part-paid" amber. Add a rule on Delivery = "Pending" if shipping is your bottleneck.

Customer balances in one view

On a new sheet, list customers in column A (in Excel 365/2021, =UNIQUE(Orders[Customer]) does it for you). Then:

Owed:   =SUMIFS(Orders[Balance],Orders[Customer],A2)
Orders: =COUNTIFS(Orders[Customer],A2)
Oldest unpaid (days): =MAXIFS(Orders[Days since order],Orders[Customer],A2)

MAXIFS needs Excel 2019 or later. Sort by "Owed", largest first, and you have your follow-up list.

Monthly sales and collections

Add a helper column Month in the table:

=TEXT([@[Order date]],"yyyy-mm")

Then a quick summary:

Sales:     =SUMIFS(Orders[Amount],Orders[Month],"2026-10")
Collected: =SUMIFS(Orders[Paid],Orders[Month],"2026-10")

Or insert a pivot table: Rows = Month, Values = Sum of Amount, Sum of Paid, Sum of Balance.

Part payments

If a customer pays in instalments, there are two ways:

Simple: just update the Paid cell (e.g. 500 becomes 1200). Fine for a few orders.
Proper: a second table, Payments (Date, Order no., Amount, Mode). Then the Paid column becomes:

=SUMIFS(Payments[Amount],Payments[Order no.],[@[Order no.]])

This keeps a dated record of every payment, which helps when a customer says "I already paid".

Find an order fast

Above the table, add a search cell (say B1) and a result line. For an order number:

=IFERROR(INDEX(Orders[Balance],MATCH($B$1,Orders[Order no.],0)),"Not found")

When a customer messages "what do I owe?", type their order number in B1 and answer in seconds. For a name search, use the table's own filter: click the Customer header arrow and type part of the name in the search box.

Keep a backup

A single file is a single point of failure. Once a week, File > Save As a dated copy (for example "Orders 2026-10-25.xlsx") to a second location such as a cloud drive or a pen drive. It takes ten seconds and saves you from a corrupted file or a lost phone.

Common mistakes

Typing customer names differently. "Priya", "Priya S" and "priya" become three customers. Keep a customer list and use a dropdown.
Merging cells for neat headings above the table. It breaks sorting and filters; keep headings outside the table.
Deleting paid orders. Filter them out instead; history is useful.
One sheet per month. One table with a Month column is far easier to summarise.

A two-minute daily habit

At the end of the day: add today's orders, update Paid from your UPI/bank app, then filter Status to "Unpaid". That's your follow-up list for tomorrow.
This article was drafted with AI tools. It's general spreadsheet guidance, not accounting, tax or legal advice.