Automated inventory template in Excel

Written by
Laura Tennyson

Do you need a way to manage stock levels in your retail business, office, restaurant, or clinic? Chances are that you already use Excel to store and manage a lot of your company data such as accounting and sales. When it comes to inventory management, however, many small businesses still rely on cumbersome and unreliable manual methods. These include paper-based lists or ordering fresh stock only when someone notices that supplies are running low!

An organized and digitized inventory management system will save you money, time, and help you run a more profitable business. Only buying what you need will reduce the risk of stockpiling, over-ordering, and wastage. On top of that, ensuring you have adequate supplies will keep your customers happy and production systems running smoothly.

Inventory management software isn’t cheap, however, and it can be overly complex for a small company. Instead of paying for an expensive stock control program with functions that you don’t need, you can set up an automated stock tracking system — with an inventory template in Excel.

excel-inventory-template-dashboard

Why use Excel for stock control?

  • Flexible: Excel files are easier to adjust and adapt than specialist inventory programs. This allows you to build your own stock management “software” entirely in spreadsheets. When things change, you can just alter the spreadsheet.
  • Accessible: Excel for the web is cloud-based, so anyone on the team can log into their spreadsheets from the Microsoft Office webpage. You and your colleagues can access the connected Excel files from anywhere — while knowing that the data you’re viewing is always up to date.
  • Compatible with everything: Every other piece of software in the world integrates with Excel. This means you can connect data from other company processes and departments with your inventory system.
  • Easy to use: Most office employees have some familiarity with Excel, and if even if they don’t, it’s easy to learn. Plus you can update and maintain the system yourself without any coding or expert IT skills.

About the template

Sheetgo’s inventory template in Excel is a pre-built system for automated inventory management. It’s suitable for companies of all sizes, managing raw materials, or any kind of product.

This inventory tracking template is a plug and play system that contains formulas and charts. It gives managers an accurate tool to track inventory and stock levels while providing warehouse-, store- or office staff with a quick and easy way to check items in and out.

Data from the check-in and check-out spreadsheets is automatically consolidated into your Excel inventory list:

excel-inventory-template-current-inventory-list

What’s a spreadsheet-based workflow?

A standard Excel template is usually a single workbook containing pre-built tables, charts, layouts, and formulas.

In contrast, a Sheetgo workflow template is an interconnected system. It consists of multiple Excel files that exchange data between them via Sheetgo connections. As a result, team members can share data with colleagues and other departments — without working in the same spreadsheet.

The Sheetgo inventory template in Excel gives you:

  • Organization and data privacy: Each file in the template is dedicated to one task. This keeps data more secure and segregated. Individual team members or departments enter information into their own Excel file and that information flows directly into the manager’s master workbook and inventory dashboard.
  • Scalability: Excel for the web allows you to share your spreadsheet with others, but too many collaborators can make a file large and slow. Firstly, keeping data in separate, but connected, workbooks keeps the system running smoothly. In addition, the template can be customized and adapted to build a more advanced system. If you want to add more data inputs, simply connect more workbooks to the template.
  • Integration: An Excel workflow allows you to import data from other company processes or software. In a couple of clicks, you can connect your accounts or sales data to your inventory workflow to generate top-level reports for directors.

What you get with this template:

When you install the Sheetgo inventory template in Excel, three separate workbooks are saved to your cloud storage (OneDrive, SharePoint, or Dropbox).

  1. A check-in spreadsheet. In this file, the individual or team responsible records items entering the company. This might include new deliveries to the warehouse, or returned items, for example.
  2. A check-out spreadsheet. This is where the individual or team responsible registers items leaving the inventory. These may include sold items, materials used in manufacturing, or products that you send to other departments.
  3. An inventory management master sheet for the manager. This workbook contains an automated stock level dashboard and an inventory list. Data flows into this file from the check-in and check-out spreadsheets via Sheetgo connections.
inventory-management-workflow-template-excel-diagram

Working for a manager?

Is someone else in your team responsible for inventory management? Share this article with your manager so they can set up the template.

How to get started

Install the Sheetgo inventory template in Excel here.

  1. Log in to Sheetgo with your Microsoft or Dropbox account.
  2. Click Use template.
  3. Wait a moment while Sheetgo installs the template and creates the connections. Sheetgo saves the files to the cloud storage platform that you used to sign in (OneDrive, SharePoint, or Dropbox).

The inventory template in Excel opens inside Sheetgo. Here you can see what the workflow of connected files looks like.

The menu bar allows you to connect more files, run the workflow, schedule automatic updates, or share the entire system with other managers:

excel-inventory-template-workflow-view

The master sheet (named Inventory management) opens in a tab in your browser.

To open the Check-in or Check-out spreadsheet, just double click on the file in the workflow.

Take a look at each of the files to familiarize yourself with the template.

You’ll notice that each Excel file contains sample data, which you or your colleagues can delete or overwrite.

How to use the inventory template for Excel

Step 1: Share the input spreadsheets

First of all, you need to share the Check-in and Check-out spreadsheets with the colleagues responsible for managing that information. To do this, click Share in the top-right corner of the workbook.

For check-in, this may be staff working in your depot, warehouse, or office.

For check-out, this might be salespeople, administrators, or production staff if you work in manufacturing or hospitality.

excel-inventory-template-check-out-file

Step 2: Enter your inventory data

Now it’s time to start entering your real inventory list in the check-in spreadsheet, overwriting the sample data provided.

To ensure data consistency, apply data validation to column E to create a dropdown menu for the appropriate unit of measure.

excel-inventory-template-enter-check-in-data

Step 3: Update the workflow

The template contains pre-built Sheetgo connections that import data from the Check-in and Check-out spreadsheets directly into the Inventory Management master file.

Every time you update (or “run”) the workflow, the data in the master sheet and dashboard is refreshed with the latest information from the input spreadsheets.

To do this manually, open Sheetgo, select the workflow, and click Run on the menu bar.

Check your Inventory management file. You’ll see that the tabs now contain the latest data from the input sheets.

excel-inventory-template-run-workflow

Step 4: Check items out

Whenever a product is removed from the inventory, staff should check it out using the designated Check-out spreadsheet.

As detailed in the Instructions tab, you can connect this file to the Check-in spreadsheet to automatically import the stock-keeping unit (SKU) codes. Use the VLOOKUP function to autofill columns C and E based on the SKU code.

Step 5: Automate the workflow

Once your inventory workflow is up and running and your colleagues are entering data into their spreadsheets on a regular basis, you can automate the whole system. This ensures you’ve always got the latest stock level data without having to open Sheetgo.

Click Automate on the menu bar and switch the toggle button to update automatically.

Select how often you want the data to be updated: from once an hour to once a month. 

Adjust which days and/or times you need the workflow to update, choose your timezone, and click Save.

excel-inventory-template-automate-schedule-settings

How to expand your Excel inventory template

As your business grows, you can expand the inventory system by connecting more Excel files.

You can also link to other workflows such as sales or finance, to create an interconnected data management system that works like a custom-built ERP.

To add more connections:

    1. Open the Inventory Management workflow in Sheetgo.
    2. Click Connect on the floating menu bar.

Find more tutorials and guidance in the Sheetgo support center.

If you’re looking for a more complex inventory management system in Excel or you’d like us to build a custom system for you, book a call with our automation experts.

Automate the check-in

Interested in automating other stages of your procurement and inventory processes?

Try linking the template to a barcode scanning app or using Microsoft Forms to enter and validate data going into your check-in spreadsheet.

Did you like this article?

If you found this post helpful, share it with your network and fellow Excel fans by clicking on the social media buttons on the left!

You may also like…