Cafe Daily Sales Data Entry System in Excel

The Cafe Daily Sales Data Entry System in Excel is a macro-enabled workbook for keeping a record of the sales your cafe counter has already made: the sale date, the item, its menu category, the quantity, the amount, how it was paid and where the order stands. 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 – 12 menu categories, 8 payment modes and 8 order statuses. Fill the form, click Add, and the sale lands in a 10-column records table with its own Record ID and timestamp.

Be clear about what it is: a sales record you type into, not a point-of-sale till or cash register. It does not take payments, add up a basket, calculate tax or tips, print receipts or kitchen tickets, or deduct stock. You ring up the sale the way you do today and log it here, so at the end of the day you can see how many sales lines were entered, what they add up to and how many orders are still pending – in a file you own.

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

🔑 Key Features of the Cafe Daily Sales Data Entry System in Excel

• ☕ A seven-field sales form – Sale Date, Item Name, Category, Quantity, Amount, Payment Mode and Order 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 CDS-0001 series plus an Entry TimeStamp on every saved row, so each sale can be found again even when two rows look identical.
• 🖱️ Double-click editing. Double-click any saved row and it loads back into the form. The macro remembers the Record ID, so Update finds the right row even after you have clicked elsewhere, sorted or filtered the table.
• 📋 Three dropdown lists on the Setting sheet – Category (Hot Coffee, Cold Coffee, Tea, Smoothies, Fresh Juice, Pastries, Sandwiches, Cakes, Cookies, Breakfast, Snacks, Bottled Water), Payment Mode (Cash, Credit Card, Debit Card, Mobile Wallet, UPI, Gift Card, Meal Voucher, Bank Transfer) and Order Status (Completed, Pending, Preparing, Served, Cancelled, Refunded, On Hold, Takeaway). The same dropdowns sit on the form and on table rows 15-214.
• 📈 Four stat cards – Total Orders, Total Sales, Completed 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 category 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 – a Cappuccino, an Iced Latte, a Blueberry Muffin, a Chicken Sandwich, a Mango Smoothie and a Chocolate Cake Slice – 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, Sale Date, Item Name, Category, Quantity, Amount, Payment Mode, Order Status and Entry TimeStamp. The six samples read 6 orders, $58 total sales, 3 completed orders and 1 pending order.

Sheet 2: Setting

Holds the Category List, Payment Mode List and Order 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. A few things are worth knowing before you rely on the cards, each with a one-line fix:

• 💵 Amount is typed, not calculated. Quantity and Amount are two separate boxes. There is no unit price, no Quantity x Price formula and no tax, tip or discount field, and the macro simply copies the Amount box into column G. Enter the line total from your till.
• 🧾 One row is one sales line, not one ticket. Setting!G13 = COUNTA('Data Entry'!$C$15:$C$1048576) counts filled Sale Date cells, and Add refuses a blank Sale Date, so Total Orders is the number of rows you entered. A cappuccino and a muffin logged as two rows count as two orders.
• 🧮 Total Sales has no status filter. Setting!I13 = SUM('Data Entry'!$G$15:$G$1048576) adds every Amount, so Pending, Preparing, Cancelled and Refunded rows are all included. The sample $58 (exactly $58.25) holds $10.50 pending, $15.00 preparing and $6.25 served, and only $26.50 from Completed rows. For completed sales use =SUMIFS('Data Entry'!$G$15:$G$1048576,'Data Entry'!$I$15:$I$1048576,"Completed").
• 🚦 Only two of eight statuses have a card. Completed and Pending are carded; Preparing, Served, Cancelled, Refunded, On Hold and Takeaway are not, so on the samples 3 + 1 does not reach 6. Add a card such as =COUNTIF('Data Entry'!$I$15:$I$1048576,"Refunded") for any status you want to watch.
• 📋 The dropdown lists are fixed-length named ranges. CategoryList is Setting!$A$3:$A$14, Payment_ModeList $C$3:$C$10 and Order_StatusList $E$3:$E$10, and all three are already full. How To Use says every dropdown updates on its own; that is only true when you insert a row inside a list. A value typed below a 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).

📊 This Excel Workbook vs. a Notebook or Sheets Log vs. Cafe POS Software

For a small cafe, coffee kiosk or bakery counter that already rings sales up elsewhere and just wants a tidy, filterable record of those sales and their status, this workbook sits in the sweet spot.

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

✅ Built for:

• Small cafes, coffee kiosks and juice bars keeping a daily record of what sold, in which category, for how much and how it was paid
• Owners who want to see pending and completed orders at a glance in a file they fully control
• Anyone comfortable in Excel who would rather edit a list than learn a POS back office

❌ NOT for:

• Cafes that need a till, card payments, table or kitchen tickets, printed receipts, tax invoices or stock deduction – this is not a POS, cash register or accounting system
• Businesses needing tax-compliant sales records or several staff 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 Cafe Sales Register

1. Unzip the download and open Cafe_Daily_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 payment modes with your own menu groups and tenders. 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 Sale Date, Item Name, Category, Quantity, Amount, Payment Mode and Order Status, then click Add. Sale Date is the one field the macro insists on.
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 completed-sales formula above before you start relying on the Total Sales card.

💼 Real-World Use Cases

Aisha runs a 20-seat neighbourhood coffee shop. At closing she keys in the day’s sales by item from her till roll, filters the table by Category to see how Hot Coffee compares with Pastries, and checks the Pending Orders card for anything still open.

Tom operates a coffee kiosk at a station. He logs each sale as it happens during the quiet hours, uses the Payment Mode column to split cash from UPI and card, and switches Total Sales to the Completed-only formula so refunds stop inflating his total.

A campus cafe adds a Refunded card with a single COUNTIF and moves Takeaway out of the status list into a column of its own, so order type and order progress are tracked separately.

⚠️ 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.
• ☕ One item per row. A ticket with three items is three rows, and nothing groups them back into one order.
• 📅 Add refuses a blank Sale Date, but text that Excel cannot read as a date is saved as today’s date – check the date before you click Add.
• 🔁 Record IDs are the highest existing number plus one, so if you delete the newest sale, the next sale added reuses its Record ID. Deleting an older sale never causes a repeat.
• 🕒 Update rewrites the Entry TimeStamp, so it shows when a row was last saved, not when it was first entered.
• 🗂️ 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 and figures are fictional, and amounts are formatted in US dollars – change the cell format for your own currency.

❓ Frequently Asked Questions

What does the Cafe Daily Sales Data Entry System in Excel record?

It records one row per sales line: Sale Date, Item Name, Category, Quantity, Amount, Payment Mode and Order Status, plus an automatic Record ID and Entry TimeStamp. Four stat cards show total orders, total sales, completed orders and pending orders.

Is it a POS system or cash register?

No. It does not take payments, total a basket, apply tax, print receipts or kitchen tickets, or track stock. It is a record of sales you have already made, for reviewing amounts, categories, payment modes and order status.

Why is Total Sales higher than the money I took?

Because the card sums every Amount regardless of Order Status, including Pending, Preparing, Cancelled and Refunded rows. On the samples it shows $58 while Completed rows total $26.50. Replace the formula with the SUMIFS version on this page to count Completed rows only.

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

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.

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.
• 🍽️ Restaurant Order Data Entry System in Excel – an Excel entry form for restaurant orders.
• ☕ Coffee Cafe POS Web App – if what you actually need is a point-of-sale system rather than a sales register.
• 📈 Coworking Cafes Dashboard in Excel – an analytical dashboard once you want charts rather than a register.
• 🧾 Optical Shop Sales and Electronics Sales Log data entry systems – sales registers for other 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: