Yes—a free food inventory spreadsheet can be made in Excel with columns for each item, unit, quantity on hand, reorder level, cost, expiry date, and supplier. Add simple formulas to flag low stock and calculate inventory value; the layout below is a practical template you can copy into a blank workbook and adapt.
What the template should track
A useful food inventory sheet answers three everyday questions: what is in stock, how much is available, and what needs attention next? Start with one row per item or, for foods with different expiry dates or batches, one row per lot. These columns cover the basics:
- Item: A clear name, such as canned tomatoes or whole milk.
- Category: For example, produce, dairy, dry goods, frozen, or packaging.
- Unit: The unit used to count the stock, such as each, case, kilogram, or litre.
- Location: A shelf, storeroom, refrigerator, or freezer location.
- Quantity on hand: The amount counted during the latest stock check.
- Reorder level: The minimum quantity that should prompt a purchase or restock.
- Unit cost: The purchase cost for the same unit used in the quantity field.
- Expiry date: The date shown on the package, entered as an actual Excel date.
- Supplier and lot/batch: Helpful for ordering and tracing stock when needed.
- Calculated fields: Stock value, reorder status, and an expiry reminder.
Keep units consistent. If a row says quantity 4 and unit case, its unit cost should be the cost per case—not the cost per individual can. If you need to track both case and individual quantities, decide on a standard unit or record the conversion clearly. Mixing units is a common cause of misleading totals.
A copyable column layout
In a blank workbook, put these headings across row 1 in this order: Item, Category, Unit, Location, Qty on hand, Reorder level, Unit cost, Expiry date, Supplier, Lot/batch, Stock value, Stock status, Expiry status. Enter one stock record per row below the headings. For example, a row might describe canned tomatoes in cases, located on a dry-goods shelf, with 6 cases on hand and a reorder level of 4.
If all units of an item share the same expiry date, one row per item may be enough. If a delivery arrives with a different date or lot number, use a separate row for that batch. Otherwise, a single item row can hide the fact that some units should be used before others. Add columns such as last counted, count by, or notes if you need an audit trail, but avoid collecting details nobody will maintain.
For a simple home pantry, you may not need supplier, location, or lot fields. A café, food pantry, caterer, or small shop is more likely to benefit from them. The best template is the smallest one that reliably supports your actual buying, storage, and counting routine.
Set it up in Excel for Microsoft 365
On Excel for Microsoft 365 for Windows, enter the headings in row 1 and the first stock records beneath them. Select the range and press Ctrl+T, confirm that “My table has headers” is selected, and choose OK. An Excel Table makes it easier to filter records, sort by expiry, and extend formatting and formulas as new rows are added. On a Mac, use Insert > Table if the Windows shortcut is not applicable; the ribbon layout can differ slightly by platform.
Format the data so it is readable and entered consistently:
- Format Qty on hand and Reorder level as numbers, using decimal places only when your units require them.
- Format Unit cost and Stock value as currency, and use the currency that matches your purchasing records.
- Format Expiry date as a date, such as a day-month-year format that staff will not confuse.
- Use short, consistent category and unit names. A drop-down list can reduce variations such as “kg,” “kilo,” and “kilogram.”
To freeze the headings while scrolling, choose View > Freeze Panes > Freeze Top Row on Windows. Use the equivalent Freeze Panes command on Mac if the workbook is maintained there. You can filter the table headings to show one category, location, supplier, or date range at a time.
Formulas for value and reorder alerts
Formula assumptions: Qty on hand is in E2 and Unit cost is in G2. Put this formula in K2 under Stock value: =E2*G2. If E2 contains 8 kilograms and G2 is $1.25 per kilogram, the result is $10.00. This is an illustrative stock-at-cost figure, not a selling price or a complete accounting valuation. Fill the formula down the column; when the data is an Excel Table, Excel will usually carry a calculated-column formula into its rows.
Formula assumptions: Qty on hand is in E2 and Reorder level is in F2. Put this in L2 under Stock status: =IF(E2<=F2,"REORDER","OK"). With 8 on hand and a reorder level of 10, it returns REORDER; with 12 on hand, it returns OK. Set the threshold based on how quickly the food is used and how long replenishment takes. A reorder flag is a prompt to review, not an automatic purchase order.
Formula assumptions: Expiry date is in H2, and you want a reminder for dates within the next seven days. Put this in M2 under Expiry status: =IF(H2="","No date",IF(H2<TODAY(),"Expired",IF(H2<=TODAY()+7,"Use soon","OK"))). It returns No date for a blank cell, Expired for a date before today, Use soon for a date from today through seven days ahead, and OK for a later date. For example, if today is November 3, 2026, and H2 is November 7, 2026, the result is Use soon. Enter real Excel dates, not text that merely looks like a date. The seven-day window is adjustable to your workflow, not a food-safety rule.
To make the results easier to spot, select the relevant status cells and choose Home > Conditional Formatting > Highlight Cells Rules. You can apply a visible color to “REORDER” or “Use soon.” Do not rely on color alone: retain the text status so the sheet remains understandable when printed or viewed by someone who cannot distinguish the colors.
Make the sheet useful for food rotation
Inventory counting and stock rotation work best together. When the same food has several dates, record separate lots and sort by expiry date so older stock is easy to identify. Many operations use a first-expiring, first-out approach for dated goods: use or issue the lot with the earliest relevant date first, while following the product label and local requirements. This is not always identical to first-in, first-out, because a newer delivery could have an earlier date.
Choose a regular time and method for counts. A small pantry might be updated after shopping; a food business may count high-use or high-value items more often than slow-moving supplies. Record a count date if quantities change frequently. Without it, a number that was accurate last week may look current even after several meals, sales, or deliveries.
Keep the inventory record distinct from temperature logs, cleaning schedules, allergen controls, and other food-safety records. A spreadsheet can help identify stock that needs review, but it cannot establish that food was stored safely or determine whether a product is safe to eat. Follow package directions and applicable guidance from the relevant food-safety authority. Date-label meanings and requirements can differ by product and jurisdiction, so do not treat a spreadsheet status as a substitute for that guidance.
Choosing reorder levels and review routines
A reorder level should reflect real use and replenishment time, rather than an arbitrary round number. If a kitchen uses about two cases of a staple between deliveries, it may choose a threshold that gives enough time to place the next order. Account for delivery schedules, minimum order quantities, seasonal demand, storage capacity, and the risk of spoilage. The right level can change, so review it after repeated stockouts or persistent over-ordering.
Decide who may update quantities, prices, and thresholds. If multiple people edit the workbook, use a shared location with appropriate access controls and agree on a counting convention. For instance, a partly used case should not be recorded as a full case by one person and as individual units by another. Add a brief note in the workbook explaining the unit conventions and when counts are due.
For a basic workflow, staff can filter for REORDER, check the physical stock, confirm the supplier and quantity, and then record the received goods after delivery. Updating the sheet only when a purchase is made can leave the on-hand count inaccurate; receiving, waste, transfers, and routine use may all affect the actual balance.
Common spreadsheet problems to avoid
- Using inconsistent names: “Tomatoes,” “tomato tin,” and “canned tomato” may become three records. Agree on one item name or use a product code.
- Combining different lots: This can conceal an earlier expiry date. Separate the lots when their dates or traceability details differ.
- Typing numbers as text: A quantity entered with an unexpected unit or leading apostrophe may not calculate. Keep units in their own column and quantities numeric.
- Leaving old counts in place: Add a count date, and make stale records easy to identify during review.
- Assuming a formula proves safety: An expiry alert only compares dates in cells. It does not assess packaging, handling, temperature history, or product condition.
- Overbuilding the workbook: Too many fields can make updates slow. Start with the information needed to count and reorder, then add fields only when they solve a real problem.
Keep a backup before making major changes, especially if the workbook is the only record of stock. If the file is shared, agree how to handle simultaneous edits and protect formula columns from accidental overwriting. A spreadsheet is flexible, but its reliability depends on careful, consistent updates.
Spreadsheet versus inventory apps
A spreadsheet is a good fit when the list is modest, stock changes can be entered manually, and the people maintaining it are comfortable with rows, filters, and formulas. It costs nothing beyond access to spreadsheet software if you build it yourself, and it is easy to tailor to household supplies or a small operation. However, it does not automatically know what was used, sold, wasted, or received unless someone records those events.
Dedicated inventory systems may offer features such as barcode scanning, purchase-order workflows, multiple-user permissions, or links to sales and accounting tools. Those features can reduce manual work but may involve subscription costs, setup, training, or a more complicated process. Compare the features you genuinely need, total cost, export options, and how the system handles lots and expiry dates before switching. A spreadsheet can also serve as a starting point for defining item names and units before adopting another system.
Frequently Asked Questions
Where can I get a free food inventory spreadsheet template?
You can create one in a blank Excel workbook using the headings and formulas above, without paying for a specialized template. If you download a file from another source, review its formulas, links, and permissions before entering business or personal data.
How do I track food expiry dates in Excel?
Enter each date as a real Excel date in an Expiry date column. Use separate rows for batches with different dates, then sort or filter that column. A formula can flag upcoming dates for review, but it cannot determine whether food is safe.
Should I use one row per food item or one row per batch?
Use one row per item when its stock can be managed as a single group. Use one row per batch or lot when dates, lot codes, locations, or traceability need to remain distinct.
Can Excel automatically tell me when to reorder food?
Excel can compare a recorded quantity with a reorder level and display a flag. It will not know about unrecorded use, waste, or deliveries, so someone must keep the on-hand count current and check the flag before ordering.
Can I use this template for a restaurant or food pantry?
Yes, as a starting point. Add fields such as storage location, supplier, lot or batch, and count date if they support your process. Use separate records and systems for any required safety, temperature, or compliance documentation, and check the rules that apply to your operation.
Related
- Visual Budget Spreadsheet Template: Build a Monthly Plan
- PowerPoint: Animate Text One Line at a Time
- Compare Data Between Two Excel Sheets: Methods That Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace Part of a String in a Cell or RangeTo replace part of a string in Excel, press Ctrl+H on Windows, enter the text to find and…
- Track Changes in Word: A Step-by-Step GuideTo track edits in Word, open the document, select Review > Track Changes, and then edit as usual.…
- Excel Named Range Manager: How to View and Edit NamesIn Excel for Microsoft 365 on Windows, open Formulas > Defined Names > Name Manager to view, create,…
- Freeze Heading Row in Excel: Keep Column Labels VisibleTo keep your column labels visible as you scroll in Excel, select View > Freeze Panes > Freeze…
- Meal Planning Spreadsheet Template: Build a Weekly PlanA meal planning spreadsheet template is a weekly grid for choosing meals, recording recipes, and organizing the groceries…
- Report Builder CountRows: Count Rows in a GroupIn Microsoft Report Builder, count rows in a group with =CountRows("GroupName"), replacing GroupName with the tablix group’s actual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.