The Tyre Shop Stock Data Entry System in Excel is a macro-enabled workbook with a 7-field entry form, 4 VBA buttons, 4 live stat cards and 4 dropdown lists holding 40 values – 12 tyre brands, 12 tyre sizes, 10 tyre types and 6 stock statuses. Every saved tyre line gets an automatic Record ID in the TSS-0001 series and an Entry TimeStamp, and the records table has formatted, dropdown-ready rows for 200 lines.
Plenty of tyre shops still keep stock on a whiteboard or in a notebook by the counter. It works until a customer rings asking for a 205/55 R16 and someone has to walk the racks to find out. This walkthrough covers every sheet, exactly what each stat card counts, and the limits worth knowing before you set it up for your own shop.
What the Tyre Shop Stock Data Entry System in Excel Does
It is an .xlsm workbook with four sheets. You fill a form, click Add, and the tyre line 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 records are added, updated or deleted.
It is deliberately small. It is not a point-of-sale till, not a purchase-order or reorder system, not a fitting or job-card tool and not accounting software. The six sample lines and prices are fictional, the brand names are editable dropdown values with no affiliation to any tyre maker, and the workbook makes no tyre-safety, tread-depth or compliance claim.
Key Features
- Seven-field form: Tyre Brand, Tyre Size, Tyre Type, Quantity, Purchase Price, Selling Price and Stock Status.
- Four VBA buttons: Add, Update, Delete (with a confirmation that shows the Record ID) and Reset.
- Automatic Record IDs: the next ID is one more than the highest ID still in the table.
- 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, Tyre Brand, Tyre Size, Tyre Type, Quantity, Purchase Price, Selling Price, Stock Status and Entry TimeStamp. With the samples loaded the cards read 6 records, $873 stock value, 1 out-of-stock item and 2 low-stock items.
Setting sheet
Four lists drive every dropdown: Tyre Brand List (Michelin, Bridgestone, Goodyear, Pirelli, Continental, Dunlop and six more), Tyre Size List (twelve sizes from 175/65 R14 to 265/35 R20), Tyre Type List (All Season, Summer, Winter, Performance, All Terrain, Mud Terrain, Highway, Touring, Run Flat, Off Road) and Stock Status List (In Stock, Low Stock, Out of Stock, On Order, Reserved, Discontinued). 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 Records | COUNTA('Data Entry'!$C$15:$C$1048576) | Counts the Tyre Brand column, not Record ID. A row typed into the table without an ID is counted, though Update and Delete cannot find it. |
| Total Stock Value | SUM('Data Entry'!$H$15:$H$1048576) | One Selling Price per row added together – Quantity and Stock Status are ignored. |
| Out of Stock Items | COUNTIF('Data Entry'!$I$15:$I$1048576,"Out of Stock") | Rows whose status is exactly Out of Stock. |
| Low Stock Items | COUNTIF('Data Entry'!$I$15:$I$1048576,"Low Stock") | Rows whose status is exactly Low Stock. |
Total Stock Value is a sum of unit prices, not a stock value. The samples show $873, which is six selling prices added up. The 64 tyres actually on hand are worth $9,115.40 at selling price and $5,455.00 at purchase price. To multiply quantity by price, replace Setting!K13 with this SUMPRODUCT formula, or swap column H for G to value stock at cost:
=SUMPRODUCT('Data Entry'!$F$15:$F$5000,'Data Entry'!$H$15:$H$5000)Stock Status is chosen by hand. Nothing links it to Quantity and there is no reorder level, so a line with 0 tyres can still read In Stock. The Out of Stock and Low Stock cards count the status you picked. A helper column such as =IF(B15="","",IF(F15=0,"Out of Stock",IF(F15<=6,"Low Stock","In Stock"))) flags lines whose status no longer matches the quantity.
Only two of six statuses have a card. In Stock, On Order, Reserved and Discontinued are not carded. Add =COUNTIF('Data Entry'!$I$15:$I$1048576,"On Order") as a fifth card if supplier deliveries matter to you.
Count Record IDs, not brands. To make the first card count saved records, use =COUNTA('Data Entry'!$B$15:$B$1048576).
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 – Tyre_BrandList is Setting!$A$3:$A$14, Tyre_SizeList $C$3:$C$14, Tyre_TypeList $E$3:$E$12, Stock_StatusList $G$3:$G$8 – and all four 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).
Entry TimeStamp means last saved. The macro writes the current time on every Add and every Update, so an edited line loses its original entry time.
Excel Workbook vs. Google Sheets Log vs. Tyre Inventory Software – Feature Comparison
| Feature | Tyre Shop Stock workbook (Excel) | Home-made Google Sheets log | Tyre and auto-parts inventory 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 or browser |
| Setup time | About 10 minutes | Hours of design work | Days of onboarding |
| Entry form with Add / Update / Delete | Yes, VBA | No | Yes |
| Real-time team collaboration | No | Yes | Yes |
| Mobile access | No | Yes | Usually |
| Customizable lists and fields | Fully unlocked | Yes | Vendor settings only |
| Barcodes, reorder alerts, invoicing | No | No | Usually |
| Year-1 cost at 5 users | One purchase per user, no renewal | Free | Subscription x 12 months |
For a small tyre shop that wants a searchable stock list without paying for inventory software it will not fully use, this workbook sits in the sweet spot.
Who Should Use This Template
Perfect for:
- Independent tyre shops and tyre-and-alignment garages keeping one line per brand, size and type
- Small dealers who want purchase and selling prices side by side for each size
- Owners comfortable in Excel who want a file they fully control
Not a fit if:
- You need a till, barcode scanning, purchase orders, reorder alerts or tax invoices
- You must track DOT or manufacture date codes, rack locations, suppliers or serial numbers
- Your team works on Mac, in a browser or in Google Sheets, or needs several people editing at once
Real-World Use Cases
Imran runs a two-bay tyre and alignment garage. He keeps one line per size he stocks, updates the quantity after each morning delivery, and checks the Low Stock Items card before placing his weekly order with the wholesaler.
Claire sells winter and all-season tyres from a rural workshop. She filters the table by Tyre Type in October to see how many winter tyres she has, and compares purchase and selling price per size before setting seasonal prices.
A small tyre dealer switches Total Stock Value to SUMPRODUCT and adds an On Order card, so the top of the sheet shows what the racks are really worth and how many lines are waiting on a supplier.
Advantages
- One purchase, no subscription and no per-user fee.
- Ten minutes from download to your first real stock line.
- 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
- A true stock value card that multiplies Quantity by price, at cost and at selling price.
- A reorder level per line with Stock Status calculated from Quantity.
- Cards for On Order and Reserved, and a record count based on Record ID.
- Self-extending dropdown lists to match what How To Use promises, and a separate Last Updated column so the original entry time survives an edit.
Best Practices
- Always add stock through the form so every row gets a Record ID; rows typed straight into the table cannot be updated or deleted by the buttons.
- Rewrite the brand, size and type lists before entering real stock, inserting rows inside each block.
- Keep Quantity and both prices numeric – the macro only checks that Tyre Brand is filled.
- Change Stock Status whenever you change a quantity, and keep a dated backup copy; the in-table dropdowns cover rows 15-214 and Delete removes the whole worksheet row.
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 Hardware Store Inventory Data Entry System in Excel and the Tile Showroom Stock Data Entry System in Excel apply the same form-and-cards build to other stock rooms, and the Paint Shop Stock walkthrough covers another retail counter. For a simpler, formula-only approach, see the Inventory Excel Stock Tracker.
In the store, the Auto Parts Inventory Management System Web App and the Auto Garage Job Card Management System Web App suit businesses that have outgrown a single Excel file.
Frequently Asked Questions
What does the Tyre Shop Stock Data Entry System in Excel record?
One row per tyre line: Tyre Brand, Tyre Size, Tyre Type, Quantity, Purchase Price, Selling Price and Stock Status, plus an automatic Record ID and Entry TimeStamp. Four cards show total records, total stock value, out-of-stock items and low-stock items.
How long does setup take?
About ten minutes. Enable macros, replace the sample brands, sizes and types on the Setting sheet, delete the six sample rows and start adding stock. 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 a reorder level field.
Why is Total Stock Value lower than my stock is worth?
The card adds one Selling Price per row and ignores Quantity. On the samples it shows $873 against $9,115.40 for the 64 tyres on hand. Swap in the SUMPRODUCT formula above to multiply quantity by price.
Does it warn me when a size is running low?
Only if you set the status yourself. Stock Status is a dropdown with no link to Quantity and no reorder level, so update it when stock changes or add the helper formula above.
How does it compare to tyre inventory software?
Inventory software runs barcodes, purchase orders and invoices for a monthly fee. This is a one-time $6.99 Excel file that records what you carry, how many and at what price.
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 counter notebook into a tyre stock table you can sort and filter, with four numbers on top that keep themselves current. Know what those numbers count – brands rather than IDs, unit prices rather than stock value, and statuses you choose rather than quantities – and it will not surprise you.
Click here to Purchase the Tyre Shop Stock 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
