How to Keep Track of Inventory in Excel
Streamline your inventory process and plan your sales needs with a pre-formatted template. Download a basic inventory tracking template in Excel, enter your inventory details, and update the spreadsheet when item quantities, suppliers, costs, or reorder dates change.
Follow this step-by-step guide to track your inventory in Excel:
-
Download the basic inventory tracking template.
- For each item, enter the number and name, and fill in the the fields labeled Description, Supplier, and Location.
- Enter the cost and quantity on hand for each item. The items’ Inventory Value and the Total Inventory Value field will auto-populate.
- Enter the Reorder Limit, or the minimum number of items in stock before you can place a reorder. If the Reorder Limit is equal to or less than the Stock Quantity, the row will automatically change color. This alerts you that it’s time to reorder that item.
- Click the dropdown menu in the Item Discontinued column to select whether an item has been discontinued. When Yes is selected, a strikethrough will be applied to the row.
- After an order is placed, enter the date in the Last Reorder Date column.
Tip: Under the Home tab, use the Sort & Filter feature to sort data from smallest to largest, largest to smallest, or alphabetically. You can also use it to filter out specific columns.
Check out this collection of free inventory templates for Excel or try one of these free inventory form templates to help you streamline inventory tracking and ensure accurate records, and to help you with inventory release, control, adjustments, or discrepancies.
Download the completed inventory tracking template with example data and use it as a guide for what information to include in your spreadsheet.
How to Create an Inventory Tracker in Excel
Create a personalized Excel inventory tracking spreadsheet with custom columns, automated calculations, and conditional formatting for easy data visualization. Tracking inventory in Excel allows you to easily record stock quantities, item locations, prices, and more.
“Excel is a go-to tool for tracking inventory for smaller businesses because it’s accessible, familiar, and flexible,” says Amit Wadhwani, VP of Operations at Ignatiuz Software. “With just a few columns and some basic formulas, you can build a working inventory tracker that gives you visibility into stock levels, reorder points, and item movement.”
Follow this step-by-step guide to create an inventory tracker in Excel:
- Open Excel and click New > Blank workbook.
- Double-click the Sheet1 tab, and enter a name.
- Add column headers to fit your inventory tracking needs. You can also customize the template (e.g., make the headings bold, adjust column widths, or add a background color).
- Apply Currency to the Cost Per Item and Inventory Value columns. Highlight the columns and click the Home tab. Then select Number.
- Under Category, click Currency. Expand the Symbol dropdown, and click $. You can also choose how to display negative numbers by selecting a format under Negative numbers. Click OK.
- Input data, including item numbers, names, descriptions, suppliers, locations, and costs per item.
- Enter a formula that multiplies the Cost Per Item by the Stock Quantity. In the example below, the formula is =F2*G2. Click and drag the formula to apply it to all rows.
- For each item, enter the Stock Quantity. The Inventory Value automatically calculates using the formula from the previous step. This method of reordering stock when it reaches a minimum level is called the minimum stock method.
Check out this complete guide to inventory control to learn more about inventory control methods and identify the best one for managing your inventory.
- Enter the Reorder Limit for each item. This is the least amount available in stock before an item should be reordered.
Add Conditional Formatting to an Excel Inventory Tracker
Apply conditional formatting to highlight rows when the quantity in stock is at or below the Reorder Limit.
- Click and drag your cursor to highlight the data range. In the example below, additional rows are highlighted to plan for inventory growth.
- From the Home tab, click Conditional Formatting > New Rule.
- A New Formatting Rule box will open. Click Use a formula to determine which cells to format, and enter a formula for when the Stock Quantity is equal or less than the Reorder Limit. In the example below, the formula is =AND($G2<=$I2, $G2<>"", $I2<>""). Click Format.
- A Format Cells box will open. Click Fill, and select a background color. Click OK.
- Now when the Stock Quantity is equal to or less than the Reorder Limit, the row will change color. This allows you to easily see when to reorder an item.
Add a Dropdown Menu to an Excel Inventory Tracker
You can add a dropdown menu to any column. In this example, you’ll learn how to add a Yes/No dropdown menu to the Item Discontinued column.
- Click Data in the ribbon. Click the first cell under the Item Discontinued column. Click Data Tools.
- Click Data Validation.
- In the Allow dropdown menu, click List. Enter Yes, No in the Source field, and click OK.
- Click and drag the formatting down the Item Discontinued column to apply it to each cell.
- In the Item Discontinued column, use the dropdown menu to indicate whether an item has been discontinued.
- Click and drag your cursor to highlight the data range.
Apply Strikethrough Conditional Formatting
- From the Home tab, click Conditional Formatting > New Rule.
- A New Formatting Rule box will open. Click Use a formula to determine which cells to format, and enter a formula that determines when the Item Discontinued is Yes. In the example below, the formula is =$J2=”Yes”. Click Format.
- Click Italic under Font style. Under Effects, check Strikethrough. Click OK.
- Now when the Item Discontinued is labeled Yes, italic and strikethrough formatting will be applied to the row.
Add Total Inventory Values and Update Reorder Dates
Follow these steps to calculate total inventory values and update reorder dates.
- Use the SUM function to add the values in the Inventory Value column. Highlight the Inventory Value column.
- Under the Formulas tab, click AutoSum, and then click Sum.
- The total will automatically be calculated in the last highlighted cell. Set the font to bold and fill the background color to easily identify it.
- Fill in the Last Reorder Date column for each item.
- Highlight the Last Reorder Date column. Select the Home tab, and then click Number.
- A Format Cells box will open. Under Category, click Date. Choose the format under Type. Click OK.
- The dates in the Last Reorder Date column will format according to your selection.
Filter Data in an Excel Inventory Sheet
For more functionality, use the FILTER function to view data based on specific criteria. In this example, FILTER is used to create a list of discontinued items.
- Add a Discontinued Items column header a few columns to the right of the Last Reorder Date column.
- In the first cell under Discontinued Items, enter =filter, and double-click FILTER.
- Highlight the Item Name column, and add a comma at the end of the formula.
- Highlight the Item Discontinued column. Enter =“yes” at the end of the formula, and press Enter.
- Now you have a list of discontinued items for quick reference. The list will automatically update when the Item Discontinued column changes.
- Click File > Save As. Name the file, choose a location to store it, and click Save.
Tip: If you need to print your inventory tracking spreadsheet, change the page layout so it fits onto one page. Click Page Layout > Orientation > Landscape. Then click Margins > Custom Margins > Page > Fit to:. Enter 1 in the box, and click OK.
Document your inventory at a specific point in time with one of these free inventory report templates.
Tips for Tracking Inventory in Excel
Tracking inventory in Excel is an effective and affordable way to manage your stock levels, reduce errors, and streamline your operations. Leverage Excel’s built-in features like formulas, conditional formatting, and data validation to create an organized and efficient inventory management system.
Follow these tips to set up a reliable and efficient inventory tracking system:
- Keep The Spreadsheet Simple: Use simple columns to track your inventory. Include columns for item number, name, location, quantity in stock, cost, value, and reorder limit. Avoid using too many columns, which can make the spreadsheet difficult to navigate and maintain.
For basic and quick tracking with fewer columns, check out this collection of small business inventory templates.
- Update the Spreadsheet Regularly: To keep your inventory tracker accurate, update it immediately when an item is restocked or sold, or when the cost changes. Remember to update the reorder date after an order is placed.
- Audit Data: Ensure your data is accurate by cross-checking physical inventory with data in your spreadsheet, or have a team member double-check your entries.
Use an inventory list template to count initial items, and then transfer the data to your inventory tracker spreadsheet.
- Utilize Conditional Formatting: Use conditional formatting to create visual alerts, such as when to order new items. This helps avoid stockouts.
- Use Data Validation: Create dropdown menus for categories or item statuses to maintain consistency.
- Freeze Panes: Freeze your column header row so when you scroll, it’s always visible. Click your column header row, then click the View tab from the ribbon. Click Freeze Panes > Freeze Top Row.
- Add Sheets: Track stock in and out balances, order statuses, vendor details, and expected delivery dates. Organize supplemental information into separate sheets with PivotTables.
Tip: Format additional spreadsheets into tables using CTL + T. The table format improves data visualization and analysis when working across multiple sheets.
- Keep a Version Log: Save versions or maintain a log sheet to track changes.
- Use a Template: Save time and reduce human error by using a template. Templates are pre-formatted with column headers, automatic calculations, and conditional formatting. Customize them to fit your needs and reuse them for operational consistency.
Check out this collection of Excel inventory templates to streamline your inventory tracking process.
Why Use Excel to Track Inventory?
Excel organizes inventory details into columns and includes features to help manage stock levels and minimize profit loss. Use formulas to calculate item values, while conditional formatting provides visual alerts when it's time to reorder stock.
Pros and Cons of Using Excel as an Inventory Tracking System
Excel is ideal for smaller businesses or for those new to inventory management. Auto-calculations and conditional formatting help decrease human error. However, if inventory grows substantially, tracking can become cumbersome.
Here are some of the benefits of using an inventory tracking spreadsheet in Excel:
- Cost Effectiveness: The online version of Excel is free.
- Shareability: Copy the workbook link, and share it with team members.
- Offline Access: Excel is available offline, which means you can access it at any time.
- Customization: Design your inventory system exactly how you want it with custom columns, formulas, PivotTables, numeric formatting, and conditional formatting.
- Built-In Functionality: Functions like SUM, SORT, and RANK can simplify inventory tracking.
- Automated Calculations: Use formulas to calculate data such as your total inventory value.
- Advanced Functions: Advanced functions like VLOOKUP and IF statements streamline calculations and automate inventory tracking.
- Data Export: Excel makes it easy to export and share your inventory data with other software.
Here are some of the drawbacks of using an inventory tracking spreadsheet in Excel:
- No Real-Time Updates: Excel does not provide real-time tracking, so any changes must be entered manually.
- Steep Learning Curve: If you’re not familiar with Excel, it can take some time to catch on.
- Risk of Human Error: Incorrect formulas and numbers or accidental overwrites can be detrimental to profits.
- Poor Scalability: As your business grows, it will require more data entry which can complicate a spreadsheet.
Wadhwani says, “Excel is not highly scalable. As your operations grow, tracking inventory in Excel becomes time-consuming and error-prone. Version control issues creep in, collaboration gets messy, and manual updates slow you down.” - Limited Image Support: If you want to include images that help identify items, a spreadsheet format is not ideal. A workaround is to include a hyperlink to the image.
“Smartsheet offers Excel-like familiarity with added features,” Wadhwani says, “like automated workflows, reminders, user permissions, dashboards, and integrations with other apps — making it much more scalable for growing teams. So, while Excel is a great starting point, keep an eye on when it’s time to move to something more robust.”
For cloud-based inventory tracking and easy sharing, learn about using Google Sheets for your inventory tracking needs.
Best Way to Organize Inventory in Excel
Tracking your inventory is essential to keep enough items in stock and avoid having capital tied up in stock that isn’t selling. An effective inventory spreadsheet uses basic column headers to list item details and track their movement.
Include these columns for an effective inventory spreadsheet:
- Item Number or Stock Keeping Unit (SKU): This is a unique code that identifies the item and simplifies tracking.
- Item Name: Always include the name of the item.
- Item Description: It’s useful to note details about the item such as color or size. This helps avoid confusion if items are similar.
- Supplier: The person or company who supplies the item.
- Location: This refers to where the item is located in the stock room or warehouse. This is important for efficient item retrieval.
- Stock Quantity: This is the number of items on hand.
- Cost Per Item: This refers to the cost to the business to purchase an item.
- Inventory Value: This is the monetary value of an item in stock. (Calculation: =stock quantity * cost per item.)
- Reorder Limit: The reorder limit refers to the level at which an item should be reordered to avoid stockout.
- Last Reorder Date: This is the date an item was last ordered. Tracking these dates helps assess how frequently items need to be reordered or whether they should be discontinued.
- Notes: Include any additional information such as storage instructions, supplier details, and maintenance requirements.
Optimize Your Inventory Tracking Systems With Smartsheet
Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.
The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.
When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.