Inventory list in Excel
Inventory list in Excel
How many times have you had to walk to the warehouse, count boxes by hand, and come back to your desk with a number on a piece of paper that nobody else understands? In small businesses, inventory usually lives in the owner's or the manager's head, and that works until two customers arrive at once or a new employee starts. An inventory list in Excel is one of the simplest tools to bring order to that information: it is a file that almost anyone can learn to handle, it costs no extra money, and it can become the foundation of your warehouse control.
What is an inventory list?
An inventory list is an organized record of the products the company has to sell or to use, with the data needed to identify them, locate them, and value them. It is not an accounting document and it does not replace an inventory card or kardex: it is an organized snapshot of what is on hand at a given moment. It is updated every time goods come in or go out, and it serves as the point of comparison when you run a physical count.
Keeping it in Excel has three advantages over a notebook: the information can be filtered and sorted in seconds, calculations are done with formulas that never make mistakes when adding, and the file can be printed or shared with the person doing the count in the warehouse. The key is to design it well from the start, with clear columns and simple rules, so that anyone can maintain it without depending on one person's memory.
The essential columns
A well-built list does not need fifty columns. Nine well-used columns are enough for most small businesses, because each one answers a concrete question: what the product is, where it is, how much there is, and how much it is worth.
| Column | Example | What it is for |
|---|---|---|
| Code | PRO-001 | Identifies the product with a short, unique text so it is not confused with a similar one. |
| Description | 3/4 inch screw x 2 (box of 100) | Explains exactly what the product is, including its presentation. |
| Group | Hardware, lubricants, packaging | Groups similar products so you can filter, count, and analyze by family. |
| Unit of measure | Unit, box, gallon, meter, kilo | Prevents mixing quantities that cannot be compared with each other. |
| Location | Warehouse A - Shelf 2 - Level 1 | Tells you where to find the product without searching the whole warehouse. |
| Current quantity | 25 | The units available today, according to the last count or movement. |
| Unit cost | 2,400 | The price at which the product's last receipt was purchased or valued. |
| Total value | 60,000 | The unit cost multiplied by the quantity; it is calculated with a formula. |
| Minimum stock | 10 | The level below which you should reorder so you do not run out of the product. |
How to build it, step by step
Building the list does not take more than an afternoon, but it is worth doing it in an orderly way so you do not have to fix everything later:
- Design the structure: create a sheet called Inventory and type the columns in the first row. Keep the first column for the code, because that is the one you will later use to search and relate data.
- Turn on data validation: in the Unit of measure column, use the Data menu and choose Data validation with a list of allowed values: Unit, Box, Gallon, Meter, Kilo. That way nobody writes boxes one day and boxs the next, and filters work properly.
- Write the formulas: in the Total value cell of the first row type the multiplication between quantity and unit cost, for example =F2*G2 depending on the order of your columns, and drag the cell down to copy it to every product.
- Add a totals row: at the end of the table use the SUM function, for example =SUM(F2:F200) for the units and =SUM(H2:H200) for the total inventory value.
- Freeze the headers and apply filters: with Freeze panes the headings stay visible, and filters let you see only one group or the products that have already reached their minimum stock.
- Protect it against mistakes: lock the formula columns and leave only the cells that are filled in by hand, such as quantity and cost, editable, so nobody deletes a formula by accident.
With these rules the sheet looks like this in practice. The last row and the total value column use formulas, so results update by themselves when someone corrects a quantity:
| Code | Description | Quantity | Unit cost | Total value |
|---|---|---|---|---|
| PRO-001 | 3/4 inch screw x 2 (box of 100) | 25 | 2,400 | 60,000 |
| PRO-002 | Clear packing tape (roll) | 40 | 1,100 | 44,000 |
| PRO-003 | Reinforced cardboard box (unit) | 120 | 850 | 102,000 |
| TOTAL | 185 units | 206,000 |
In every Total value cell we wrote the formula that multiplies quantity by unit cost, and in the TOTAL row we used =SUM over both columns. When someone corrects a quantity or a cost, the totals change by themselves and the list never shows an outdated sum.
How to use the list for physical counts
The list proves its worth when it is time to count, because the physical count is the only way to confirm that what the paper says matches what is actually on the shelves:
- Count by location: print the list sorted by the Location column and walk through the warehouse in that same order, so you do not pass the same shelf twice or skip any shelf at all.
- Count by groups: if the warehouse is large, count one product family at a time and mark each finished group so you always know where you are.
- Record the differences: write the counted quantity next to each product and compare it with the current one. A shortage can be theft, shrinkage, or an unrecorded sale; a surplus can be a purchasing or recording error.
- Use cycle counting: instead of counting everything once a year, count part of the inventory every week, for example the fastest-moving products or the ones below their minimum stock. Sorting the list by that column tells you where to start.
A golden rule: the person who counts should not be the same person who keeps the list, because when the same hand records and verifies, errors reproduce themselves without anyone noticing. If the business is so small that there is no second person, at least count calmly, do two passes on the highest-value products, and sign the list at the end of every session.
Caring for a file that becomes critical
When the list becomes the business's source of truth, the file stops being a draft and deserves proper care. These are the most common risks and how to avoid them:
- Files that get damaged or lost: a broken USB drive or a failing hard disk can erase months of work. Always keep a backup copy somewhere else and keep dated versions, such as Inventory_2026_09_01.
- Several people editing: if two employees open the same file in a shared folder, whoever saves last can overwrite the other one's changes. Decide who is responsible for the list and have everyone else send that person the information to record.
- Formula errors: a row inserted in the wrong place or a blank cell can change the totals without being obvious. Check that the sums match the purchase invoices and the physical count.
- Dirty data: different descriptions for the same product or duplicated codes make it appear twice. Before adding a row, use the search to confirm that the product does not already exist.
When should you move to inventory software?
Excel is an excellent first stage, but it has a natural limit: when the business no longer needs only the stock snapshot, but also the history of every movement, who it bought from, who it sold to, at what cost, and with which valuation method. If you manage hundreds of products, if several people ask for inventory information every day, or if your accountant asks for a valued inventory card that the spreadsheet cannot reliably produce, it is time to take the next step. Programs such as Kardex Tauro, designed for businesses with up to 50 employees, were created for that transition point: they handle receipts, issues, and costs without you having to build formulas or protect a file.
And the work done in Excel is not wasted: Kardex Tauro lets you import the opening inventory from a spreadsheet through the Import Inventory option. The columns you already have, such as code, description, quantity, and cost, become the program's starting point without typing everything again. Your list stops being the final destination of the data and becomes the doorway into a system that keeps control for you.
Start with the simple things: design the nine columns, fill a sheet with your real products, and run your first count with it. The order you build today, even in a spreadsheet, is the same foundation that will later let you manage your inventory with more powerful tools, without starting from zero again.