A copy shop counter generates a lot of very small transactions. Forty photocopies here, a spiral binding there, two passport photos, one banner. Most shops write them in a register or a notebook, and by the end of the week nobody can say how many jobs went out, which ones are still sitting on the shelf waiting for collection, or how much of the day’s total has actually been paid for. The Photocopy Shop Sales Data Entry System in Excel turns that notebook into a form, an ID and four counts.
It is a macro-enabled workbook with exactly four sheets. One entry form of seven fields feeds a records table of ten columns. Every sale gets its own ID in the PSS-0001 series and an entry timestamp written to the minute. Four stat cards sit above the form and recalculate from the table itself – Total Orders, Total Revenue, Completed Orders and Pending Orders. Three dropdown lists ship pre-filled with 12 service types, 8 payment modes and 8 order statuses, and 200 record rows are already formatted and validated before you type anything.
This article walks through what is actually in the file, sheet by sheet, and is equally clear about what is not – including four things in the shipped formulas that you should know about before you quote a number off a card.
Key Features of the Photocopy Shop Sales Data Entry System in Excel
- A seven-field form. Sale Date, Customer Name, Service Type, Quantity, Amount, Payment Mode, Status. That is the whole form – there is no hidden second page.
- Four working buttons. Add (green), Delete (red), Update (amber) and Reset (teal), each bound to a VBA routine.
- Record IDs generated for you. PSS-0001, PSS-0002, and onward. Two walk-in customers with the same name never merge.
- An Entry TimeStamp column. Written automatically when the row is created, separately from the Sale Date you type.
- Update finds the record by ID. Double-click any row to load it back into the form; the macro then locates it by its Record ID wherever it has been sorted to. You never have to click back onto the row first.
- Delete confirms with the ID. The prompt names the record it is about to remove.
- Four live stat cards. Total Orders is a COUNTA, Total Revenue a SUM of Amount, Completed Orders and Pending Orders are COUNTIFs on Status.
- Dropdowns on both the form and the table. Service Type, Payment Mode and Status are validated in the form cells and again down rows 15 to 214 of the table, so typing straight into the grid is just as safe.
- A How To Use sheet inside the file. Six sections, written in plain language.
Sheet-by-Sheet Walkthrough
1. Data Entry
The working sheet. A navy banner across the top, then the four stat cards on the left, the seven-field form in the middle and the four buttons on the right. Below all of that sits the records table with ten columns: S.No., Record ID, Sale Date, Customer Name, Service Type, Quantity, Amount, Payment Mode, Status and Entry TimeStamp.
Six sample sales ship with the file so you can see the buttons work before clearing them out: 120 black and white photocopies at $6.00 in cash, 45 colour prints at $22.50 on UPI, 8 spiral bindings at $16.00 on a credit card, 30 laminations at $45.00 on a debit card, 3 banners at $90.00 by bank transfer and 60 photo prints at $36.00 on a mobile wallet. Their statuses run across Completed, Delivered, Ready for Pickup, Pending and In Progress, which is a fair picture of a real counter mid-afternoon.
The S.No. column is a formula – IF($B15="","",ROW()-14) – so it fills itself in as soon as a Record ID appears, all the way down to row 214.
2. Setting
Three editable lists and the four real stat cards. Service Type List ships with Black & White Photocopy, Color Photocopy, Document Printing, Color Printing, Lamination, Spiral Binding, Hardcover Binding, Scanning, Photo Printing, Banner Printing, ID Card Printing and Passport Photos. Payment Mode List holds Cash, Credit Card, Debit Card, UPI, Bank Transfer, Mobile Wallet, Cheque and Store Credit. Status List is Pending, In Progress, Ready for Pickup, Completed, Delivered, Cancelled, On Hold and Refunded.
The four cards on the Data Entry sheet are linked pictures of the real cards that live in row 13 of this sheet, which is why they update on their own and why restyling one here restyles it there.
3. How To Use
Six sections inside the workbook itself: entering records, updating a record without re-selecting the row, deleting a record, how the stat cards work, editing the dropdown lists, and a reminder that the file is macro-enabled and needs Enable Content on first open.
4. Get More Templates
A single link sheet back to the NextGenTemplates store. Nothing to configure.
Photocopy Shop Sales Data Entry System in Excel vs. Google Sheets vs. Shop Billing Software – Feature Comparison
| This Excel system | A Google Sheets form | Shop billing / POS software | |
|---|---|---|---|
| Cost | $6.99 once | Free, build it yourself | $10-$50 per month |
| Platform | Excel for Windows, desktop | Browser, any device | Desktop, tablet or phone |
| Setup time | About 10 minutes to swap the three lists | Hours of building | Days, plus your price list |
| Prints a customer bill or receipt | No | No | Yes |
| Rate per copy, automatic amount | No – Amount is typed | Build it yourself | Yes |
| Tax or GST handling | No | Build it yourself | Yes |
| Two people entering at once | No – one file, one editor | Yes | Yes |
| Works on a phone at the counter | No – VBA buttons need the desktop app | Yes | Yes |
| Unique ID and timestamp per sale | Yes, automatic | Build it yourself | Yes |
| Owns its own data file | Yes – one .xlsm you keep | In your Google account | In the vendor’s system |
| Year-1 cost | $6.99 | $0 | $120-$600 |
Who Should Use This Template
Use it if you run a single-counter photocopy, printing or stationery shop, a cyber cafe, a college print point or a documentation centre, you are the only person keying the records, and what you want is a tidy register with an ID per job, a timestamp and four counts at the top.
Look elsewhere if you need to hand the customer a printed bill, price jobs from a rate card, charge tax, track what a corporate account owes across a month, or have two people billing at the same counter at once. None of that is in this file, and no amount of configuration will put it there.
Real-World Use Cases
The copy shop beside a college. Forty to sixty small jobs a day. Each one is keyed as it is handed over – service, quantity, amount, payment mode, Pending – and moved to Ready for Pickup once it comes off the machine. Sorting by Status at closing shows exactly what is still on the shelf.
A documentation centre with corporate customers. Binding and lamination jobs go in against the company name with Payment Mode set to Store Credit. At month end the owner filters that column to see what still has to be invoiced outside the workbook.
A stationery shop that also prints. The twelve shipped service types were swapped for the ones actually offered – passport photos, ID cards, banners, thesis binding – and Total Orders becomes the day count that gets written into the cash book.
Advantages of the Photocopy Shop Sales Data Entry System in Excel
- No re-selecting rows. Update works by Record ID, so a sorted or filtered table cannot make you overwrite the wrong job.
- Two dates per row. The Sale Date is what the customer’s job belongs to; the Entry TimeStamp is when it was typed. Backdating a row does not hide when it was entered.
- Validation already in place. Rows 15 to 214 carry the dropdowns and the date, currency and timestamp formats, so a typed “Cmpleted” is rejected rather than quietly breaking the count.
- Nothing to install and nothing to log into. One 64 KB workbook, opened from your own machine, with the VBA left unlocked and readable.
- Cheap to abandon. The data is an ordinary Excel table. If you outgrow the register, it exports to a real POS in one copy and paste.
Opportunities for Improvement
These were read out of the workbook’s own XML rather than guessed from a screenshot. None of them stops the file working, but each one changes how you should read a number.
- Total Revenue counts everything.
Setting!I13 = SUM('Data Entry'!$G$15:$G$1048576)has no status filter. On the shipped sample data the card reads $216 while only $42.00 sits on rows marked Completed. Cancelled and Refunded rows would be added in as well. Subtotal or filter the Amount column by Status when you want cash actually taken. - Only two of eight statuses have a card. Completed and Pending are COUNTIFs; In Progress, Ready for Pickup, Delivered, Cancelled, On Hold and Refunded are counted nowhere – which is why 2 + 1 does not add up to the Total Orders 6 on the sample data. A third COUNTIF card is a two-minute edit on the Setting sheet.
- Total Orders counts the wrong column.
Setting!G13 = COUNTA('Data Entry'!$C$15:$C$1048576)counts Sale Date in column C, not the Record ID in column B. A row saved without a date is stored and never counted. The sheet’s own S.No. formula keys off column B, so pointing the card at B makes it consistent. - The card format hides the cents. The Total Revenue cell is formatted
"$"#,##0, so the sample data’s true $215.50 renders as $216. The underlying value is exact; only the display is rounded. - The dropdown ranges do not auto-extend, and all three are exactly full. Service_TypeList is Setting!$A$3:$A$14 (12 of 12), Payment_ModeList is $C$3:$C$10 (8 of 8) and StatusList is $E$3:$E$10 (8 of 8), while the How To Use sheet says the lists update on their own. Replacing an entry works instantly; adding a 13th service needs the range widened in Formulas > Name Manager first.
- Quantity is never totalled. The six samples come to 266 copies, prints and bindings and nothing anywhere shows it. If volume matters more to you than value, add a SUM of column F beside the revenue card.
- Amount is typed, not calculated. There is no rate card in the file, so nothing multiplies 120 copies by your per-copy price. A rate column plus one formula would fix that, at the cost of editing the VBA that writes the row.
- The sample timestamps sit before the sales. The six demo rows are dated 1 to 14 August 2026 but their Entry TimeStamps read 2 to 7 June 2026. It is demo data only and disappears the moment you delete the sample rows, but do not read the shipped timestamps as a worked example.
Best Practices
- Always give a row a Sale Date – the Total Orders card counts that column, so a dateless row is invisible to it.
- Set your three lists on the Setting sheet before you start entering, so you never have to reconcile “Colour Printing” against “Color Printing” later.
- Use Status honestly: Pending until it is paid for, Ready for Pickup while it waits on the shelf, Completed or Delivered when it has gone. The cards are only as good as that column.
- Keep one workbook per financial year. Two hundred pre-formatted rows is a couple of busy months, and a new file each year keeps the register quick to open.
- Back the .xlsm up somewhere off the counter PC. It is a single file, and a single file is a single point of failure.
- Do not store customer phone numbers or ID scans in it unless you have to. As shipped it holds a name and nothing else that identifies anyone, and it is an ordinary unencrypted workbook.
Explore Relevant Templates
- Uniform Sales Data Entry System in Excel – the same register pattern for a counter selling sized goods.
- Supplement Sales Data Entry System in Excel – product sales instead of print jobs.
- Newspaper Subscription Data Entry System in Excel – recurring subscribers rather than walk-in sales.
- Petty Reimbursement Data Entry System in Excel – the same four buttons applied to small cash claims.
Frequently Asked Questions
Do I have to enable macros?
Yes. Add, Update, Delete and Reset are VBA. Without macros the buttons do nothing, although you can still type into the table because those columns carry the same dropdowns.
Will it run on a Mac, in Excel for the web, or in Google Sheets?
It is built and tested for Microsoft Excel on Windows desktop. Excel for the web cannot run these macro buttons, and Google Sheets cannot run VBA at all.
Does it work out the amount from the number of copies?
No. There is no rate card anywhere in the workbook. Quantity and Amount are two independent fields you key in, which is deliberate for a shop where a banner and a photocopy are priced nothing alike – but it does mean nothing checks your arithmetic.
Can it print a bill for the customer?
No. There is no bill or receipt layout, no tax or GST field and no invoice numbering. This is the shop’s own record of what was sold, not a billing system.
How many sales will it hold?
Rows 15 to 214 are pre-formatted and validated, which is 200 records. You can continue past row 214 but would need to extend the data validation and the S.No. formula yourself.
Can I add a thirteenth service to the dropdown?
Yes, with one extra step. The named range Service_TypeList is fixed at Setting!$A$3:$A$14 and is exactly full, so type your new service under the list and then widen the range in Formulas > Name Manager. The same is true of the eight payment modes and eight statuses.
What happens if I sort the table after adding records?
Nothing breaks. S.No. is a row-position formula that renumbers, and Update and Delete match on Record ID rather than row position – which is exactly why the ID exists.
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
The Photocopy Shop Sales Data Entry System in Excel is a counter register done properly: a seven-field form, four working VBA buttons, an ID and a timestamp you never type, three dropdown lists you control and four counts that refresh themselves. It is not a POS, it prints no bills and it does not price your jobs for you – and the four formula quirks above are worth ten minutes of your time before you rely on a card. If that is the trade you want, it is about ten minutes from download to your first filed job.
Get the Photocopy Shop Sales Data Entry System in Excel on NextGenTemplates
For step-by-step Excel and VBA walkthroughs, subscribe to youtube.com/@PKAnExcelExpert.
