How to Manage Your Inventory in Excel

Real talk? It’s hard to track your inventory completely by hand. Fortunately, there’s a better way—and it doesn’t involve spending hundreds of dollars per month on inventory management software.

Microsoft Excel is an Excel-lent tool for managing all types of data. With Excel, you can create everything from simple to complex spreadsheets that incorporate automatic calculations, conditional formatting, and even pivot tables that present your data in a new, visual way.

The catch is Excel isn’t the easiest program to use if you’re not familiar with all its features and formulas. So we’ve broken down the basics for this guide to help you create a simple Excel document to track your business’s inventory.

There are a few things you should know about Microsoft Excel before you invest too much time into creating an inventory tracking spreadsheet.

1. Excel is not primarily a cloud-based software. However, Microsoft 365 for business does allow cloud storage and collaboration. Most people use Excel on a singular computer and purchase a license once since Microsoft 365 for Business costs $12.50/month per user. This cost can add up fast!
2. Excel’s main selling point is its built-in formulas, which perform calculations for you based on the data you input. This makes Excel a lot more efficient and accurate than manual inventory management systems. But it’s still not quite as accurate as real-time tracking solutions since Excel sheets rely a lot more on human input.
3. Excel is not free, even if you have a PC. The software costs $139.99 for a single user, or you can opt to get the complete Microsoft 365 Personal suite for $69.99 per year (which you can break up into monthly payments of $6.99). This makes Excel considerably cheaper than most inventory management software, and it includes 1 TB of cloud storage for all your business files. If your business has multiple people dealing with inventory, sales, or other spreadsheet needs, then you will need to look at a Business plan that includes the software for multiple users to collaborate.
4. Excel is a vastly complicated program, and even experienced Excel users get tripped up from time to time. So if you’re unfamiliar with Excel, you may want to use an inventory product tracking template.

Every business’s needs are different, but we found that you can’t go wrong creating these three main tabs on your Excel inventory tracker. We recommend entering all your orders, sales, and inventory counts on the following tabs.

The Products tab on your Excel doc should be your complete inventory list. This sheet will help you keep track of all your current and discontinued items, as well as the vital information you need to assess product performance, determine which items to reorder, and store product details.

To create this sheet, we recommend creating a table with the following data points as columns:

• Item number—List the serial numbers, SKU numbers, or product numbers you’re using to track each item on your list.
• Category—Include the sales category for each item so you can track performance and revenue on a category-by-category basis.
• Product name—Share the name of each product so you know which items correspond to the item numbers you’re using.
• Description—Include short descriptions of your products (just in case the product name doesn’t make it clear which item you’re dealing with).
• List price—Note the price you’re selling each item for.
• Stock location—List the warehouses, shelves, or bin numbers where your items are being stored. This makes it easier to track them down when you need them.
• Quantity in stock—Note the numbers of items currently on your shelf.
• Inventory value—Share the total values each of your inventory items represent (based on the cost per item and the number of items in your current stock)
• Reorder level—List the reorder points for each of your products.
• Cost per item—Include how much you pay per item.
• Vendor—Share the names of the vendors who supply each product.
• Date of last reorder—Note the most recent dates when you reordered your products.
• Days per reorder—List the average times between your reorder dates and the dates you receive each product.
• Stock waiting to be received—Include the numbers of items you’ve already ordered but haven’t yet received.
• Discontinued?—Note whether each item is discontinued or not.

Once you’ve got a basic table going, you can kick your Product tab up a notch with extra goodies. For starters, you may opt to have Excel calculate your inventory values by using a formula (which would automatically multiply the values in your “Quantity in Stock” and “Cost Per Item” columns). That way, you can know for sure that your inventory values are accurate as long as you input your data correctly.

For advanced customization, you could apply conditional formatting to this tab so any products with stock levels below your reorder point are highlighted in red. This would make it easier for you to quickly identify which products require your attention. Or you could use a complex formula to auto-calculate the amount of stock on its way based on the number of items entered in your Orders tab (see below).

If all that sounds overwhelming, just remember you can always use a template that’s preformatted and includes formulas for easy calculations.

If you’re big on the conditional formatting, you could set up a rule that automatically highlights any items in your worksheet that haven’t been delivered on time. Or you could create a dropdown list for your product categories so you don’t have to manually type them in every time. There’s no single right answer for how to set up your inventory management and control, so feel free to personalize your Excel sheet (or template) to fit your needs.

Last but not least, we think no inventory management solution is complete without a way to track your sales.

Once again, we recommend setting up a table with the following columns:

• Sales order number—Write in the numbers you’re using to identify and track each customer order or purchase. If you’re selling inventory on multiple channels (such as in person and via your e-commerce site, for example), you may opt to list order numbers for only online orders.
• Item number—As with your Product and Order tabs, list the SKUs, serial numbers, or product numbers you’re using to track your items. And again, be sure to list each product on a separate line (even if it’s part of a larger order with other items).
• Product name—Share the names of your products.
• Quantity—Note the numbers of items each client ordered.
• Sale date—Input the date when the sale was made.
• Ship deadline—Show the dates when each item must be shipped to get to your customers on time.
• Ship date—List the dates when each item was actually shipped.
• Tracking number—If you have it, share the tracking numbers for each shipped item (so you can track it en route).
• Client—List the names or customer loyalty numbers associated with the clients on each order.
• Retail price—Note how much your customers paid for each item they ordered.
• Amount—Show the total dollar amounts you received from each customer for the items they ordered.

We know we sound like a broken record, but from here you can customize your inventory and sales tracking even further. In our template, we added conditional formatting to highlight outstanding online orders that didn’t get shipped in time. Or you could create a formula to automatically calculate the “Amount” value on each order (by multiplying your “Retail Price” and “Quantity” values).

Excel offers tons of functionality, so with a little work, you could definitely bring your Excel spreadsheet up to the same level as a basic inventory software.

One way to get your spreadsheet to that point would be to create another tab for reporting. Since your inventory list, your Order tab, and your Sales tab are all formatted as tables, you can pull data from those tabs to create custom reports that can be adjusted according to your needs. These can be really helpful for quarterly reports and month-over-month tracking.

You could also add a tab just for doing reorder point calculations. That way, you don’t have to guess at the ideal inventory levels for reordering each of your products—you can use a formula to do precise calculations for you.

Another option? If you’re not interested in doing manual counts of your inventory every day, you could create another tab just for conducting physical inventory. This tab would list all your products by item number, as well as a place to record physical counts of each item. Once you have this data in place, you could use an advanced formula to rig your Product tab to auto-calculate your current stock for each item based on the number of sales and purchases you’ve completed since your last physical inventory count.

And as we’ve mentioned already, there are plenty of free templates available online that would deliver these features with minimal work on your part. So if you’re feeling intimidated by the sheer amount of Excel knowledge needed to execute any of the ideas above, a template could be a big help.

Excel is a good tool, but it certainly isn’t infallible. Here are our top tips and tricks to make Excel work for your business:

• Keep things simple. While Excel is certainly capable of tracking every minute detail, you don’t want to spend all day every day doing data entry. The more things you have to track, the more work it is for you and the harder it is to keep accurate tabs on the state of your inventory. Plus, Excel spreadsheets with hundreds of columns are hard to navigate, so chances are small you’ll ever be able to find what you need.
• Update, update, update. Excel can’t track incoming shipments and outgoing sales automatically, so it’s on you to keep your spreadsheet up to date. Try to update your Excel sheet immediately after receiving new shipments or processing a customer order. It’ll help you stay confident that your inventory tracker is accurate, and it’ll keep you from forgetting that you have 50 extra items sitting on the shelf in your back room.
• Put it in the cloud. Storing your Excel sheet in the cloud allows access to the document from multiple devices. That way, everyone who needs to can view and update your inventory tracker. Just make sure you have the co-authoring feature turned on, or you’ll wind up with two different versions of your spreadsheet anytime multiple people try to make changes at once.
• Audit yourself. A single data entry error on your spreadsheet could have serious effects that ripple through your business for months afterward. So be extra careful to review your inventory management document often to screen for errors. You should also go over your spreadsheet regularly and try to evaluate whether your methods for tracking products, orders, and sales are realistically working for you.
• Consider adding hardware. For a really robust inventory management tracker in Excel, try integrating barcode scanners. Many of the top barcode scanners on the market can upload data directly to Excel, so they may be able to save you some time and improve accuracy on your data entry.
• Work with your data. If you’re new to Excel, you can take pictures of analog spreadsheets and easily convert the picture to a fully editable Excel spreadsheet. Once the data is in, use formatting, sparklines, and tables to get a better read on the data and see it in a new light.
• Use an inventory control template. You can find literally hundreds of Excel inventory management templates—both online and in your Excel template list. Some (like list templates) focus on just one aspect of your inventory tracking, while others are more comprehensive. Still other templates are designed specifically for restaurants, research and development businesses, and other niche industries. Using a template is a great way to get your inventory tracking system set up with minimal effort, so take advantage of the options at your disposal.
• No seriously. Use a template. If you’re reasonably sure you’ll upgrade to inventory management software at some point in the future, you may want to scope out your favorite platforms. Some inventory management software providers actually offer Excel templates that come preformatted for uploading to your inventory software of choice whenever you’re ready to upgrade. Depending on the provider (and the quality of the template they provide), that could make your life a whole lot simpler down the road when you’re ready to move up to a paid inventory management software.

Microsoft Excel offers a lot of possibilities for business owners who want to track their inventory on a budget. The learning curve for Excel is steep, but your inventory manager can be as easy or complex as you feel comfortable with. And if it comes down to it, there are tons of Excel templates for inventory management that include formulas, conditional formatting, pivot tables, and other tools for effective tracking.

Not sure if Excel is really the right choice for managing your inventory? Inventory management software may not be a bad way to go. Check out all the ways your small business stands to benefit from using software to track inventory to see if it sounds appealing to you.

Disclaimer

At Business.org, our research is meant to offer general product and service recommendations. We don't guarantee that our suggestions will work best for each individual or business, so consider your unique needs when choosing products and services.