Inventory Management
How to Create an Inventory Management System in Excel
- Written by Rachael Pilcher
As a small ecommerce seller, it’s easy enough to manage inventory tracking by hand. But there will come a time in your business (maybe right now, since you’re reading this!) that you’ll need a more efficient solution.
Whether you love them or loathe them, there’s no denying that spreadsheets are pretty good at tracking and organizing stuff — and that includes your inventory.
Microsoft Excel is one of the most trusted names in spreadsheet software, and it’s a popular choice for ecommerce retailers. You can get started for free, it has a flexible setup, and there are a bunch of useful built-in tools to make stock control easier.
In this article, we’ll give you a walkthrough of how to create an inventory management system in Excel. Plus, we’ll show you how Veeqo’s free inventory management software can help you update and track stock in real-time, and eliminate many of the common challenges associated with spreadsheets.
Table of contents
Pros and cons of Excel for inventory management
How to create an Excel inventory management system
Why should you use an inventory management software like Veeqo instead?
Pros and cons of Excel for inventory management
Excel is a great option for new or smaller ecommerce sellers. As a trusted Microsoft software solution, it’s an easily accessible tool for business owners, and it’s ideal if you don’t have the budget or shipping volume to justify paying for pricey inventory software.
But before you jump into creating your Excel inventory management system, you’ll need to weigh up some of the advantages and disadvantages of using spreadsheets.
Pros of Excel
1. Free to use basic features
Microsoft offers a free online version of Excel that runs directly in your web browser. This is sufficient for basic tasks and gives you 5GB of cloud storage, so if you have a limited budget and not much stock, the web-based version of Excel might be all you need.
2. Flexible
With Excel, you can set up and customize your inventory spreadsheets to fit your specific products and workflows. You’ll be able to add and modify columns, formulas, and workbooks as needed to keep your product and sales data organized as your business grows.
3. Comprehensive tools
Ecommerce sellers love Excel because of the built-in features for sorting, filtering, and searching, which are essential for managing and scaling inventory.
Excel also supports complex formula calculations that can predict sales trends, calculate optimal product reorder times, and analyze performance metrics to help you make better business decisions.
Cons of Excel
1. Cost
Eventually, your inventory management needs will outgrow the features offered in the free version of Excel, and this is when you’ll need to upgrade to a paid plan. Excel is part of the Microsoft 365 suite*, which will cost between $6.99 and $9.99 USD a month for a home plan, depending on how many users you need to add. This is the cheapest option, but it won’t give you the security or collaboration features you need to scale your ecommerce business.
Your best solution is to subscribe to a Microsoft business plan. These plans cost between $6 and $22 per user, per month, and you’ll have the ability to add up to 300 other Excel users if needed.
The business plans also give you advanced features including identity, access, and user management, 1TB of storage for every user, and enhanced cyberthreat protection against viruses and phishing attacks.
Even though this is still cheaper than many of the other inventory management tools out there, the ongoing costs can add up for sellers with smaller monthly budgets.
* Prices current as of April 2024
2. Lack of integrations
As part of the Microsoft Office suite, Excel plays nicely with other business apps like Outlook and Access. Outside of this, you’ll need to use a paid tool like Zapier to help you integrate Excel with all the other tools you want to use.
3. Things can get complicated!
Your Excel spreadsheets might start out as simple, minimalist documents. But as your shipping and inventory needs grow, they can quickly spiral out of control with hundreds of tabs, sheets, columns, and formula inputs turning your neat inventory system into a nightmare.
Even the most dedicated Excel fans will tell you that things can get really complex, really fast when it comes to stock control!
4. No real-time tracking
One of Excel’s biggest downsides is that it can’t track real-time inventory levels. If you’re selling across multiple channels such as Amazon, Shopify, Etsy, and your website, you’ll have to manually update your product levels for every store, every time something sells.
On a busy day of picking, packing and shipping, your inventory might not get updated for hours, or until the end of the day. This can lead to ongoing stock control problems, especially if you’re selling from multiple storefronts.
5. Risk of data errors
It’s easy to make mistakes when you’re inputting Excel data, especially if you’re collaborating with multiple other users who can change the spreadsheets. These constant data updates make it difficult to find errors and fix them before they become a problem.
If you use Excel, you’ll need to have solid systems in place to stay organized, manage your data, track who’s updated what, and run regular audits and backups to ensure all your data is accurate.
How to create an Excel inventory management system
Now we’ve given you the rundown of what to expect when you use Excel, let’s take a walkthrough of how you can create a basic inventory management system for your ecommerce business.
Create your spreadsheet
There are two basic ways to create an inventory spreadsheet in Excel. You can either start from scratch by creating a new, blank workbook, or choose a pre-built Excel template that fits your needs.
Define your columns
Create and name columns that will help you track and update all the necessary product details for your ecommerce inventory. These might include:
Product Name: A clearly identifiable name for each product
Item ID: A unique identifier for each product.
Category: The category a product belongs to
Supplier: Who you buy the product from
Purchase price: The total amount you paid the supplier for the product
Sale price: The retail price for the product
Quantity in stock: The current inventory count for the product
Reorder level: How many products should be left in stock before you need to reorder
Minimum order quantity: The least number of items you can order from a supplier to restock
Lead time: Average time for the supplier to deliver more product
Input your inventory data
Enter all the necessary information into each row and column you’ve created in your inventory workbook.
Excel has a handy “Data validation” feature that allows you to restrict the data entered in each cell to specific formats and values, which will help you reduce errors whenever you input new data.
Set up formulas
Formulas are one of Excel’s superpowers. You’ll need to learn and set up a few basic formulas in different cells and columns to help you do things like:
Calculate the total value of stock
Create reorder alerts
Count incoming stock
Track outgoing stock
Find specific data within the spreadsheet
Create inventory insights such as sales velocity
Add filter and sort functions
Excel’s filter and sort functions help you to quickly sort through your inventory based on the criteria you’ve set.
The filter tool lets you filter a column of inventory data within a table to isolate key data. The sorting function allows you to sort by things like, number, date, alphabetical order, and more, making it easier to get a good overview of what’s going on with your stock and sales.
Secure your spreadsheet
Depending which Excel plan you’re on, you’ll have different options and levels of security available to you. At the very least, you need to protect your inventory data by selecting “File” and then “Protect workbook”.
You should also create a password to prevent anyone else from changing your spreadsheets without your permission.
Share your spreadsheet
Now you’re all set up, you can give access permissions to other people that will be collaborating with you to manage and track your inventory.
Update as often as possible
As we mentioned earlier, Excel doesn’t give you the ability to manage your inventory in real time. This means you’ll need to be super organized about updating your spreadsheet to reflect current sales data, stock levels, prices, and other inventory changes.
Schedule regular backups
Even though Excel is cloud-based software with auto-save features, it’s smart to carry out regular data backups and keep these files in a secure location such as on your desktop, or in a dedicated folder on your computer.
Why choose Veeqo as an alternative to Excel?
If you’re growing fast (or planning to), it can be better to use free, comprehensive shipping and inventory software like Veeqo, instead of managing everything manually in Excel.
Excel can cause more problems than it solves when you have a medium to large inventory. With increased stock turnover, multiple sales channels, and only 24 hours in the day, it’s simply too time-consuming and stressful to handle everything with spreadsheets.
At some point, you’ll need to upgrade to a dedicated inventory management tool, so it’s much easier to start out as you mean to go on.
Veeqo is totally free to use
Veeqo is a free, cloud-based suite of ecommerce tools that helps sellers from startups to enterprises manage their multi-channel inventory and fulfillment.
Real-time inventory management
Unlike Excel, Veeqo tracks and updates inventory in real-time across all your sales channels. For example, if an item sells on Amazon, Veeqo automatically updates the stock levels of that product across all your other stores and marketplaces.
This helps you avoid the many hassles of manually updating spreadsheets, as well as avoiding customer disappointment and extra admin time caused by stockouts and refunds.
Integrations
Veeqo integrates seamlessly with leading ecommerce marketplaces and platforms like Shopify, Amazon, Etsy, Walmart, and eBay — as well apps and tools like CRMs, accounting software, 3PL solutions, POS systems, and more.
If you need extra integrations, the Veeqo API is on hand to give your developers the extra syncing capabilities they need.
Automations
Veeqo automates a lot of the processes that are manual in Excel spreadsheets, such as incoming and outgoing stock, order processing, shipping, and returns.
This reduces the risk of human error involved with manual inventory control methods, and can save you a significant amount of time.
You can see at a glance:
Exactly what’s in stock
The levels of inventory across warehouse locations
What you've sold, and on which store or marketplace
What you could sell more of
What's not selling
With Veeqo you can also create automated inventory rules that can adjust and advertise different stock levels across every sales channel. These can help you to:
Create a sense of urgency by showing stock levels that are lower than your actual inventory count
Set aside units at retail stores for walk-in customers
Create a buffer of stock to prevent oversells
Reporting and forecasting
If you need accurate forecasting, and more detailed analytics than Excel can offer, Veeqo gives you the power to generate advanced reports on demand, and quickly view metrics like inventory turnover, sell-through rate, and order fulfillment times.
Forecast future product demand and understand which products are selling best on which marketplaces, so you can make faster, better decisions about your investing and marketing.
Scalability
Veeqo is designed to scale smoothly alongside your ecommerce business, making order management a breeze.
No matter how fast you grow, or how big your business gets, you’ll have all the enterprise-grade tools you need to manage warehouse stock across multiple locations — without all the messy complications of spreadsheets!
Further reading
Comparing Veeqo vs Linnworks: Which Inventory Management is Best For You
Renting Warehouse Space vs Buying a Warehouse: Everything You Need to Know
If you’ve grown beyond hand-tracking your inventory, but aren’t quite ready to pay for expensive inventory management software, Excel spreadsheets make a great mid-point solution.
But if you’re looking for software that automatically updates and tracks inventory across multiple sales channels, eliminates the time-consuming, manual hassle of updating spreadsheets, and doesn’t cost a cent to use — then Veeqo might be the solution you’re looking for.
Want to skip the spreadsheets and manage your inventory without breaking a sweat? Sign up for your free Veeqo account today and discover how simple shipping and inventory management can be.
