The Dairy Product Sales Data Entry System in Excel is a macro-enabled workbook with an 8-field entry form, 4 VBA buttons, 4 live stat cards and 4 dropdown lists holding 36 values – 12 dairy products, 10 customers, 8 payment methods and 6 payment statuses. Every saved sale gets an automatic Record ID in the DPS-0001 series, a Batch No and an Entry TimeStamp.
Most small dairies still run on a delivery book. It works until a cafe disputes a butter invoice, or you need to know which grocers took cheese from one batch, and the answer means turning pages of handwriting. This walkthrough covers every sheet, exactly what the four stat cards count, and the limits worth knowing before you set it up for your own milk shop or depot.
What This Excel Dairy Sales Register Does
It is an .xlsm workbook with four sheets. You type a sale into a form, click Add, and the sale drops into a records table with its Record ID and timestamp. There is nothing to install, nothing to sign into and no subscription. Four cards beside the form recount themselves as records are added, updated or deleted.
It is deliberately small. It is not a point-of-sale system, not accounting software and not a stock, expiry or batch-recall system – it is the sales register a dairy shop or small distributor keeps so the weekly numbers, and the customers who still owe money, are one glance away.
Key Features
- Eight form fields: Sale Date, Product, Batch No, Customer, Quantity, Total Amount, Payment Method and Status.
- Batch No on every sale: type the code printed on the crate or carton, then filter the table by it later.
- Four live buttons: Add, Update, Delete and Reset, all running VBA.
- Double-click editing: double-click a row to load it into the form; the workbook remembers its Record ID, so Update rewrites the right record even after you sort the table.
- Confirmed deletes: Delete asks first and shows the Record ID it is about to remove.
- Credit-sale statuses: Paid, Pending, Partially Paid, Overdue, Refunded and Cancelled.
- Four stat cards: Total Orders, Total Sales, Pending Payments and Paid Orders.
- Six fictional sample sales so you can test every button before you start.
Sheets Explanation
The workbook has four sheets: Data Entry, Setting, Instructions and Get More Templates. The first three do the work.
Data Entry sheet
This is the working screen. The Total Orders, Total Sales, Pending Payments and Paid Orders cards sit on the left, the eight-field form in the middle and the Add, Delete, Update and Reset buttons on the right. Below them is an eleven-column records table: S.No., Record ID, Sale Date, Product, Batch No, Customer, Quantity, Total Amount, Payment Method, Status and Entry TimeStamp. On the sample data the cards read 6, $2,712, 1 and 3.
The S.No. column is a formula, =IF($B15="","",ROW()-14), so it numbers itself and stays blank on empty rows. The Product, Customer, Payment Method and Status columns carry the same dropdowns as the form, so a backlog of delivery-book sales can be typed straight down the grid.
Setting sheet
Everything the dropdowns offer lives here in plain cells you can rewrite, next to the original stat cards.
- Product (12): Whole Milk, Skimmed Milk, Salted Butter, Cheddar Cheese, Mozzarella Cheese, Greek Yogurt, Fresh Cream, Paneer, Ghee, Buttermilk, Ice Cream, Cottage Cheese.
- Customer (10): Green Valley Grocers, Sunrise Cafe, Metro Supermart, Corner Bakery, Hilltop Restaurant, Fresh Mart Retail, City Dairy Depot, Golden Spoon Diner, Riverside Deli, Village Provision Store.
- Payment Method (8): Cash, Credit Card, Debit Card, Bank Transfer, Cheque, Mobile Wallet, Store Credit, Cash on Delivery.
- Status (6): Paid, Pending, Partially Paid, Overdue, Refunded, Cancelled.
The customers are fictional sample entries for you to replace with your own trade and walk-in customers. The cards on the Data Entry screen are linked pictures of the cards on this sheet, which is why restyling one here restyles it there.
How To Use sheet
The Instructions tab explains entering, updating and deleting records, the stat cards, the dropdown lists and enabling macros.
What the Four Stat Cards Count
A card that means something slightly different from what you assumed is worse than no card. These are the formulas straight from the Setting sheet:
| Card | Formula | What it means |
|---|---|---|
| Total Orders | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Sale Date column. A record saved without a date is not counted. |
| Total Sales | SUM('Data Entry'!$H$15:$H$1048576) | Every Total Amount whatever the status, Pending, Overdue, Refunded and Cancelled included. Billed value, not cash collected. |
| Pending Payments | COUNTIF('Data Entry'!$J$15:$J$1048576,"Pending") | Rows whose Status is exactly Pending. |
| Paid Orders | COUNTIF('Data Entry'!$J$15:$J$1048576,"Paid") | Rows whose Status is exactly Paid. |
Pending Payments understates what customers owe. Only two of the six statuses have a card. On the six samples Pending Payments reads 1, yet three sales are unpaid: a Pending $520, a Partially Paid $412.50 and an Overdue $288. To count all three, replace the Pending Payments formula on the Setting sheet with:
=SUM(COUNTIF('Data Entry'!$J$15:$J$1048576,{"Pending","Partially Paid","Overdue"}))Total Sales is billed, not banked. The samples total 2,711.50, shown as $2,712, while the three Paid rows add up to $1,491. For money actually received, add one cell: =SUMIFS('Data Entry'!$H$15:$H$1048576,'Data Entry'!$J$15:$J$1048576,"Paid").
Total Amount is also a typed value. There is no Unit Price, discount or tax column, and Quantity carries no unit, so enter the final billed amount for each sale and keep each product in one unit – litres for milk, kilograms for paneer, packs for ice cream.
Excel Workbook vs. Google Sheets Log vs. Dairy Distribution Software – Feature Comparison
| Feature | This Excel dairy sales register | Home-made Google Sheets log | Dairy distribution software |
|---|---|---|---|
| Cost | $6.99 one-time (regular $11.99) | Free, but you build it | Recurring monthly subscription |
| Platform | Excel for Windows desktop | Any browser | Vendor app or browser |
| Setup time | About 10 minutes | Hours of design work | Days of onboarding |
| Entry form with Add / Update / Delete | Yes, VBA | No | Yes |
| Batch number per sale | Yes, free text | Only if you add it | Usually, with lot tracking |
| Real-time collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Usually |
| Customizable lists | Fully unlocked | Yes | Vendor settings only |
| Stock, expiry dates, invoices | No | No | Usually included |
For a small dairy shop or depot that wants a searchable sales register without paying for distribution software it will not fully use, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Dairy shops, milk booths and cheese counters keeping a daily sales register
- Small depots and distributors selling on credit to cafes, bakeries, restaurants and grocers
- Owners who want paid and pending customer payments visible at a glance
Not a fit if:
- You need stock control, expiry-date tracking, cold-chain logs or batch recall
- You need tax invoices, route planning or several people editing at once
- You work on a Mac, in Excel for the web or in Google Sheets
Real-World Use Cases
Anita runs a neighbourhood dairy shop. She logs each morning’s milk, paneer and ghee sales, types the batch code from every crate, and filters the Batch No column when her supplier asks which customers received a particular delivery.
Tom supplies butter and cheese to local cafes on 7-day credit. He saves each delivery as Pending, switches it to Partially Paid or Paid as money arrives, and filters the Status column every Friday before his collection calls.
A small dairy depot filters the Payment Method column at month-end to split bank transfers, cheques and cash-on-delivery sales before the accountant visits.
Advantages
- Consistent records: dropdowns stop the same cheese or customer being typed three different ways.
- Safe editing: Update and Delete work by Record ID, so sorting the table never breaks an edit.
- Credit visibility: the Status column shows every unpaid delivery in one filter.
- Low cost: $6.99 once instead of a monthly software fee.
- Fast training: a new counter assistant can learn the form in two minutes.
Opportunities for Improvement
- More status cards. Only Pending and Paid are carded; Partially Paid and Overdue deserve their own COUNTIF cards for a business that sells on credit.
- Status-aware sales. Total Sales includes Refunded and Cancelled rows. The SUMIFS above gives cash received.
- Unit price and unit of measure. Total Amount is typed rather than calculated from quantity and price, and Quantity has no unit column.
- Self-extending lists. The four named ranges are fixed and already full –
Setting!$A$3:$A$14,$C$3:$C$12,$E$3:$E$10and$G$3:$G$8. The How To Use page says every dropdown updates on its own, but a value typed below a list will not appear until you insert a row inside the list or widen the range in Formulas > Name Manager. - Dropdown depth. In-table validation covers rows 15 to 214, so drag it down past 200 records.
- Expiry date. There is no expiry or best-before column for perishable stock.
Best Practices
- Rewrite the four lists before your first real sale, and add values by inserting rows inside each list.
- Always fill Sale Date so Total Orders counts the sale.
- Use the same batch code format every time (for example product initials, year-month and a sequence) so a Batch No filter finds everything.
- Update the Status the day a customer pays, so the cards stay honest.
- Start a fresh copy each year and keep a backup, since the file lives on one machine.
Macros, Batches and Food Safety
On first open, click Enable Content. If the file came by download and Excel blocks it, right-click it, choose Properties and tick Unblock. Microsoft explains both steps in Enable or disable macros in Microsoft 365 files and Macros from the internet are blocked by default. Excel for the web and Excel for Mac are not supported.
Batch No is a free-text label that helps you find sales later. The workbook does not track stock, temperatures or expiry dates, and it is not a traceability, recall or food-safety compliance record – keep those in the system your regulator or supplier requires. The sample customers are fictional, and the file has no password or access log.
Explore Relevant Templates
- Dairy Industry Dashboard in Excel – charts and KPIs for analysing a dairy business.
- Dairy Industry KPI Dashboard in Excel – monthly KPI tracking against targets.
- Dairy Products Processing Plant Dashboard in Excel – production-side reporting for a plant.
- Bakery Sales Register Data Entry System in Excel – the same form-and-cards build for a bakery counter.
- Optical Shop Sales Data Entry System in Excel and Garment Shop Sales Data Entry System in Excel – more retail sales registers.
Frequently Asked Questions
What does the Dairy Product Sales Data Entry System in Excel record?
One row per sale with Sale Date, Product, Batch No, Customer, Quantity, Total Amount, Payment Method and Status, plus an automatic Record ID and Entry TimeStamp. Four cards summarise Total Orders, Total Sales, Pending Payments and Paid Orders as records change.
How long does setup take?
About ten minutes. Enable macros, replace the four Setting lists with your own products, customers, payment methods and statuses, delete the six sample rows and start adding sales. No formula needs editing.
Do I need to know VBA?
No. The macros are already written and wired to the Add, Update, Delete and Reset buttons. You only enable macros once when you first open the workbook.
Can it track stock or expiry dates?
No. There is no stock balance, expiry-date column or cold-chain log. It is a sales register, and Batch No is a label you type so you can filter sales by batch.
Why is Pending Payments lower than the number of unpaid sales?
It counts only the status Pending. Partially Paid and Overdue sales are not included. Use the COUNTIF formula above to count all three.
How does it compare to dairy distribution software?
Distribution software adds stock, routes, invoices and multi-user access for a monthly fee. This workbook costs $6.99 once and gives a single computer a clean sales register with live stat cards.
About the Author
Built by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets and Power BI experience. Founder of NextGenTemplates, reaching 300K+ subscribers across YouTube channels. Every template is hand-built and tested before release.
Conclusion
This workbook turns a dairy delivery book into a table you can sort and filter by product, batch, customer or payment status, with four numbers on top that keep themselves current. Know what those numbers count – the Sale Date column, billed rather than collected sales, and two statuses out of six – and it will not surprise you.
Click here to Purchase the Dairy Product Sales Data Entry System in Excel – $11.99, currently $6.99.
✅ Instant download · One-time payment · No subscription
Last updated: September 2026
Watch more Excel tutorials at Youtube.com/@PKAnExcelExpert
