The Grocery Store Billing Data Entry System in Excel is a macro-enabled workbook with a 7-field bill form, 4 VBA buttons, 4 live stat cards and 3 dropdown lists holding 25 values – 12 item categories, 8 payment modes and 5 bill statuses. Every saved bill gets an automatic Record ID in the GSB-0001 series and an Entry TimeStamp, and the records table has formatted, dropdown-ready rows for 200 bills.
Plenty of small grocery and kirana stores still keep a carbon-copy bill book or a notebook of credit customers. It works until someone asks what the bills added up to last week, or which regular still owes money, and the answer means turning pages. This walkthrough covers every sheet, exactly what each stat card counts, and the limits worth knowing before you set it up for your own store.
What the Grocery Store Billing Data Entry System in Excel Does
It is an .xlsm workbook with four sheets. You type a bill into a form, click Add, and the bill 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 bills are added, updated or deleted.
It is deliberately small, and the word “billing” needs a plain explanation: this is a record of bills you have already raised. It is not a point-of-sale till, it does not scan barcodes or add up items, it does not calculate GST or any other tax, it does not print receipts or invoices, and it does not track stock. The six sample customers, bills and figures are fictional.
Key Features
- Seven-field form: Bill No, Bill Date, Customer Name, Item Category, Bill Amount, Payment Mode and Status.
- Four VBA buttons: Add, Update, Delete (with a confirmation that shows the Record ID) and Reset.
- Record IDs separate from bill numbers: your own Bill No is stored as typed, while the GSB Record ID is what Update and Delete use to find the row.
- 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, Bill No, Bill Date, Customer Name, Item Category, Bill Amount, Payment Mode, Status and Entry TimeStamp. With the samples loaded the cards read 6 bills, $296 total sales, 2 pending bills and 3 paid bills.
Setting sheet
Three lists drive every dropdown: Item Category List (Fruits and Vegetables, Dairy and Eggs, Bakery, Meat and Seafood, Beverages, Snacks and Confectionery, Frozen Foods, Grains and Pulses, Cooking Oil and Spices, Household Supplies, Personal Care, Baby Care), Payment Mode List (Cash, Credit Card, Debit Card, UPI, Net Banking, Mobile Wallet, Gift Card, Store Credit) and Status List (Paid, Pending, Partially Paid, 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 Bills | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Bill No column. Add refuses a blank Bill No, so this matches the bills added through the form. |
| Total Sales | SUM('Data Entry'!$G$15:$G$1048576) | Every Bill Amount whatever the Status – Pending, Partially Paid, Refunded and Cancelled included. |
| Pending Bills | COUNTIF('Data Entry'!$I$15:$I$1048576,"Pending") | Bills whose status is exactly Pending. |
| Paid Bills | COUNTIF('Data Entry'!$I$15:$I$1048576,"Paid") | Bills whose status is exactly Paid. |
Total Sales is billed, not collected. The samples total $295.90 (the card shows $296), but only $149.65 comes from Paid bills; the two pending bills ($65.20 for beverages and $53.15 for household supplies) and the partially paid bakery bill ($27.90) make up the rest. For paid sales only, replace Setting!I13 with this SUMIFS formula:
=SUMIFS('Data Entry'!$G$15:$G$1048576,'Data Entry'!$I$15:$I$1048576,"Paid")Paid plus Pending will not equal the bill count. Only two of the five statuses have a card, so on the samples 3 + 2 falls short of 6. Add =COUNTIF('Data Entry'!$I$15:$I$1048576,"Partially Paid") as a fifth card if credit customers matter to you. There is no amount-received column, so the workbook cannot tell you how much of a partially paid bill is still owed.
Bill Amount is typed. There are no item lines, quantities, unit prices, discounts or tax fields, and the macro copies the Bill Amount box straight into column G. Total the bill on your till or calculator first, then log the final figure.
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 – Item_CategoryList is Setting!$A$3:$A$14, Payment_ModeList $C$3:$C$10, StatusList $E$3:$E$7 – 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).
How the Buttons Behave – Details From the Macro
- Add only insists on Bill No. It does not check for a duplicate bill number, and a blank or unreadable Bill Date is saved as today’s date.
- Record IDs are the highest existing number plus one. Deleting an older bill never causes a repeat, but if you delete the newest bill, the next bill you add reuses its Record ID.
- Update rewrites every field, including the Entry TimeStamp, so the timestamp shows when a bill was last saved rather than first entered.
- Delete removes the whole worksheet row, so the formatted, dropdown-ready block (rows 15-214) shortens by one row each time.
Excel Workbook vs. Google Sheets Log vs. Grocery POS Software – Feature Comparison
| Feature | Grocery Store Billing workbook (Excel) | Home-made Google Sheets log | Grocery / supermarket 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 |
| Barcode scanning, item totals, tax, printed receipts | No | No | Usually |
| Real-time team collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Usually |
| Customizable lists and fields | Fully unlocked | Yes | Vendor settings only |
| Year-1 cost at 5 users | One purchase per user, no renewal | Free | Subscription x 12 months |
For a small grocery store that already bills on a till or by hand and wants a searchable record of those bills, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Small grocery, kirana and convenience stores recording each bill, its main category, the amount and how it was paid
- Shops that give regular customers credit and need to see which bills are still pending
- Owners comfortable in Excel who want a file they fully control
Not a fit if:
- You need a till, barcode scanning, item-by-item bills, GST or tax invoices, printed receipts or stock levels
- You need accounting ledgers or tax-compliant records
- Your team works on Mac, in a browser or in Google Sheets, or needs several people editing at once
Real-World Use Cases
Ramesh runs a neighbourhood kirana store. Regular customers settle at month end, so he logs each bill as Pending, switches it to Paid when the money arrives, and checks the Pending Bills card before closing.
Maria owns a small organic grocery. She keeps Fruits and Vegetables and Dairy and Eggs at the top of the Item Category list and filters the table by category and payment mode at month end to see where her card and UPI takings come from.
A two-counter convenience store adds a Partially Paid card and switches Total Sales to the Paid-only formula so the top of the sheet matches the money received.
Advantages
- One purchase, no subscription and no per-user fee.
- Ten minutes from download to the first real bill.
- 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
- An Amount Received column, so partially paid bills show the balance still owed.
- A paid-only sales card, and cards for Partially Paid, Refunded and Cancelled.
- Self-extending dropdown lists to match what How To Use promises.
- A duplicate Bill No warning, and Record IDs that are never reused after the newest bill is deleted.
- Keeping the first Entry TimeStamp on Update, perhaps with a separate Last Updated column.
Best Practices
- Always add bills through the form so every row gets a Record ID; rows typed straight into the table cannot be loaded back with a double-click.
- Rewrite the Item Category and Payment Mode lists before you enter real bills, inserting rows inside each block.
- Amounts are formatted in US dollars – change the number format on column G and the Total Sales card for your own currency.
- Keep a dated backup copy and store it on a secured PC, because it holds customer names.
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 Electronics Sales Log walkthrough covers another retail counter. For the shopping side, see the Grocery Shopping List Data Entry System in Excel.
In the store, the Grocery Store KPI Dashboard in Excel suits stores that have outgrown a simple register, and the General Store POS Web App is the option if you need a real point-of-sale system.
Frequently Asked Questions
What does the Grocery Store Billing Data Entry System in Excel record?
One row per bill: Bill No, Bill Date, Customer Name, Item Category, Bill Amount, Payment Mode and Status, plus an automatic Record ID and Entry TimeStamp. Four cards show total bills, total sales, pending bills and paid bills.
Can it generate or print grocery bills?
No. It does not add up items, apply GST or other tax, or print receipts or invoices. It records bills you have already raised on a till, in a bill book or elsewhere.
How long does setup take?
About ten minutes. Enable macros, replace the sample categories and payment modes on the Setting sheet, delete the six sample rows and start adding bills. 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 adding an Amount Received field.
Why is Total Sales higher than the cash I collected?
The card adds every Bill Amount whatever the Status. On the samples it shows $296 against $149.65 from Paid bills. Swap in the SUMIFS formula above to count Paid bills only.
How does it compare to grocery POS software?
POS software runs tills, barcode scanners, tax and stock for a monthly fee. This is a one-time $6.99 Excel file that records which bills were raised, for whom, for how much and whether they were 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 grocery bill book into a table you can sort and filter, with four numbers on top that keep themselves current. Know what those numbers count – billed rather than collected sales, a typed Bill Amount and two statuses out of five – and it will not surprise you.
Click here to Purchase the Grocery Store Billing 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
