Kardex in Excel
Kardex in Excel
The kardex is the record that gives inventory its life: in it, movement by movement, you note the receipts and issues of each product, with their quantities, their costs and the balance left after each transaction. Without that history, a business knows how much money it has in the cash drawer, but it does not know how much merchandise is in the warehouse or what it paid for it.
Many small businesses —shops, workshops, hardware stores and small distributors— start keeping their kardex in Excel. It is an understandable decision: the spreadsheet is already installed, it requires no extra investment, and you can have a working template in an afternoon. That said, for that Excel kardex to be trustworthy you need well-defined columns, correct formulas and a few everyday precautions. That is what this article explains, together with the signs that the business is ready to move to a dedicated inventory program.
What a kardex is and why so many businesses keep it in Excel
A kardex is an individual record for each product. Every reference has its own history: how much came in, on what date, at what cost, how much went out and what balance remained after each movement. With that information you can answer in seconds the questions that repeat every day: how many units are left of the best-selling product, what the goods sold last month really cost, and whether there is merchandise that has not moved for months and is tying up capital.
Excel is the natural starting point because it is already used for budgets, expenses and payroll, and because there is no new system to learn. You can begin with one sheet per product or with a workbook where each tab is one item. The key is not the tool but the discipline: a kardex that is not updated on the same day the movement happens loses all its value, because nobody trusts it anymore.
How to structure a kardex in Excel, column by column
An organized template works when each column has a fixed meaning and each row represents a single movement. The recommended minimum structure is this:
| Column | What it records |
|---|---|
| Date | The day the movement happened. |
| Detail | The document and the concept: purchase invoice, sales delivery note, return or adjustment. |
| Receipts | The quantity received and the unit cost of that purchase. |
| Issues | The quantity dispatched and the cost at which the merchandise leaves. |
| Balance | The units left after the movement and their accumulated cost. |
Two practical rules make the template survive real use. First: one row per movement, never mixing a purchase and a sale on the same line, because afterwards it is impossible to audit. Second: keep totals and formulas separate from the area where data is typed, and freeze the header row so the columns are not lost when scrolling. If the business handles several references, the most comfortable setup is a workbook where each product has its own tab with the same structure.
A numeric example of a kardex in Excel
The way it works is easier to understand with a concrete case. Suppose only one product is controlled, for example a specific line of notebooks, during the month of January. The kardex would look like this:
| Date | Detail | Receipts | Issues | Balance |
|---|---|---|---|---|
| 02/01/2026 | Opening balance | — | — | 50 |
| 09/01/2026 | Purchase, invoice 0012 | 100 | — | 150 |
| 16/01/2026 | Sale, delivery note 0034 | — | 40 | 110 |
| 22/01/2026 | Purchase, invoice 0018 | 200 | — | 310 |
| 29/01/2026 | Sale, delivery note 0051 | — | 90 | 220 |
The balance of each row is the previous row plus receipts minus issues: 50 plus 100 is 150; 150 minus 40 leaves 110, and so on until the month closes at 220 units. That column must not be typed: it must be calculated with a formula.
The basic formulas: balance and average cost
The first formula is the unit balance. If column C holds the receipts, column D the issues and column E the balance, in row 3 the formula would be =E2+C3-D3: it takes the previous balance, adds the receipt and subtracts the issue. When you drag that formula down, Excel adjusts the row reference automatically and the balance calculates itself. The only requirement is that no blank rows interrupt the record, because the formula chain breaks.
The second important formula is the average cost, the method most small businesses use to value inventory. Since each purchase can have a different cost, you cannot simply say what each unit cost: you calculate a weighted average. For example: you buy 100 units at a unit cost of 5,000, so the inventory is valued at 500,000. Then you buy another 100 units at 6,000 and the total value rises to 1,100,000. The average cost is 1,100,000 divided by 200 units, that is, 5,500 per unit. If 60 units go out later, the issue is valued at 60 times 5,500, which is 330,000, and the balance ends at 140 units worth 770,000.
In the sheet, that average is calculated by dividing the total cost of the balance by the units in the balance: if column F stores the unit cost and column G the accumulated total cost, the average cost formula is =G3/E3. For it to work, column G must be updated on every row: when there is a receipt, add quantity times unit cost; when there is an issue, subtract quantity times the current average cost.
Everyday care: typical mistakes in the Excel kardex
The Excel kardex does not fail because of Excel but because of human errors that repeat in every business. The most common ones are these:
- Formula errors. Dragging the balance one row too far, deleting the formula and typing the number by hand, or leaving blank rows that cut the sequence. The result is a balance that does not match and that nobody detects until the physical count.
- Typing wrong figures. An extra zero in a quantity or a mistaken unit cost changes every later calculation. That is why the person typing should check against the original document before saving.
- Duplicate files. The classic one: on Monday you work on Kardex.xlsx and on Tuesday on Kardex final.xlsx, and in the end nobody knows which one is up to date. Use a single folder, a single name and one person responsible for updating.
- No backup. A corrupted spreadsheet or a broken computer takes the whole kardex with it. A weekly backup to the cloud or an external drive is enough to avoid starting from zero.
- No physical check. The kardex must be compared with a periodic physical count. If the sheet says 220 units and the warehouse has 180, there is a recording or control problem worth fixing early.
When it is time to move from Excel to an inventory program
The Excel kardex works while the volume is low and one person updates the file. The breaking point appears when one of these situations shows up: several employees need to record movements at the same time, invoicing happens in a program but inventory stays in the sheet so everything is typed twice, the average cost is recalculated by hand or with formulas nobody understands anymore, or the owners need reliable reports to decide what to buy.
At that point, moving to an inventory program is not a luxury: it is a way to win back working hours and avoid losses caused by badly calculated data. For a small business with up to fifty employees, programs such as Kardex Tauro let you import the opening inventory from the very Excel file you were using. From then on, when you record a purchase the program generates the kardex movement and updates the average cost automatically, with no dependence on someone dragging the right formula.
Keeping the kardex in Excel was a good first step; knowing when to leave it behind is the second. When recording stops being a manual task and happens automatically with each purchase and each sale, inventory becomes a reliable source of information for the business again, and that is exactly what Kardex Tauro solves.