Fruit and Vegetable Sales Data Entry System in Excel

The Fruit and Vegetable Sales Data Entry System in Excel is a macro-enabled workbook for logging produce sales: which item went out, from which supplier, how many kilograms, for what amount and whether it has been paid. It gives you a 7-field entry form, 4 live VBA buttons, 4 self-updating stat cards and 3 editable dropdown lists holding 28 ready values – 10 categories, 12 suppliers and 6 payment statuses. Fill the form, click Add, and the sale lands in a 10-column records table with its own Record ID and timestamp.

It is a sales register, not a point-of-sale till, not a billing or GST/VAT invoicing tool, not a weighing-scale link, not stock control and not accounting software. It answers the questions a greengrocer or produce wholesaler asks at the end of the week – how many sales were logged, how much was written up, and how many orders are paid or still pending – without scrolling back through a notebook.

✅ Instant download · One-time payment · No subscription · No per-user fees · Fully unlocked and editable

🔑 Key Features of the Fruit and Vegetable Sales Data Entry System in Excel

• 🧾 A seven-field entry form – Item, Category, Supplier, Sale Date, Quantity (kg), Amount and Payment Status – placed beside the stat cards on the Data Entry sheet, so one screen covers entry and review.
• 🔘 Four live VBA buttons. Add saves the form as a new record, Update rewrites the record you loaded, Delete removes a record after a confirmation that shows its Record ID, and Reset clears the form without touching the table.
• 🔢 Automatic Record IDs in the FAVS-0001 series plus an Entry TimeStamp on every saved sale. The next ID is always one more than the highest ID in the table, so a number is never reissued after a delete.
• 🖱️ Double-click editing. Double-click any row and it loads back into the form. The macro remembers the Record ID, so Update finds the right row even if you have clicked somewhere else in the meantime.
• 📋 Three dropdown lists on the Setting sheet – Category (Fruit, Vegetable, Herb, Leafy, Root, Berry, Citrus, Melon, Exotic, Organic), Supplier (12 sample farms and traders) and Payment Status (Paid, Pending, Partial, Overdue, Refunded, Cancelled). The same dropdowns sit on the form and on table rows 15-214.
• 📈 Four stat cards – Total Sales Records, Total Revenue, Paid Orders and Pending Orders. They are linked pictures of formula cards on the Setting sheet, so they recalculate the moment a record is added, updated or deleted.
• 🔓 Nothing locked. No sheet protection and no VBA password – rename a column, add a list value or change a card formula yourself.

📦 What’s Inside the Workbook

One .xlsm file with four sheets: Data Entry, Setting, Instructions (headed How To Use) and Get More Templates. Six fictional sample sales are included so you can see the register working before you clear it.

Sheet 1: Data Entry

The working screen. The form and the four buttons sit at the top beside the stat cards; below them is the records table with S.No., Record ID, Item, Category, Supplier, Sale Date, Quantity (kg), Amount, Payment Status and Entry TimeStamp. The six samples read 6 records, $754 revenue, 3 paid orders and 1 pending order.

Sheet 2: Setting

Holds the Category List, Supplier List and Payment Status List that feed every dropdown, plus the four formula cards the Data Entry sheet displays as pictures. Restyle a card here and the picture follows.

Sheet 3: How To Use

Plain-English instructions for entering, updating and deleting records, how the stat cards work, how to edit the dropdown lists, and how to enable the macros the first time you open the file.

📐 What the Four Stat Cards Actually Count

We read the workbook’s own formulas and macro before writing this page. Five things are worth knowing before you rely on the cards, each with a one-line fix:

• 💵 Amount is typed, not calculated. There is no Unit Price field, and Module1 simply copies the Amount box into column H. Work out kilograms x price per kg yourself – or add a price input named in_Unit_Price and change that line to sh.Range("H" & r).Value = sh.Range("in_Quantity_kg").Value * sh.Range("in_Unit_Price").Value.
• 🧮 Total Revenue has no status filter. Setting!I13 = SUM('Data Entry'!$H$15:$H$1048576) adds every Amount, so Pending, Partial, Overdue, Refunded and Cancelled rows are all included. The sample $754 holds only $349 from Paid rows. For paid revenue use =SUMIFS('Data Entry'!$H$15:$H$1048576,'Data Entry'!$I$15:$I$1048576,"Paid").
• 🔢 Total Sales Records counts the Item column. Setting!G13 = COUNTA('Data Entry'!$C$15:$C$1048576), so a row typed straight into the table without a Record ID is still counted, even though Update and Delete cannot find it. Count IDs instead with =COUNTA('Data Entry'!$B$15:$B$1048576).
• 🚦 Only two of six statuses have a card. Paid Orders and Pending Orders are carded; Partial, Overdue, Refunded and Cancelled are not, so on the samples 3 + 1 does not reach 6. Add a card such as =COUNTIF('Data Entry'!$I$15:$I$1048576,"Overdue") for any status you want to watch.
• 📋 The dropdown lists are fixed-length named ranges. CategoryList is Setting!$A$3:$A$12, SupplierList $C$3:$C$14 and Payment_StatusList $E$3:$E$8, and all three are already full. How To Use says the dropdowns update on their own; that is only true when you insert a row inside a list. A value typed below the list never appears. Either insert rows inside the block, or redefine the name as =OFFSET(Setting!$A$3,0,0,COUNTA(Setting!$A$3:$A$100),1).

One cosmetic note: How To Use says pending cards read amber, but the Pending Orders card is red.

📊 This Excel Workbook vs. a Google Sheets Log vs. Produce POS Software

For a small produce stall or wholesaler that wants a tidy, searchable sales register without paying for POS software it will not fully use, this workbook sits in the sweet spot.

👥 Who This Template Is For – and Who It’s Not For

✅ Built for:

• Greengrocers, market stalls and farm shops keeping a daily record of each sale and its payment status
• Small produce wholesalers who sell by the kilogram to a handful of regular buyers and suppliers
• Owners who want to see paid and pending orders at a glance in a file they fully control

❌ NOT for:

• Shops that need a till, barcode or weighing-scale connection, stock levels, or GST/VAT invoices – this is not a POS, billing or inventory system
• Businesses needing accounting ledgers, food-safety or traceability records, or several users editing at once
• Anyone working on a Mac, in Excel for the web or in Google Sheets, where the VBA buttons do not run

⚙️ How to Use the Fruit and Vegetable Sales Register

1. Unzip the download and open Fruit_and_Vegetable_Sales_System.xlsm in Excel for Windows. Click Enable Content; if the file came from a download, right-click it, choose Properties and tick Unblock first.
2. On the Setting sheet, replace the sample categories and suppliers with your own. Each list is full, so insert a row inside the block to add a value.
3. Delete the six sample rows, or keep them while you learn the buttons.
4. Fill Item, Category, Supplier, Sale Date, Quantity (kg), Amount and Payment Status, then click Add. Leave Sale Date blank and today’s date is used.
5. To change a sale, double-click its row, edit the form and click Update.
6. To remove a sale, double-click it (or click any cell in its row), click Delete and confirm the Record ID.
7. Decide whether you want the paid-revenue formula above before you start relying on the Total Revenue card.

💼 Real-World Use Cases

Ravi runs a two-counter greengrocery. He logs each wholesale order to local restaurants with its kilograms and amount, marks it Pending until the cheque clears, and checks the Pending Orders card every Friday before calling customers.

Maria sells at a weekend farmers’ market. She uses the Supplier list for the three farms she buys from and filters the table by supplier at month end to see which farm’s produce sold best by weight.

A small fruit wholesaler keeps one register per season, adds an Overdue card with a single COUNTIF, and switches Total Revenue to Paid-only so the top of the sheet matches the cash actually received.

⚠️ Limits to Know Before You Buy

• 🪟 Macros must be enabled, and Windows desktop Excel is required. The buttons do not run on Mac, in Excel for the web, on mobile or in Google Sheets.
• ⚖️ Quantity is labelled kg. Items sold by the bunch or piece need converting, or rename the column.
• 🗂️ The in-table dropdowns cover rows 15-214, and Delete removes the whole worksheet row, so that formatted block shortens by one row per delete.
• 🧪 The sample items, suppliers and figures are fictional. “Organic” is just an editable category label – the workbook makes no organic, food-safety or FSSAI claim.

❓ Frequently Asked Questions

What does the Fruit and Vegetable Sales Data Entry System in Excel record?

It records 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 stat 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. There is nothing to install and no account to create.

Why is Total Revenue higher than the cash I collected?

Because the card sums every Amount regardless of Payment Status, including Pending, Partial, Overdue, Refunded and Cancelled rows. On the samples it shows $754 while Paid rows total $349. Replace the formula with the SUMIFS version on this page to count Paid sales only.

Does it calculate the amount from quantity and price?

No. Amount is a typed field and there is no Unit Price column. Enter the final amount for each sale, or add a price input and change one line of the macro so Amount becomes Quantity multiplied by Unit Price.

Will it work on a Mac or in Excel for the web?

The sheets open, but the Add, Update, Delete and Reset buttons are VBA macros that need Excel for Windows on the desktop with macros enabled. On a Mac, in a browser or in Google Sheets you can only type directly into the table.

How does this compare to produce POS software?

POS systems handle tills, scales, stock and invoices for a monthly fee. This workbook does none of that; it is a one-time $6.99 sales register for recording what was sold and whether it was paid, in a file you own and can edit.

Is the file locked?

No. There is no sheet protection and no VBA project password, so you can rename columns, extend lists, change card formulas or edit the macro.

👤 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 (@PKAnExcelExpert, @NextGenTemplates, @NeoTechNavigators). Every template is hand-built and tested before release.

🔗 Explore Related Templates

• 🥐 Bakery Sales Register Data Entry System in Excel – the same form-and-cards build for a bakery counter.
• 🌾 Harvest Record Data Entry System in Excel – log what the farm picked before it reaches the sales register.
• 🛒 Grocery Delivery Services KPI Dashboard in Excel and Hypermarkets KPI Dashboard in Excel – KPI tracking once you outgrow a simple register.
• 🧾 Optical Shop Sales, Furniture Sales Register and Garment Shop Sales data entry systems – sales registers for other retail counters.

Browse more Excel VBA tools and Excel sales templates in the store. New to macros? Microsoft explains how to enable or disable macros in Microsoft 365 files.

📖 Click here to read the Detailed Blog Post

🎥 Visit our YouTube channel for step-by-step video tutorials

👉 YouTube.com/@NextGenTemplates

📅 Last updated: September 2026

Watch the demo video: