DEV Community: Ashish Srivastava

Payroll register in Excel: gross pay, deductions and net pay for a small team

Ashish Srivastava — Sat, 31 Oct 2026 13:34:36 +0000

Written with AI assistance.

If you pay a handful of people, you don't need payroll software to stay organised. You need one register with a row per employee per month, and formulas that work out gross pay, deductions and net pay. This guide builds that register in Excel, so you type each number once and can print a payslip for anyone.

Note: deductions, contributions and reporting duties depend on where you operate. This article shows spreadsheet mechanics only, with rates you enter yourself. It is not tax, payroll, legal or accounting advice; check the rules with a qualified adviser.

Sheet 1: Employees

Col	Field
A	Emp ID
B	Name
C	Job title
D	Start date
E	Monthly base pay
F	Fixed allowances
G	Deduction 1 % (for example tax withholding)
H	Deduction 2 % (for example pension)
I	Fixed deduction per month
J	Employer contribution % (optional)

You enter the rates that apply to you; nothing is preset. Keep only what you need. Full bank and ID numbers don't belong in a spreadsheet you email around.

Sheet 2: Register (one row per employee per month)

Col	Field
A	Month (first day, e.g. 2026-10-01)
B	Emp ID
C	Days in month
D	Days paid
E	Base pay
F	Allowances
G	Other earnings
H	Gross
I	Deduction 1
J	Deduction 2
K	Fixed deduction
L	Other deductions
M	Total deductions
N	Net pay

Days in month (C2): =DAY(EOMONTH(A2,0)). If you pay on working days (Monday to Friday), use =NETWORKDAYS(A2,EOMONTH(A2,0)) instead.

Pro-rated base pay (E2), for someone who started mid-month or took unpaid leave:

=IF($B2="","",ROUND(INDEX(Employees!$E:$E,MATCH($B2,Employees!$A:$A,0))*D2/C2,2))

Copy the same pattern for allowances (column F of Employees).

Gross, deductions, net:

H2: =IF($B2="","",E2+F2+N(G2))
I2: =IF($B2="","",ROUND(H2*INDEX(Employees!$G:$G,MATCH($B2,Employees!$A:$A,0)),2))
J2: =IF($B2="","",ROUND(H2*INDEX(Employees!$H:$H,MATCH($B2,Employees!$A:$A,0)),2))
K2: =IF($B2="","",INDEX(Employees!$I:$I,MATCH($B2,Employees!$A:$A,0)))
M2: =IF($B2="","",I2+J2+K2+N(L2))
N2: =IF($B2="","",H2-M2)

The percentage deductions here are taken from gross pay. If a rule you follow works on a different base, change the formula to match it. Keeping the rates in the Employees sheet means that when one changes, you update one cell, not a formula.

Quick check: base pay 4,200 plus allowances 300 for a full month is a gross of 4,500. At a 15% deduction 1 that is 675. Half a month (15 of 30 days) gives a gross of 2,250 and a deduction of 337.50.

Add checks

Days paid can't exceed days in month. Select column D, Data > Data Validation, whole number between 0 and =C2.
Duplicates. A check column catches the same person and month entered twice:

=IF(COUNTIFS($A:$A,A2,$B:$B,B2)>1,"Duplicate","OK")

Unknown IDs. Wrap the lookups in IFERROR(...,0) and add a Name column that shows "Check ID" when the ID isn't in the Employees list.

A monthly summary without a pivot table

On a Summary sheet, list months down column A and use SUMIFS:

Gross:      =SUMIFS(Register!$H:$H,Register!$A:$A,A2)
Deductions: =SUMIFS(Register!$M:$M,Register!$A:$A,A2)
Net pay:    =SUMIFS(Register!$N:$N,Register!$A:$A,A2)

For a year-to-date total, add a start and end date:

=SUMIFS(Register!$N:$N,Register!$A:$A,">="&DATE(2026,1,1),Register!$A:$A,"<="&DATE(2026,12,1))

Overtime and one-off payments

Bonuses, overtime and reimbursements don't belong in the Employees sheet because they change each month. Put them in the Register's Other earnings column and include them in gross, as above. Label them clearly on the payslip so the employee can see where the extra amount came from.

Employer cost (optional)

If you track an employer contribution, add one more column: =ROUND(H2*INDEX(Employees!$J:$J,MATCH($B2,Employees!$A:$A,0)),2). Total payroll cost for the month is then gross plus that column.

Common mistakes

Overwriting last month's rows. Add new rows each month; the register is your history.
Rates hard-coded in formulas. Keep them in the Employees sheet.
Duplicate rows for the same person and month. Use the check column.
Sharing the whole file. Send each person a PDF of their own slip, not the register.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

FLASH40 has ended — Excel templates for freelancers and small shops (full price)

Ashish Srivastava — Fri, 30 Oct 2026 18:55:20 +0000

FLASH40 has ended — templates still available at full price

The FLASH40 40% off window closed after 30 Oct 2026 11:59 PM IST. The same Excel templates remain on Gumroad at regular prices.

Live paid templates

Free starter (always $0): Order & Payment Tracker

Made with AI tools under my direction. Plain formulas where noted — check each product page for macros/VBA. Global templates — not tax, payroll, or legal advice. Questions welcome in the comments.

Sales tax / VAT on an Excel invoice — rates, inclusive prices, and rounding

Ashish Srivastava — Fri, 30 Oct 2026 18:54:59 +0000

Sales tax, VAT and similar taxes all work the same way in a spreadsheet: a percentage applied to an amount. What differs is the rate, whether prices include the tax, and how you round. This guide shows a simple, generic tax % column for an Excel invoice template and the logic behind it, using plain formulas that work in older Excel versions too.

Note: which rate applies, whether you must charge tax and what an invoice must show depend on where you operate. This is about spreadsheet logic only. It is not tax, legal or accounting advice; check the rules with a qualified adviser.

1. Keep one default rate in Settings

Make a Settings sheet and type your default tax rate in one cell, say Settings!B7 = 8%. Type 0% if you don't charge tax. Every formula points at this cell, so you never type the rate twice.

2. The invoice log

On an Invoices sheet, one row per invoice: B = date, C = customer, D = amount before tax, E = tax % (optional, leave blank to use the default).

Rate used (H2):

=IF($B2="","",IF(E2="",Settings!$B$7,E2))

A blank means "use the default". A typed 0% means "no tax on this one", so the two cases stay different.

Tax (F2) and total (G2):

F2: =IF($B2="","",ROUND(D2*H2,2))
G2: =IF($B2="","",D2+F2)

Quick check: 1,000 at 8% gives tax 80 and a total of 1,080. The same 1,000 with 5% typed in column E gives 50 and 1,050, and with 0% gives 0 and 1,000.

3. When your prices already include tax

Some businesses quote one price that includes tax. Then you work backwards from the price. In a spare area, put the tax-inclusive price in P2, the rate in Q2, and:

Net amount (R2): =ROUND(P2/(1+Q2),2)
Tax part (S2):   =P2-R2

With 1,080 at 8%, the net is 1,000 and the tax part is 80. Use the tax part as a difference, not as a second rounded calculation, so net and tax always add back to the total.

4. Mixed rates on one invoice

If different items carry different rates, put the rate on each line instead of the invoice. On an Items list add a tax % column (blank = default). The line formula picks the item's rate if there is one and the default if not:

=IFERROR(IF(INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))="",Settings!$B$7,INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))),Settings!$B$7)

5. Round each line, then add

Rounding can make totals differ by a cent. Take three items at 0.50 with 5% tax:

Round each line: 0.03 + 0.03 + 0.03 = 0.09
Add first, then round: 1.50 x 5% = 0.075, which rounds to 0.08

Neither is "wrong". What matters is picking one method and using it everywhere. Rounding each line means the tax column always adds up to what's printed on each line, which is why the formulas above round per line. If you need a particular rounding method, ask your adviser.

6. A tax summary by rate

If you use more than one rate, list the rates in a small table (say M2:M5) and use SUMIFS on the "rate used" column:

Amount at this rate: =SUMIFS($D$2:$D$500,$H$2:$H$500,M2)
Tax at this rate:    =SUMIFS($F$2:$F$500,$H$2:$H$500,M2)

Add a check that the table's total equals =SUM(F2:F500). If it doesn't, a row uses a rate that isn't in your list.

7. Why every formula starts with `IF($B2="","",…)`

You'll fill formulas down 500 rows ahead of time. If they aren't wrapped, empty rows show 0 or errors, and your totals and charts get messy. Using the date cell as the "is this row in use?" check keeps unused rows blank.

8. Turn the log into a receivables list

Add columns for due date (I = date + payment terms) and amount received (J), then:

Balance (K):      =IF($B2="","",G2-N(J2))
Days overdue (L): =IF($B2="","",IF(AND(K2>0,TODAY()>I2),TODAY()-I2,0))

A per-customer SUMIFS on balance and days overdue then gives 0-30 / 31-60 / 61-90 / 90+ buckets, which is your follow-up list for the week.

Common mistakes

Hard-coding the rate inside formulas. Keep it in a cell.
Mixing amounts before tax and after tax in one column.
Leaving the tax cell blank when you mean zero. Type 0%.
Using a rate from an old invoice for a new one. Check your rates when they change.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

Order & Shipping Tracker in Excel — ship-by dates and late flags (40% off through 30 Oct)

Ashish Srivastava — Thu, 29 Oct 2026 16:02:31 +0000

Order & Shipping Tracker — 40% off with FLASH40 through 30 Oct

Update (30 Oct): FLASH40 ends tonight at 11:59 PM IST — last hours at 40% off. After that, full price.

For freelancers and small shops who track orders in scattered notes: one Excel file with ship-by dates, late flags, and simple profit-per-order math.

What's in it

Order log with ship-by date
Late / on-time flags (plain formulas — no macros)
Basic profit / order view

Price: usually $8. Code FLASH40 = 40% off (~$5) through 30 Oct 2026 11:59 PM IST.

→ Order & Shipping Tracker on Gumroad

Same sale (same code):

Free (no code): Order & Payment Tracker

Made with AI tools under my direction. Global templates — not tax or legal advice. Questions welcome in the comments.

ChatGPT Prompts for Small Business Owners: 8 Fill-in Templates and How to Use Them

Ashish Srivastava — Thu, 29 Oct 2026 13:38:00 +0000

Written with AI assistance. The prompts below are starting points; results depend on the details you add and the tool you use.

A prompt is a short brief. The more specific it is, the less editing you do afterwards. The shortcut is a fill-in template: a prompt with [brackets] where your details go, saved so you only fill the blanks.

Below are eight, covering parts of a small shop that people often forget about. Each is written to be pasted into ChatGPT or any other AI chat tool.

1. How to fill the brackets

Replace every [bracket]. A prompt with a bracket left in gets generic output.
Give real numbers: the date, the order number, the price.
Say who's reading. "A customer who has waited a week" gets a different reply from "a new customer".
Ask for a length. "Three sentences" or "under 80 words" saves trimming.

2. Eight fill-in prompts

Product description (listing)

Write a product description for [product]. Buyer: [who]. Key features: [features]. Tone: [tone]. Under [80] words.

Listing titles

Give me 10 listing title options for [product] sold on [platform]. Include [keyword] and keep each under [60] characters.

Shipping update (customer email)

Write a shipping update for order [order number]. It left on [date] with [carrier]; tracking number [number]. Friendly, three sentences.

Delay apology (customer email)

Write a polite apology for a delay on order [order number]. New date: [date]. Offer [remedy]. No excuses, no blame.

Supplier quote

Write a short message to [supplier] asking for a quote on [quantity] of [item], delivery to [place] by [date].

Pricing sums

I pay [cost] per unit plus [shipping] shipping and want about [margin]% margin. Show the maths for a selling price, step by step, so I can check it.

Packing checklist

Make a checklist for packing and posting an order, for a shop that sells [product]. Keep it to [8] steps.

Review request

Write a short, friendly message asking a customer who received [product] to leave a review. Do not offer anything in return.

3. Always check what it writes

AI tools write fluently, including when they're wrong. Before you send or publish anything:

Check facts, prices, dates and policies against your own records. The tool doesn't know your stock, your carrier or your refund rules unless you told it.
Redo any maths. The pricing prompt asks for the steps so you can follow them; confirm the answer with a calculator or a spreadsheet.
Read it aloud. If it doesn't sound like you, change it.
Don't promise anything on your behalf (refunds, delivery dates, discounts) that you haven't decided yourself.

I haven't measured how well these prompts perform. Results depend on the details you add and the tool you use.

4. Keep them in a sheet you can search

Once you have a few dozen, paste them into a sheet: A Category, B Prompt. Put a search word in B1, then in C4:

=IF($B$1="","",IF(ISNUMBER(SEARCH($B$1,B4)),"match",""))

Fill it down, and filter column C for "match". Count the matches with:

=COUNTIF(C4:C104,"match")

SEARCH ignores capital letters, so "Tracking" and "tracking" both find the shipping prompt. With the eight prompts above, searching "tracking" finds one match (the shipping update).

5. Don't paste private details

Leave out customers' full names, addresses, phone numbers and payment details when you use a public AI tool. Use "a customer" and the order number instead.

Written with AI tools. This is general guidance, not business, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

Admin Data Consolidation Workbook — merge messy admin tables in Excel (40% off through 30 Oct)

Ashish Srivastava — Thu, 29 Oct 2026 08:29:04 +0000

Admin Data Consolidation — 40% off with FLASH40 through 30 Oct

Update (30 Oct): FLASH40 ends tonight at 11:59 PM IST — last hours at 40% off. After that, full price.

One Excel workbook for merging messy admin tables into a clean working file. LibreOffice-tested. Plain formulas — no macros / no VBA. UI notes in English + Spanish. Built with AI tools under my direction.

Usually $15. Code FLASH40 = 40% off (~$9) through 30 Oct 2026 11:59 PM IST.

→ Admin Data Consolidation Workbook

Also in the same sale:

Free starter (no code): Order & Payment Tracker

Global templates — not tax, payroll, or legal advice. Questions welcome in the comments.

Agency Admin Workbook — clients, income/expense, and reports in one Excel file (40% off through 30 Oct)

Ashish Srivastava — Thu, 29 Oct 2026 03:42:16 +0000

Agency Admin Workbook — 40% off with FLASH40 through 30 Oct

Update (30 Oct ~8 PM IST): Final hours — FLASH40 ends tonight at 11:59 PM IST. After that, full price.

I put together an Excel workbook for freelancers and small agencies who juggle clients, money in/out, and simple reports in separate sheets (or worse, in their head).

What's in it

Client list
Income and expense log
Auto-built summary reports (plain Excel formulas — no macros)

Price: usually $15. Code FLASH40 = 40% off (~$9) through 30 Oct 2026 11:59 PM IST.

→ Agency Admin Workbook on Gumroad

Other paid Excel templates in the same sale (same code):

Free (no code needed): Order & Payment Tracker

Made with AI tools under my direction. Global templates — not tax, payroll, or legal advice. Happy to answer setup questions in the comments.

FLASH40: 40% off my Excel templates for freelancers and small shops (through 30 Oct)

Ashish Srivastava — Wed, 28 Oct 2026 14:46:43 +0000

Written with AI assistance. I edited it.

Update (30 Oct ~8 PM IST): Final hours — FLASH40 ends tonight at 11:59 PM IST. After that, full price.

I'm running a short flash sale on the Excel (.xlsx) templates I publish for freelancers and small shops — orders, invoices/receivables, bookkeeping, shipping, inventory, and a sheets bundle.

Code FLASH40 = 40% off every paid product through 30 Oct 2026, 11:59 PM IST.

Start free, then grab what you need:

Free Order & Payment Tracker: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker
Simple Bookkeeping ($6): https://ashishsrivastavajay.gumroad.com/l/hpuzo?code=FLASH40
Invoice & Receivables ($7): https://ashishsrivastavajay.gumroad.com/l/bxwjyy?code=FLASH40
Order & Shipping Tracker ($8): https://ashishsrivastavajay.gumroad.com/l/dcbkqg?code=FLASH40
Sheets Bundle ($19): https://ashishsrivastavajay.gumroad.com/l/lozujz?code=FLASH40

Shop: https://ashishsrivastavajay.gumroad.com

All templates are made with AI tools under my direction, Excel only, no macros. They're for record-keeping — not tax, legal, or accounting advice.

If you've sold low-price utility templates before: did a free tracker → niche paid files → bundle convert for you, or did one mid-priced bundle win first? Still looking for the first buyers and hard lessons on discovery.

Invoice format in Excel: a simple layout with an optional tax % column

Ashish Srivastava — Wed, 28 Oct 2026 13:42:10 +0000

You don't need billing software to send a clean invoice. A well laid out Excel workbook can keep your details in one place, pull customer and item details from lists, add tax if you charge it, and print or save a PDF. This guide builds a simple, worldwide invoice format in Excel step by step.

Note: whether you charge tax, which rate applies and what an invoice must show depend on where you operate. This article covers spreadsheet layout and formulas only. It is not tax, legal or accounting advice.

Step 1: A Settings sheet

Make a sheet called Settings with the details that appear on every invoice:

Cell	Value
B2	Business name
B3	Address
B4	Email
B5	Currency label, e.g. `USD`
B6	Tax name (for example VAT or Sales tax)
B7	Default tax % (type `0%` if you don't charge tax)
B8	Invoice prefix, e.g. `INV-`
B9	Payment details

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 = email, C = billing address, D = payment terms in days.

On an Items sheet: A = item or service, B = unit price, C = tax % (leave blank to use the default from Settings).

Turn both into Excel Tables (Ctrl+T) so new rows appear in your dropdowns automatically.

Step 3: The invoice header

On an Invoice sheet:

F2: a running number you type, e.g. 41
F3: =Settings!B8&TEXT(F2,"0000") gives INV-0041
F4: the invoice date
B6: the customer, as a dropdown (Data > Data Validation > List, pointing at the customer names)
F5, due date: =F4+IFERROR(INDEX(Customers!$D:$D,MATCH($B6,Customers!$A:$A,0)),30)

If the customer isn't in the list, the due date falls back to 30 days.

Pull the customer's address the same way:

B7: =IFERROR(INDEX(Customers!$C:$C,MATCH($B$6,Customers!$A:$A,0)),"")

Step 4: The line items

Start the item rows at row 10 with these columns:

Col	Field
A	Item (dropdown from the Items list)
B	Qty (you type)
C	Rate
D	Amount
E	Tax %
F	Tax
G	Line total

C10: =IF($A10="","",IFERROR(INDEX(Items!$B:$B,MATCH($A10,Items!$A:$A,0)),0))
D10: =IF($A10="","",ROUND(B10*C10,2))
E10: =IF($A10="","",IFERROR(IF(INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))="",Settings!$B$7,INDEX(Items!$C:$C,MATCH($A10,Items!$A:$A,0))),Settings!$B$7))
F10: =IF($A10="","",ROUND(D10*E10,2))
G10: =IF($A10="","",D10+F10)

The IF($A10="","",…) wrapper keeps unused rows blank. Copy row 10 down for as many lines as you need. These are plain IF, INDEX and MATCH formulas, so they work in older versions of Excel as well as Excel 365.

The tax % column is optional. An item with a blank tax % uses the default; an item with 0% stays at zero, which is handy for items you don't charge tax on.

Step 5: Totals

Below the items (say rows 25 to 27), with the tax label pulled from Settings:

Subtotal:        =SUM(D10:D24)
=Settings!B6:    =SUM(F10:F24)
Total due:       =SUM(G10:G24)

Add the currency label next to the total with ="Total due ("&Settings!B5&")".

Step 6: Keep a register of every invoice

Before you clear the invoice for the next customer, copy its number, date, customer, subtotal, tax and total into an Invoices register sheet, one row per invoice. Add columns for Amount received and Balance (=F2-N(G2)). That register is what you use for monthly totals and for chasing payments. A pivot table on it (Rows: month; Values: sum of total) gives a quick monthly view.

Step 7: Print or save as PDF

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

Common mistakes

Hard-coding a tax rate in formulas. Keep rates in Settings and Items.
Typing customer details by hand each time. Look them up from the list instead.
Overwriting the last invoice without logging it. Add it to the register first.
Rounding only the grand total. Round each line's tax so the lines add up to the total.
Reusing invoice numbers. A check such as =COUNTIF(A:A,A2)>1 on the register's number column flags duplicates.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

Salary slip format in Excel: a printable payslip you fill from one register

Ashish Srivastava — Wed, 28 Oct 2026 13:36:05 +0000

Written with AI assistance.

Staff, contractors and part-timers often ask for a payslip, for a loan, a rental application or their own records. 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: what to put on it, how to lay it out and how to fill it with formulas.

Note: which deductions apply, what a payslip must show and what you must report depend on where you operate. This article covers spreadsheet layout only. It is not tax, payroll, legal or accounting advice; check the rules with a qualified adviser.

What a typical salary slip shows

Header: business name, address, "Salary slip for October 2026"
Employee details: name, employee ID, job title, start date, bank account (last 4 digits only)
Attendance: days in the month, days paid
Earnings and deductions: two columns side by side
Net pay, plus a line such as "This is a computer-generated slip"

Step 1: The data the slip reads from

Keep three sheets:

Settings: B1 = business name, B2 = address, B3 = currency label, B4 = name of deduction 1 (for example "Tax withholding"), B5 = name of deduction 2 (for example "Pension")
Employees: A = Emp ID, B = Name, C = Job title, D = Start date, E = Bank (last 4)
Register: one row per employee per month. A = Month (first day), B = Emp ID, C = Days in month, D = Days paid, E = Base pay, F = Allowances, G = Other earnings, H = Gross, I = Deduction 1, J = Deduction 2, K = Other deductions, L = Total deductions, M = 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 a portrait page:

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

Two inputs at the top:

C6: Month (type the first day of the month, or use a dropdown of register months)
C7: Emp ID (a dropdown from the Employees list)

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(TEXT(INDEX(Employees!$D:$D,MATCH($C$7,Employees!$A:$A,0)),"dd mmm yyyy"),"")
C11: =IFERROR("XXXX"&INDEX(Employees!$E:$E,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 base pay. Change only the first range for each field: $F:$F for allowances, $G:$G for other earnings, $I:$I to $K:$K for deductions, $D:$D for days paid.

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

Earnings	Amount	Deductions	Amount
Base pay	…	`=Settings!B4`	…
Allowances	…	`=Settings!B5`	…
Other earnings	…	Other deductions	…
Gross	`=SUM(C15:C17)`	Total deductions	`=SUM(E15:E17)`

Leave out rows that don't apply to you rather than showing zeros.

Step 5: Net pay

C21: =C18-E18
B22: ="Net pay: "&TEXT(C21,"#,##0.00")&" "&Settings!B3

C18 is the gross cell and E18 the total deductions cell in the layout above. Adjust the references to match your rows.

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, then Page Layout > Print Area > Set Print Area.
Page Setup > Fit to 1 page, portrait.
Pick the month in C6, then change C7 for each employee and use 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.

Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.

Practical spreadsheet templates for small businesses, made with AI assistance.

Order Tracking Spreadsheet for Online Sellers: Ship-By Dates, Late Orders and Profit per Order

Ashish Srivastava — Wed, 28 Oct 2026 12:56:52 +0000

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

If you sell on more than one channel (say Etsy, Shopify, eBay and Instagram DMs), each one has its own order screen and none of them tells you the two things that matter at 9 a.m.: what has to ship today, and which orders are already late.

Here's a single order sheet that answers both, plus what each order actually made after postage and fees. It uses WORKDAY, NETWORKDAYS, COUNTIF and SUMIFS, with no macros.

1. Settings and the order list

At the top of the sheet:

B1: Handling days, the number of working days you promise to ship within (2 here).
B2: As of, today's date. =TODAY() works; I used a fixed date so the results below don't change.
U2:U10: Holidays, days you don't ship. Blank cells are ignored.

Orders from row 5: A Order date, B Order no, C Channel, D Price, E Shipping charged, F Item cost, G Postage, H Fees (platform and payment fees), I Shipped date, J Delivered date. Give column C a dropdown (Data validation → List) so "etsy" and "Etsy " don't become two channels.

2. The ship-by date

K5:

=IF(A5="","",WORKDAY(A5,$B$1,$U$2:$U$10))

WORKDAY counts forward in working days, skipping weekends and your holidays. An order on Friday with 2 handling days must ship by Tuesday, not Sunday. Format K as a date.

3. Status

L5:

=IF(A5="","",IF(J5<>"","Delivered",IF(I5<>"","In transit",IF($B$2>K5,"LATE - ship now","To ship"))))

The order of the tests matters. A delivered date wins over a shipped date, and only orders with neither can be "To ship" or "LATE". You fill in two dates per order; the status works itself out.

4. Days late

M5, in working days:

=IF(A5="","",IF(I5<>"",MAX(0,NETWORKDAYS(K5,I5,$U$2:$U$10)-1),IF($B$2>K5,NETWORKDAYS(K5,$B$2,$U$2:$U$10)-1,0)))

For shipped orders it measures how late the shipping was (0 if on time). For unshipped orders it measures how late they are today. NETWORKDAYS counts both end dates, so the -1 makes "shipped on the ship-by date" read 0. Format M as a Number.

This column is worth keeping even after orders ship: a run of 1s and 2s on one channel is an early warning that your handling time is too optimistic.

5. Profit and margin per order

N5, profit:

=IF(A5="","",D5+E5-F5-G5-H5)

O5, margin (format as %):

=IF(OR(N5="",D5+E5=0),"",N5/(D5+E5))

Including shipping charged and postage paid is the point. An order where you charge 4 for shipping and pay 6 to post it quietly loses 2, and a per-order profit column shows it.

6. The summary

Put the four statuses in Q5:Q8 and count them in R5:

=COUNTIF($L$5:$L$504,Q5)

Then the totals and a check:

Cell	Formula
R9: Orders counted by status	`=SUM(R5:R8)`
R10: All orders	`=COUNTA($B$5:$B$504)`
R11: Check	`=IF(R9=R10,"OK","CHECK")`
R12: Total profit	`=SUM(N5:N504)`
R13: Overall margin	`=IF(SUM(D5:D504)+SUM(E5:E504)=0,"",R12/(SUM(D5:D504)+SUM(E5:E504)))`
R14: Revenue, last 30 days	`=SUMIFS(D5:D504,A5:A504,">"&($B$2-30))+SUMIFS(E5:E504,A5:A504,">"&($B$2-30))`
R15: Delivered but never marked shipped	`=COUNTIFS(I5:I504,"",J5:J504,"<>")`

And a channel table (channels in Q18:Q21):

=COUNTIF($C$5:$C$504,Q18)
=SUMIFS($N$5:$N$504,$C$5:$C$504,Q18)

R11 catches an order with no order date (it has no status, so the counts fall short). R15 catches a delivered date typed into a row with no shipped date, which would hide a late shipment.

Six made-up orders, as of Wednesday 28 Oct 2026, with 2 handling days:

Order	Ordered	Channel	Ship-by	Status	Days late	Profit	Margin
1001	Mon 19 Oct	Etsy	Wed 21 Oct	Delivered	0	23.80	53%
1002	Wed 21 Oct	Shopify	Fri 23 Oct	In transit	1	11.40	46%
1003	Fri 23 Oct	eBay	Tue 27 Oct	LATE - ship now	1	29.50	43%
1004	Mon 26 Oct	Etsy	Wed 28 Oct	To ship	0	11.40	52%
1005	Thu 22 Oct	Instagram	Mon 26 Oct	In transit	0	21.00	60%
1006	Sat 24 Oct	Shopify	Tue 27 Oct	LATE - ship now	1	25.60	47%

Statuses: 1 to ship, 2 late, 2 in transit, 1 delivered, so 6 of 6 counted and the check said OK. Profit 122.70 on 250.00 of revenue (49%). By channel: Etsy 2 orders, 35.20; Shopify 2, 37.00; eBay 1, 29.50; Instagram 1, 21.00.

Two things stand out. Order 1006 came in on a Saturday and was already late by Wednesday, because the two handling days started on Monday. And order 1002 shipped a working day late (Monday instead of Friday) even though it now shows as "In transit", which is exactly what the days-late column is for.

7. Test it before you trust it

Add rows you know the answer to, then confirm:

an order placed on a Friday (ship-by should be the following Tuesday with 2 handling days)
an order whose ship-by date falls on one of your holidays (it should move past it)
an order shipped exactly on its ship-by date (days late should be 0)
a delivered date with no shipped date (R15 should say 1)

Then delete them.

This is an order-tracking method, not accounting or tax advice. Check your profit figures against your platform payouts.

About me, and a disclosure: I'm Ashish, an 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.

A freelance income tracker spreadsheet: invoices, unpaid clients, and money set aside for tax

Ashish Srivastava — Tue, 27 Oct 2026 16:11:12 +0000

This article was written with AI assistance and reviewed before publishing.

When you freelance, "how much did I make this month?" is surprisingly hard to answer. Some invoices are paid, some are late, and some clients pay in a different currency. Here's a plain spreadsheet that answers it, plus two more questions: who still owes me? and how much should I put aside?

It works in Excel or Google Sheets.

1. The Income tab: one row per invoice

Column	Example
Invoice #	INV-031
Client	Acme Ltd
Project	Landing page
Invoice date	2026-10-05
Due date	=D2+30
Amount	1,200.00
Currency	USD
Paid date
Status
Days overdue

Status:

=IF(H2<>"","Paid",IF(TODAY()>E2,"Overdue","Open"))

Days overdue:

=IF(I2="Overdue",TODAY()-E2,"")

Sort by Days overdue, largest first. Those are the reminder emails to send today.

2. Handling more than one currency

Add a Rate column (how much of your home currency one unit is worth on the day you're paid) and a Home amount column:

=IF(H2="","",F2*K2)

Fill in the rate from your bank statement once the money arrives. Then your totals reflect what actually landed in your account, not an estimate.

3. Monthly summary

On a Summary tab, put the first day of each month in A2:A13, then:

Earned (paid):  =SUMIFS(Income!L:L,Income!H:H,">="&A2,Income!H:H,"<="&EOMONTH(A2,0))
Invoiced:       =SUMIFS(Income!F:F,Income!D:D,">="&A2,Income!D:D,"<="&EOMONTH(A2,0))
Still owed:     =SUMIFS(Income!F:F,Income!I:I,"<>Paid")

A simple column chart of "Earned" by month shows your real income trend, including the slow months.

4. A "set aside" line (not tax advice)

Lots of freelancers move a fixed percentage of every payment into a separate account. Put your percentage in one cell (for example Summary!B1 = 25%) and add:

Set aside this month: =B2*$B$1

The right percentage depends on where you live and how you're set up, so ask a local accountant. The point of the sheet is just to make the habit automatic.

5. Expenses (optional)

Add an Expenses tab with Date, Description, Category, and Amount. Then you get:

Profit this month = Earned − SUMIFS(Expenses!D:D, Expenses!A:A, ">="&A2, Expenses!A:A, "<="&EOMONTH(A2,0))

Weekly 5-minute routine

Add any new invoices.
Mark payments received, with the date and rate.
Send reminders to everything marked Overdue.
Move the set-aside amount.

That's all there is to it. If you build one, I'd like to hear what columns you added.

DEV Community: Ashish Srivastava

Payroll register in Excel: gross pay, deductions and net pay for a small team

Sheet 1: Employees

Sheet 2: Register (one row per employee per month)

Add checks

A monthly summary without a pivot table

Overtime and one-off payments

Employer cost (optional)

Common mistakes

FLASH40 has ended — Excel templates for freelancers and small shops (full price)

FLASH40 has ended — templates still available at full price

Sales tax / VAT on an Excel invoice — rates, inclusive prices, and rounding

1. Keep one default rate in Settings

2. The invoice log

3. When your prices already include tax

4. Mixed rates on one invoice

5. Round each line, then add

6. A tax summary by rate

7. Why every formula starts with IF($B2="","",…)

8. Turn the log into a receivables list

Common mistakes

Order & Shipping Tracker in Excel — ship-by dates and late flags (40% off through 30 Oct)

Order & Shipping Tracker — 40% off with FLASH40 through 30 Oct

ChatGPT Prompts for Small Business Owners: 8 Fill-in Templates and How to Use Them

1. How to fill the brackets

2. Eight fill-in prompts

3. Always check what it writes

4. Keep them in a sheet you can search

5. Don't paste private details

Admin Data Consolidation Workbook — merge messy admin tables in Excel (40% off through 30 Oct)

Admin Data Consolidation — 40% off with FLASH40 through 30 Oct

Agency Admin Workbook — clients, income/expense, and reports in one Excel file (40% off through 30 Oct)

Agency Admin Workbook — 40% off with FLASH40 through 30 Oct

FLASH40: 40% off my Excel templates for freelancers and small shops (through 30 Oct)

Invoice format in Excel: a simple layout with an optional tax % column

Step 1: A Settings sheet

Step 2: Customers and Items lists

Step 3: The invoice header

Step 4: The line items

Step 5: Totals

Step 6: Keep a register of every invoice

Step 7: Print or save as PDF

Common mistakes

Salary slip format in Excel: a printable payslip you fill from one register

What a typical salary slip shows

Step 1: The data the slip reads from

Step 2: Lay out the slip

Step 3: Pull employee details

Step 4: Pull the month's figures

Step 5: Net pay

Step 6: Catch blanks before printing

Step 7: Print a slip for each person

Tips

Order Tracking Spreadsheet for Online Sellers: Ship-By Dates, Late Orders and Profit per Order

1. Settings and the order list

2. The ship-by date

3. Status

4. Days late

5. Profit and margin per order

6. The summary

7. Test it before you trust it

A freelance income tracker spreadsheet: invoices, unpaid clients, and money set aside for tax

1. The Income tab: one row per invoice

2. Handling more than one currency

3. Monthly summary

4. A "set aside" line (not tax advice)

5. Expenses (optional)

Weekly 5-minute routine

7. Why every formula starts with `IF($B2="","",…)`