Item details are the fixed facts that name a product. Set these once in your Excel template and rarely change them.
List SKU, item name, description, category, and unit of measure (each, case, or pallet).
Add the supplier name and the supplier part number.
Set naming rules, such as all caps for SKUs, so sorting works.
A rule like "BOLT-M8-50" for every bolt keeps related items side by side.
Quantity and Location Columns
Quantity and location columns show what you hold and where it sits. Every inventory sheet needs them.
Quantity on hand is the count on the shelf.
Quantity on order is stock you bought but have not received.
Quantity committed is stock promised to open orders.
Warehouse, aisle, shelf, or bin tells pickers where to walk.
A clear bin code cuts search time on every pick.
Cost and Value Columns
Cost columns hold unit cost, selling price, and total stock value. Match unit cost to the way your accounting software records it, such as average cost.
If the books use average cost, the sheet should too.
Use one currency format with the same number of decimal places in every cell.
Total stock value is quantity on hand times unit cost, so use a formula.
A stock value you cannot trace to the books is a number nobody trusts.
Reorder Point and Reorder Quantity Columns
A reorder point is the stock level that tells you to buy more. Add it to the sheet as its own column. The formula is daily use times lead time in days, plus safety stock.
Lead time is the wait from order to delivery. Safety stock is a spare cushion.
Example: you sell 10 a day. Delivery takes 5 days. Safety stock is 20. The reorder point is 10 x 5 + 20 = 70.
How Do You Set Up an Inventory Spreadsheet in Excel?
Format the range as a table so filters and formulas spread to new rows.
Set number formats for counts, dollars, and dates.
Enter 10 to 20 items and test every formula.
Save a clean master copy before real use.
Any excel inventory template you copy from elsewhere should pass the same test.
Which Formulas Help Track Inventory in Excel?
Three plain formulas run most of a warehouse inventory sheet. Type each one in a cell and copy it down.
=E2*F2 gives total value: quantity on hand times unit cost.
=IF(E2<=H2,"Reorder","OK") flags any item at or below its reorder point.
=E2+G2-I2 gives available stock: on hand plus on order minus committed.
Check one row by hand with a calculator. A formula you have tested once is a formula you can trust.
`=E2*F2` gives total value: quantity on hand times unit cost. `=IF(E2<=H2,"Reorder","OK")` flags any item at or below its reorder point. `=E2+G2-I2` gives available stock: on hand plus on order minus committed.
Not sure this is the right shape for your operation
Describe how the work runs today. We map it on a call and show you what it would look like built around that, before you spend anything.
A low stock alert in Excel is a color rule. It turns a row red when quantity falls to the reorder point.
Select all data rows.
Open Conditional Formatting and choose New Rule.
Pick "Use a formula" and enter =$E2<=$H2.
Choose a red fill and save.
Use 2 or 3 colors at most. Red means buy now. Yellow means close. Few colors keep the alert meaningful.
Drop-down lists stop typing errors. Use Data Validation on category, location, and supplier cells. Then "Bin A-12" and "bin a12" never split a filter into two groups.
Select all data rows; pick "Use a formula" and enter `=$E2<=$H2`; choose a red fill and save.
How Do You Track Stock Counts and Run a Cycle Count?
A cycle count is a small count done each week. It replaces one big count a year. The tax agency sets the reason. In IRS Publication 538, "you must value your inventory at the beginning and end of each tax year."
Build a stock count spreadsheet in two parts:
A log tab holds date, SKU, quantity, reason, and the initials of the person.
A summary tab pulls totals with =SUMIF(Log!B:B,A2,Log!C:C).
Count 5 to 10 bins a week. For example, log counts on 1/6/2025, 1/13/2025, and 1/20/2025. Record the gap and its reason. Fix the cause, such as a mislabeled bin.
How Do You Keep Accounting Software and Spreadsheet Inventory Matched?
Accounting software and spreadsheet inventory stay matched when you name one source for quantities and one for costs. Pick them first. Then follow a schedule.
Reconcile on a set day each week or month.
Enter each sale in one place only so no sale is counted twice.
Compare total stock value in both files and chase any gap.
Good inventory control with Excel depends on one owner for each number.
Reconcile on a set day each week or month. Enter each sale in one place only so no sale is counted twice. Compare total stock value in both files and chase any gap.
The comparison is easier when one option is built for you
Off-the-shelf means fitting your process to the software. We do it the other way round, and the first look costs nothing.
What Mistakes and Warning Signs Show the Sheet Is Outgrown?
Spreadsheet trouble has two kinds of cause. The first is habit.
Many copies get emailed around and nobody knows which is current.
Merged cells and blank rows break filters.
No one owns updates and no change history exists.
The second is size. Several people can edit one file at the same time. The spreadsheet maker's support page covers co-authoring. Still, watch for these signs:
Staff overwrite each other or wait for the file to open.
Counts drift from the shelf.
Reorders happen by memory or email.
Staff rekey data between spreadsheets, paper, and accounting software.
Rekeying 2 people x 5 hours a week at $22 an hour costs $11,440 a year. Any one of these signs means the sheet needs work.
What to Do When Excel Is No Longer Enough
A business should move past Excel when manual steps cost more than the sheet saves. You have 3 options:
Tighten the sheet with drop-downs, one owner, and one master copy.
Add a simple database for shared, live stock.
Have a custom tool built around your current workflow.
Replace only the manual steps. Keep what works. Ask any provider about scope, timeline, and how your data imports. Ask what stays in your accounting software. Fix the slowest step first.
Quick Checklist Before You Start
The checklist below covers the whole inventory spreadsheet build in 6 items.
List every item with a SKU.
Pick the columns and keep one fact in each.
Set the formulas and test one row by hand.
Set the red alert and the drop-downs.
Name one owner for updates.
Schedule weekly cycle counts.
Start with a small batch and grow from there.
Frequently asked questions
Should I build an inventory sheet or find a free one?
Build your own if your stock has odd units, bins, or supplier rules. A free sheet is fine to learn from. Check every column and formula before you trust it with real stock.
How many items can an Excel inventory sheet handle?
Excel holds over 1 million rows per sheet. Speed and errors fail first. Most teams feel strain at a few thousand SKUs, or when several people edit daily.
Can several people use one inventory spreadsheet?
Yes. Store the file in the cloud and use co-authoring so people edit together. Give one person ownership, and add a log tab so every change has a name and date.
Start with a free first look
A 30 minute call, your operation mapped, and a clear picture of what we would build. No obligation and nothing to install.