The Fruit and Vegetable Sales Data Entry System in Excel is a macro-enabled workbook with a 7-field entry form, 4 VBA buttons, 4 live stat cards and 3 dropdown lists holding 28 values – 10 produce categories, 12 suppliers and 6 payment statuses. Every saved sale gets an automatic Record ID in the FAVS-0001 series and an Entry TimeStamp, and the records table has formatted, dropdown-ready rows for 200 sales.
Plenty of greengrocers and market sellers still keep sales in a counter notebook. It works until someone asks how much was written up last week, or which restaurant order is still unpaid, and the answer means flicking through pages of handwriting. This walkthrough covers every sheet, exactly what each stat card counts, and the limits worth knowing before you set it up for your own stall or wholesale business.
What the Fruit and Vegetable Sales Data Entry System in Excel 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 till, not a billing or GST/VAT invoicing tool, not connected to a weighing scale, not stock control and not accounting software. The six sample sales, suppliers and figures are fictional, and the workbook makes no food-safety, FSSAI or organic claim – “Organic” is simply one editable category label.
Key Features
- Seven-field form: Item, Category, Supplier, Sale Date, Quantity (kg), Amount and Payment Status.
- Four VBA buttons: Add, Update, Delete (with a confirmation that shows the Record ID) and Reset.
- Stable Record IDs: the next ID is one more than the highest in the table, so a deleted number is never reused.
- Double-click to edit: the row loads into the form and the macro remembers its ID, so Update finds it wherever it sits.
- Dropdowns in two places: on the form and on table rows 15-214, all reading the Setting sheet.
- Linked-picture stat cards: the cards on Data Entry are pictures of formula cards on Setting, so restyling one restyles both.
- Unlocked: no sheet protection and no VBA password.
Sheets Explanation
Data Entry sheet
The form, the four buttons and the four stat cards sit across the top. Below them the table holds S.No., Record ID, Item, Category, Supplier, Sale Date, Quantity (kg), Amount, Payment Status and Entry TimeStamp. With the samples loaded the cards read 6 records, $754 revenue, 3 paid orders and 1 pending order.
Setting sheet
Three lists drive every dropdown: Category List (Fruit, Vegetable, Herb, Leafy, Root, Berry, Citrus, Melon, Exotic, Organic), Supplier List (Green Valley Farms, Sunrise Produce, Fresh Fields Co and nine more) and Payment Status List (Paid, Pending, Partial, Overdue, Refunded, Cancelled). The four formula cards live here too.
How To Use sheet
Short instructions for entering, updating and deleting records, how the stat cards update, how to edit the lists and how to enable the macros on first open.
What the Four Stat Cards Count
These are the real formulas on the Setting sheet, read from the workbook itself:
| Card | Formula | What it means |
|---|---|---|
| Total Sales Records | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Item column, not Record ID. A row typed into the table without an ID is counted, though Update and Delete cannot find it. |
| Total Revenue | SUM('Data Entry'!$H$15:$H$1048576) | Every Amount whatever the Payment Status – Pending, Partial, Overdue, Refunded and Cancelled included. |
| Paid Orders | COUNTIF('Data Entry'!$I$15:$I$1048576,"Paid") | Rows whose status is exactly Paid. |
| Pending Orders | COUNTIF('Data Entry'!$I$15:$I$1048576,"Pending") | Rows whose status is exactly Pending. |
Total Revenue is written up, not collected. The samples total $754, but only $349 comes from Paid rows; the Pending carrot order, the Partial spinach order and the Overdue watermelon order make up the rest. For paid revenue only, replace Setting!I13 with this SUMIFS formula:
=SUMIFS('Data Entry'!$H$15:$H$1048576,'Data Entry'!$I$15:$I$1048576,"Paid")Paid plus Pending will not equal the record count. Only two of the six statuses have a card, so on the samples 3 + 1 falls short of 6. Add =COUNTIF('Data Entry'!$I$15:$I$1048576,"Overdue") as a fifth card if overdue buyers matter to you.
Count Record IDs, not items. To make the first card count saved records, use =COUNTA('Data Entry'!$B$15:$B$1048576).
Amount is typed. There is no Unit Price column, and the macro copies the Amount box straight into column H. Work out kilograms x price per kg before you type it, or add a price input named in_Unit_Price and change the Amount line in Module1 to sh.Range("H" & r).Value = sh.Range("in_Quantity_kg").Value * sh.Range("in_Unit_Price").Value.
The lists do not grow on their own. How To Use says every dropdown updates when you add rows, but the named ranges are fixed – CategoryList is Setting!$A$3:$A$12, SupplierList $C$3:$C$14, Payment_StatusList $E$3:$E$8 – and all three are full. Insert a row inside a list to add a value, or redefine the name as =OFFSET(Setting!$A$3,0,0,COUNTA(Setting!$A$3:$A$100),1).
Excel Workbook vs. Google Sheets Log vs. Produce POS Software – Feature Comparison
| Feature | Fruit and Vegetable Sales workbook (Excel) | Home-made Google Sheets log | Grocery / produce POS 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, till or browser |
| Setup time | About 10 minutes | Hours of design work | Days of onboarding |
| Entry form with Add / Update / Delete | Yes, VBA | No | Yes |
| Real-time team collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Usually |
| Customizable lists and fields | Fully unlocked | Yes | Vendor settings only |
| Scale link, stock control, tax invoices | No | No | Usually |
| Year-1 cost at 5 users | One purchase per user, no renewal | Free | Subscription x 12 months |
For a produce stall or small wholesaler that wants a searchable sales register without paying for a POS it will not fully use, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Greengrocers, farm shops and market stalls recording each sale and whether it has been paid
- Small produce wholesalers selling by the kilogram to regular restaurant or retail buyers
- Owners comfortable in Excel who want a file they fully control
Not a fit if:
- You need a till, barcode or scale connection, stock levels, or GST/VAT invoices
- You need accounting, traceability or food-safety records
- Your team works on Mac, in a browser or in Google Sheets, or needs several people editing at once
Real-World Use Cases
Ravi runs a two-counter greengrocery. He logs each wholesale order to local restaurants with its kilograms and amount, leaves it Pending until payment arrives, and checks the Pending Orders card every Friday before calling customers.
Maria sells at a weekend farmers’ market. She keeps her three regular farms in the Supplier list and filters the table by supplier at month end to see whose produce sold best by weight.
A small fruit wholesaler keeps one register per season, adds an Overdue card and switches Total Revenue to the Paid-only formula so the top of the sheet matches the cash received.
Advantages
- One purchase, no subscription and no per-user fee.
- Ten minutes from download to first real sale.
- Update and Delete work by Record ID, so sorting or filtering the table does not break them.
- Everything is visible and editable – formulas, lists and macro.
Opportunities for Improvement
- A Unit Price column with Amount calculated as Quantity x Unit Price.
- A paid-only revenue card, and cards for Partial and Overdue.
- Self-extending dropdown lists to match what How To Use promises.
- A record-count card based on Record ID. The Pending Orders card is also red although How To Use describes pending cards as amber.
Best Practices
- Always add sales through the form so every row gets a Record ID; rows typed straight into the table cannot be updated or deleted by the buttons.
- Rewrite the Category and Supplier lists before you enter real sales, inserting rows inside each block.
- Quantity is labelled kg – convert bunches or pieces, or rename the column.
- Keep a dated backup copy; the in-table dropdowns cover rows 15-214 and Delete removes the whole worksheet row.
Macros and Compatibility
Macros must be enabled and Windows desktop Excel is required. On first open click Enable Content; Microsoft’s guide to enabling macros in Microsoft 365 files covers the security bar, and its note on macros from the internet being blocked explains the Unblock checkbox.
Explore Relevant Templates
If you like this format, the Bakery Sales Register Data Entry System in Excel and the Optical Shop Sales Data Entry System in Excel apply the same form-and-cards build to other counters, and the Furniture Sales Register walkthrough covers a higher-value sale. Growers can start one step earlier with the Harvest Record Data Entry System in Excel.
In the store, the Grocery Delivery Services KPI Dashboard in Excel and Hypermarkets KPI Dashboard in Excel suit businesses that have outgrown a simple register.
Frequently Asked Questions
What does the Fruit and Vegetable Sales Data Entry System in Excel record?
One row per produce sale: Item, Category, Supplier, Sale Date, Quantity in kilograms, Amount and Payment Status, plus an automatic Record ID and Entry TimeStamp. Four cards show total records, total revenue, paid orders and pending orders.
How long does setup take?
About ten minutes. Enable macros, replace the sample categories and suppliers on the Setting sheet, delete the six sample rows and start adding sales. Nothing needs installing.
Do I need to know VBA?
No. The Add, Update, Delete and Reset buttons are already wired. VBA only matters if you want to change what a button writes, such as calculating Amount from a price per kilogram.
Why is Total Revenue higher than the cash I collected?
The card adds every Amount whatever the Payment Status. On the samples it shows $754 against $349 from Paid rows. Swap in the SUMIFS formula above to count Paid sales only.
Can it print invoices or connect to a scale?
No. It is a sales register only – no invoices, GST/VAT, barcode, scale or stock features. Use POS or billing software for those and this workbook for a simple, searchable record.
How does it compare to produce POS software?
POS software runs tills, scales and stock for a monthly fee. This is a one-time $6.99 Excel file that records what was sold, to what value and whether it was paid.
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 produce counter notebook into a table you can sort and filter, with four numbers on top that keep themselves current. Know what those numbers count – the Item column, written-up rather than collected revenue, and two statuses out of six – and it will not surprise you.
Click here to Purchase the Fruit and Vegetable 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
