A stationery shop is deceptively hard to count. One shelf of pens is six brands in four ink colours and three tip sizes, the paper aisle turns over in reams, and half the stock is small enough that nobody notices it has gone until a customer asks. Most small shops end up with a paper register that is accurate on the day it is written and wrong a fortnight later. The Stationery Shop Stock Data Entry System in Excel is a 65,759-byte macro-enabled workbook with four sheets that turns that register into one seven-field form: type the item, click Add, and the row is written with its own Record ID and a timestamp. This walkthrough covers exactly what the workbook does, what it does not do, and the four card behaviours you should understand before you trust the numbers at the top of the sheet.
Key Features of the Stationery Shop Stock Data Entry System in Excel
- Seven-field entry form – Item Name, Category, Brand, Supplier, Stock Qty, Unit Price and Stock Status, sitting in one panel above the table.
- Four real VBA buttons – Add, Update, Delete and Reset, written as macros inside the
.xlsmrather than as worksheet formulas. - Automatic Record IDs and timestamps – each saved line gets a sequential
SSS-0001ID and an Entry TimeStamp such as 02-Jun-2026 10:10:00 AM. - Update by ID, not by row position – double-click a record to load it into the form; the macro remembers the ID and writes back to the right row even after you sort or filter.
- Four linked stat cards – Total Items, Inventory Value, Low Stock Items and Out of Stock, all linked pictures of live cells on the Setting sheet.
- Four editable dropdown lists – 12 categories, 12 brands, 10 suppliers and 5 stock statuses.
- Ten-column register across 200 pre-validated rows (rows 15 to 214).
Sheet-by-Sheet Walkthrough
1. Data Entry
This is the sheet you live on. Across the top sit the four stat cards – Total Items, Inventory Value, Low Stock Items and Out of Stock – then the seven-field form, then the four coloured buttons on the right. Below them, a ten-column table: S.No., Record ID, Item Name, Category, Brand, Supplier, Stock Qty, Unit Price, Stock Status and Entry TimeStamp.
The workbook ships with six sample lines so you can see the behaviour before you commit: Ball Pen Blue 0.7mm (Pilot, 240 units at $0.85), A4 Copy Paper 80gsm Ream (Deli, 18 at $5.50), Spiral Notebook A5 200pg (Camlin, 65 at $2.25), Permanent Marker Black (Staedtler, 0 at $1.40), Scientific Calculator 991 (Casio, 12 at $18.99) and Sticky Notes 3×3 Pack (3M, 130 at $1.75). Delete them once you have had a look.
The S.No. column is a formula – =IF($B15="","",ROW()-14) – so it numbers itself only where a Record ID exists. Everything else on the row is written by the macro or chosen from a dropdown.
2. Setting
Four source lists sit side by side here: Category List (Writing Instruments, Paper Products, Filing & Storage, Desk Accessories, Art Supplies, Adhesives & Tapes, Cutting Tools, Markers & Highlighters, Notebooks & Diaries, Envelopes & Mailing, Calculators, Printer Supplies), Brand List (Faber-Castell, Pilot, Staedtler, Parker, Bic, Camlin, Maped, Pentel, Reynolds, Deli, Casio, 3M), Supplier List (Metro Office Supplies, PaperWorld Distributors, Global Stationers Ltd, Prime Wholesale Co, OfficeHub Traders, Sunrise Paper Mills, Apex Stationery Depot, City Books & Supplies, Everest Trading, Blueline Wholesalers) and Stock Status List (In Stock, Low Stock, Out of Stock, Reorder, Discontinued).
To the right of the lists live the four real KPI cells that the Data Entry cards are pictures of. Their formulas are worth reading in full, because two of them are not what the card labels suggest:
- Total Items:
=COUNTA('Data Entry'!$C$15:$C$1048576) - Inventory Value:
=SUM('Data Entry'!$H$15:$H$1048576) - Low Stock Items:
=COUNTIF('Data Entry'!$I$15:$I$1048576,"Low Stock") - Out of Stock:
=COUNTIF('Data Entry'!$I$15:$I$1048576,"Out of Stock")
3. Instructions
A plain How To Use page: entering records, updating without re-selecting the row, deleting with a confirmation that names the Record ID, how the stat cards are linked pictures, where the dropdowns come from, and the macro security step – Enable Content on the yellow bar, and right-click > Properties > Unblock if the file came from e-mail or a download.
4. Get More Templates
A links sheet back to the NextGenTemplates catalogue. Nothing functional, and nothing you need to keep.
Stationery Shop Stock Data Entry System vs. Google Sheets vs. Zoho Inventory – Feature Comparison
| What matters | This workbook | A Google Sheets stock list | Zoho Inventory / NetSuite ERP |
|---|---|---|---|
| Cost | $6.99 once | Free, but you build it | $39 – $300+ per month |
| Platform | Excel for Windows desktop, macros on | Any browser | Browser and mobile apps |
| Setup time | Under 5 minutes | 1 – 3 hours of building | Days, often with onboarding |
| Multi-user editing | No | Yes | Yes |
| Supplier on every line | Yes, from a 10-name dropdown | Only if you add the column | Yes, with full vendor records |
| Barcode scanning | No | No | Yes |
| Automatic reorder alerts | No – status is set by hand | No, unless you build it | Yes |
| Purchase orders / GRN | No | No | Yes |
| Year-1 cost at 5 users | $6.99 | $0 plus your build time | $2,340 – $18,000 |
Who Should Use This Template
It suits a single-branch stationery or book shop, a school supplies counter, a printing and photocopy shop that also sells over the counter, and an office storeroom that issues pens and paper to departments. If you have been running a paper stock register and want the same discipline in one file – one you can sort, filter and hand to an accountant at month end – this is the right size of tool.
It does not suit a chain that needs one shared stock figure across branches, a shop selling online that needs live quantities pushed to a storefront, or anyone expecting barcodes, purchase orders, GST or VAT handling, or automatic reordering. It is a register, not an inventory ERP, and nothing in it decrements Stock Qty when you sell something.
Real-World Use Cases
Anjali, single-store stationery shop. Roughly 400 lines across pens, paper and files. Every Monday she walks the shelves with a laptop, updates Stock Qty, and flips anything down to a box or two to “Low Stock” so the card at the top tells her how many lines need a call.
Ravi, school supplies counter. He sorts by Category and Supplier, so when the new-session rush starts he can pull every Paper Products line from Sunrise Paper Mills into one order in seconds.
Fatima, office storeroom keeper. She logs each delivery as it arrives with its unit price, and uses the Reorder and Discontinued statuses to separate what is genuinely on the shelf from what she has stopped stocking.
Advantages of This Workbook
- No subscription and no account. One
.xlsmfile you own outright and can copy to a second machine. - Update by ID is genuinely better than update by row. Most form-and-table templates write back to whatever row is selected, which quietly corrupts data after a sort. This one carries the Record ID in a hidden cell and finds the record by ID.
- The supplier column earns its place. A small register that records who you bought from turns a stock count into a reorder call list.
- Everything is visible and editable. The lists are cells, the cards are ordinary formulas on the Setting sheet, and the VBA is unlocked, so you can adapt any of it.
- Dropdowns already cover 200 rows, so the first hundred entries need no formatting work at all.
Opportunities for Improvement
These are real, they are in the shipped file, and you should know them before you buy rather than after.
- “Inventory Value” does not value your inventory. It is
SUMof the Unit Price column – no quantity multiplier, no status filter. On the six sample lines it reads $31 (the unit prices total $30.74, displayed with no decimals), while those same six lines at their listed quantities are worth $904.63. Replace the formula withSUMPRODUCT('Data Entry'!G15:G214,'Data Entry'!H15:H214)if you want a true stock-at-cost figure. - Only two of the five statuses have a card. Low Stock and Out of Stock are counted. In Stock, Reorder and Discontinued are not, so 2 + 1 will never reconcile to the 6 on Total Items.
- Total Items counts the Item Name column, not the Record ID. A row saved without a name is stored but never counted.
- The four named ranges do not auto-extend. The Instructions sheet says the dropdowns update on their own, but CategoryList is
Setting!$A$3:$A$14, BrandList$C$3:$C$14, SupplierList$E$3:$E$12and Stock_StatusList$G$3:$G$7– each exactly as long as the list shipped. Type a 13th brand under the last one and it will not appear in the dropdown until you widen the range in Formulas > Name Manager, or insert the new row inside the existing range instead of below it.Stationery Shop Stock Data Entry System in Excel - Stock Qty is never totalled. There is no units-on-hand figure anywhere.
- Validation stops at row 214. Two hundred records, after which you copy the formatting down yourself.
- The Instructions “Stat cards” paragraph is generic boilerplate that mentions income, expense and overdue colours which do not exist in this workbook. Cosmetic only, but it reads oddly.
Best Practices
- Fix the Inventory Value formula on day one, before anyone quotes the card at you in a stock meeting.
- Rewrite the four lists on the Setting sheet before you enter a single record – the categories and suppliers in the file are samples, not a taxonomy you have to accept.
- Widen the named ranges to a few rows past your lists, so future additions land inside the range instead of below it.
- Agree what “Low Stock” means for your shop – a number of boxes, not a feeling – and apply it the same way every week, since the card only counts what you have marked.
- Keep one file, on one machine, backed up. It is a single-user desktop workbook; two people editing two copies is how registers diverge.
- Save a copy at each month end. There is no history table, so a saved copy is your only audit trail.Stationery Shop Stock Data Entry System in Excel
Explore Relevant Templates
- Footwear Shop Stock Data Entry System in Excel – the same build for a shoe shop’s size and colour runs.
- Salon Product Stock Data Entry System in Excel – retail product stock with the identical four-button workflow.
- Product Inventory Data Entry System in Excel – the general-purpose version of the same register.
- Photocopy Shop Sales Data Entry System in Excel – the selling side of the same counter.
- Mobile Accessory Sales Data Entry System in Excel – small-ticket retail lines, sales rather than stock.
- Uniform Sales Data Entry System in Excel – sizes and quantities on the selling side.
- Pharmacy Stock Expiry Calendar in Excel – if your shelves also carry dated stock.Stationery Shop Stock Data Entry System in Excel
Frequently Asked Questions
Does it warn me when an item runs low?
No. There is no reorder point, no formula and no alert. Stock Status is a dropdown you set by hand, and the Low Stock Items card counts the rows you have marked “Low Stock”.Stationery Shop Stock Data Entry System in Excel
Is Inventory Value the value of my stock?
No. It adds the Unit Price column only, so on the sample data it reads $31 against a true stock-at-cost of $904.63. Multiply quantity by price yourself, or swap in a SUMPRODUCT.Stationery Shop Stock Data Entry System in Excel
Will it work in Excel for the web or on a Mac?
Excel for the web cannot run VBA, so the buttons are dead there. Excel for Mac runs VBA but with known gaps, so this is sold as an Excel for Windows desktop tool.Stationery Shop Stock Data Entry System in Excel
Does it handle billing, GST or barcodes?
No. There is no sales, returns, margin, tax or barcode field, and nothing reduces Stock Qty automatically.
Can two staff use it at the same time?
No. It is one desktop file with macros – single user.Stationery Shop Stock Data Entry System in Excel
How many items can it hold?
The dropdown validation and formatting are laid down for 200 rows, 15 to 214. The card formulas read the whole column, so rows beyond 214 still count once you extend the formatting yourself.Stationery Shop Stock Data Entry System in Excel
Can I add my own categories, brands and suppliers?
Yes – edit the four lists on the Setting sheet, then widen the matching named range in Name Manager so the dropdowns pick up the new rows.Stationery Shop Stock Data Entry System in Excel
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.Stationery Shop Stock Data Entry System in Excel
Conclusion
If you run a stationery counter and your stock lives in a notebook, this workbook is a five-minute upgrade with a genuinely useful supplier column and an update-by-ID macro that most templates at this price get wrong. Go in knowing that the Inventory Value card is a price total rather than a valuation, that three of the five statuses have no counter, and that the dropdown ranges need widening before you grow the lists – all fixable in a few minutes, and all documented above rather than discovered later.Stationery Shop Stock Data Entry System in Excel
Get it here: Stationery Shop Stock Data Entry System in Excel on NextGenTemplates, and watch the build walkthroughs at youtube.com/@PKAnExcelExpert.Stationery Shop Stock Data Entry System in Excel
