Inventory Tracking Excel
Automatically calculates remaining stock from incoming and outgoing movements, the low-stock alert, and total stock value.
How do you track inventory with Excel?
Inventory tracking with Excel is built on opening one row per product and entering incoming and outgoing quantities, calculating remaining stock with the =In−Out formula, and colouring products that fall below the critical level with conditional formatting. A properly built sheet does a job close to paid software for up to a few hundred products.
Which columns do you really need?
Too many columns make a sheet impossible to keep filled in. An inventory sheet should have at least these fields: product code, product name, unit, quantity in, quantity out, remaining stock, reorder level and purchase price. Shelf/location and last movement date make it easier to find items and catch mistakes.
Having a product code is critical: it lets you match items across your stock count, price list and order sheets. Without a code, the same product multiplies under different spellings such as "A4 paper" and "A4 Paper 80gsm". You can find how to set codes up (category prefix + running number, variant code, an automatic code formula in Excel) in our guide on how to assign stock codes.
Basic formulas
For remaining stock use =E2-F2; to avoid seeing zeros on empty rows, =IF(A2="","",E2-F2) is preferable. For the alert column, =IF(G2<=0,"OUT OF STOCK",IF(G2<=H2,"CRITICAL","OK")) is enough. Stock value is =G2*J2, and the total value is found with =SUM(L2:L300).
To count critical products use =COUNTIF(I2:I300,"CRITICAL"), and for the total of a single category use =SUMIF(C2:C300,"Cleaning",G2:G300). In Turkish-language Excel these functions are called EĞER, EĞERSAY and ETOPLA — the template you download opens without problems in either language.
Setting up the reorder-level alert
For each product, write the answer to "what is the minimum quantity I should have on hand?" in the reorder-level column. To work out this number, multiply average daily sales, lead time and a safety margin: daily sales × lead time (days) × 1.2 is a practical starting point.
Then go to Home → Conditional Formatting and add a color rule to the status column. In the templates you can download, these rules come ready-made.
Common mistakes
Everyone opening a single file: when two people write at the same time, data gets lost. For shared editing, keep the file on OneDrive/Google Drive.
Formula rows running out: when adding a new row, remember to drag the formula down.
Not making backups: at the end of each month, save a copy of the file named with the date.
Writing the balance instead of the movement: just typing "40 left" makes it impossible to find errors later; record every movement as a row.
When is Excel not enough?
When you see these signs, the spreadsheet has started to slow you down: your product count has passed a few thousand, more than one person enters data at the same time, the number of branches/warehouses has grown, you need fast barcode entry, or sales and stock are kept in separate places.
At this point, a program that keeps stock, customer accounts and invoices in one place saves time and lowers the risk of errors. Your Excel file isn't wasted when you switch; most programs support .xlsx import.
Frequently asked questions
Is Excel enough for inventory tracking?
Up to a few hundred products with a single person entering data, it is more than enough. It falls short for multi-user, multi-warehouse businesses or where barcode speed matters.
Google Sheets or Excel?
If several people will be entering data at the same time, Google Sheets is more convenient. If you have heavy formulas and large data, Excel is faster.
Where should I start with inventory tracking?
Do a stock count and set your opening quantities, then record every stock in and out consistently. If the opening balance isn't right, the sheet will never match reality.