The Sweet Shop 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 29 values – 12 sweet categories, 9 payment modes and 8 order statuses. Every saved sale gets an automatic Record ID in the SSS-0001 series and an Entry TimeStamp.
Most sweet shops start with a counter notebook. It works until someone asks what the shop sold last week, which gift-box orders are still unpaid, or whether fudge outsells toffee, and the answer means flicking through pages of handwriting. This walkthrough covers every sheet, exactly what the four stat cards count, the workbook limits we found when we read its formulas and macros, and a one-line fix for each.
What the Sweet Shop Sales Data Entry System 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 above the table recount themselves as records are added, updated or deleted.
It is deliberately small. It is not a point-of-sale system, not a billing or GST/VAT invoicing tool, not inventory software and not accounting software, and it makes no food-safety claim. It is the sales register a small confectionery counter keeps so the weekly numbers are one glance away.
Key Features
- Seven form fields: Sale Date, Item Name, Category, Quantity, Amount, Payment Mode and Status.
- Four live buttons: Add, Update, Delete and Reset, all running VBA from one module.
- 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.
- Stable Record IDs: each new ID is one more than the highest already used, so numbers are never reused after a delete.
- Sweet shop categories: Chocolates, Gummies, Hard Candy, Lollipops, Toffees, Fudge, Marshmallows, Caramels, Licorice, Jellybeans, Truffles and Mints.
- Four stat cards: Total Sales Records, Total Revenue, Paid Orders and Pending 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 Sales Records, Total Revenue, Paid Orders and Pending Orders cards sit on the left, the seven-field form in the middle and the Add, Delete, Update and Reset buttons on the right. Below them is a ten-column records table: S.No., Record ID, Sale Date, Item Name, Category, Quantity, Amount, Payment Mode, Status and Entry TimeStamp. On the sample data the cards read 6, $120, 3 and 2.
The S.No. column is a formula, =IF($B15="","",ROW()-14), so it numbers itself and stays blank on empty rows. The Category, Payment Mode and Status columns carry the same dropdowns as the form, so a backlog of 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.
- Category (12): Chocolates, Gummies, Hard Candy, Lollipops, Toffees, Fudge, Marshmallows, Caramels, Licorice, Jellybeans, Truffles, Mints.
- Payment Mode (9): Cash, Credit Card, Debit Card, Mobile Wallet, Gift Card, Bank Transfer, Store Credit, QR Payment, Voucher.
- Status (8): Paid, Pending, Partially Paid, Refunded, Cancelled, On Hold, Completed, Delivered.
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 Sales Records | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Sale Date column. The Add macro requires a Sale Date, so every saved sale is counted; a row typed into the grid with no date is not. |
| Total Revenue | SUM('Data Entry'!$G$15:$G$1048576) | Every Amount whatever the status – Pending, Refunded and Cancelled included. Value logged, not cash collected. |
| 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. |
Paid plus Pending will not equal Total Sales Records. Only two of the eight statuses have a card. On the six samples that is 3 + 2 against 6, because the Completed Salted Caramel Fudge sale is counted by neither. Add a COUNTIF card for any other status you care about, such as Refunded or Partially Paid.
Total Revenue is logged, not banked. The samples total $120.00, of which $56.75 is Paid, $17.50 Completed and $45.75 still Pending. For settled sales only, replace Setting!I13 with:
=SUM(SUMIFS('Data Entry'!$G$15:$G$1048576,'Data Entry'!$I$15:$I$1048576,{"Paid","Completed","Delivered"}))Microsoft’s SUMIFS function reference explains the syntax if you want to change the status list.
Amount is typed, not calculated. The form has Quantity but no Unit Price, and the Add macro copies Amount as typed, so a Quantity of 3 and an Amount of $14.85 are never cross-checked. Enter the line total, or add a Unit Price column and turn Amount into =F15*K15.
Sweet Shop Sales Data Entry System in Excel vs. Google Sheets Log vs. Retail POS Software – Feature Comparison
| Feature | Sweet Shop Sales Data Entry System in Excel | Home-made Google Sheets log | Retail POS software (Square, Lightspeed) |
|---|---|---|---|
| Cost | $6.99 one-time (regular $11.99) | Free, but you build it | Monthly subscription plus hardware |
| Platform | Excel for Windows desktop | Any browser | Vendor app or tablet |
| Setup time | About 10 minutes | Hours of design work | Days of onboarding |
| Entry form with Add / Update / Delete | Yes, VBA | No | Yes |
| Real-time collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Yes |
| Customizable lists | Fully unlocked | Yes | Vendor settings only |
| Receipts, tax invoices, stock control | No | No | Usually included |
For a small sweet counter that wants a searchable sales register without paying for POS software it will not fully use, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Independent sweet shops, candy stores and chocolate boutiques keeping a daily sales register
- Market stalls and festival pop-ups tracking which payment modes customers use
- Owners who want paid and pending gift-box orders visible at a glance
Not a fit if:
- You need a till, receipts, GST/VAT invoices, stock deduction or accounting
- You need food-safety, batch or expiry records – there are no such fields
- You work on a Mac, in Excel for the web or in Google Sheets
Real-World Use Cases
Anita runs a family sweet shop. She logs each chocolate bar and toffee pack sold, marks advance gift-box orders Pending, and switches them to Paid when the customer collects, checking the Pending Orders card before she locks up.
Tom sells fudge and truffles at weekend markets. At month-end he filters the table by Category to see which lines sell, then trims his Category List to the flavours worth making again.
A mall candy kiosk filters the Payment Mode column at closing time to split cash, card, mobile wallet and QR payments instead of adding them up on paper.
Advantages
- Consistent records: dropdowns stop the same category being typed three different ways.
- Safe editing: Update and Delete work by Record ID, so sorting the table never breaks an edit.
- 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 Paid and Pending are carded; Refunded and Partially Paid deserve their own COUNTIF cards.
- Status-aware revenue. Total Revenue includes Pending, Refunded and Cancelled rows. The SUMIFS above fixes that.
- Quantity x unit price. Amount is typed rather than calculated.
- Self-extending lists. The three named ranges are fixed and already full – CategoryList
Setting!$A$3:$A$14, Payment_ModeList$C$3:$C$11and StatusList$E$3:$E$10. The How To Use page says every dropdown updates on its own, but a value typed below a list will not appear. Fix: redefine each in Formulas > Name Manager as=OFFSET(Setting!$A$3,0,0,COUNTA(Setting!$A$3:$A$100),1)(change the column for the other two). - Dropdown depth. In-table validation covers rows 15 to 214, so drag it down past 200 records.
Best Practices
- Rewrite the three lists before your first real sale, and add values by inserting rows inside each list.
- Always fill Sale Date – the Add button insists, and it keeps Total Sales Records accurate.
- Delete the six fictional sample rows first; their Entry TimeStamps even predate their Sale Dates.
- Start a fresh copy each year and keep a backup, since the file lives on one machine.
Macros and Requirements
This is a macro-enabled workbook, so macros must be enabled and Excel for Windows desktop is required. 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 its drop-down list guide covers the validation behind the dropdowns. Excel for the web and Excel for Mac are not supported.
Explore Relevant Templates
- 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 – a sales register for an eyewear counter.
- Furniture Sales Register Data Entry System in Excel – another retail sales register.
- Garment Shop Sales Data Entry System in Excel – the clothing-counter edition in the store.
Need a real till for several staff? The General Store POS Web App is the multi-user step up.
Frequently Asked Questions
What does the Sweet Shop Sales Data Entry System in Excel record?
One row per sale with Sale Date, Item Name, Category, Quantity, Amount, Payment Mode and Status, plus an automatic Record ID and Entry TimeStamp. Four cards summarise Total Sales Records, Total Revenue, Paid Orders and Pending Orders as records change.
How long does setup take?
About ten minutes. Enable macros, replace the three Setting lists with your own categories, payment modes and statuses, delete the six sample rows and start adding sales. No formula needs editing unless you apply the fixes above.
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 in Excel for Windows.
Can it print receipts or GST invoices?
No. It is a sales register, not a POS, billing, invoicing, inventory or accounting system, and it makes no food-safety or FSSAI claim. Keep receipts and tax invoices in your till or accounting software.
Why is Total Revenue higher than the cash I collected?
It sums every Amount whatever the status, including Pending, Partially Paid, Refunded and Cancelled rows. On the samples it shows $120 while Paid sales come to $56.75. Use the SUMIFS formula above for settled sales only.
How does it compare to POS software?
POS software adds a till, receipts, stock control 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 sweet counter notebook into a table you can sort and filter, with four numbers on top that keep themselves current. Know what those numbers count – every row with a date, logged rather than collected revenue, and two statuses out of eight – and apply the one-line fixes where they matter to your shop.
👉 Click here to Purchase the Sweet Shop Sales Data Entry System in Excel
✅ Instant download · One-time payment · No subscription
🎥 Video tutorials: Youtube.com/@PKAnExcelExpert
Last updated: September 2026
