How to Manage Your Inventory in Excel
By signing up I agree to the Terms of Use and Privacy Policy.
By signing up I agree to the Terms of Use and Privacy Policy.
Every businesss needs are different, but we found that you cant 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:
Once youve 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 thats preformatted and includes formulas for easy calculations.
Next, we recommend creating a tab to track all your purchase orders. This helps you monitor your supply chain and keep up with any outstanding orders.
Like in the Product tab, we recommend you create a table. List each product from each order on a separate line with the following columns as headers:
Again, you can choose to make this tab as simple or complex as you like. We think the data points listed above should be sufficient for most businesses, but youre free to add more columns if you want your inventory tracker to monitor other factors (like bills of materials). Some of these data points will also be the same as in the Products tab above. This helps keep your business organized and ensures the right information is present for your tracking.
If youre big on the conditional formatting, you could set up a rule that automatically highlights any items in your worksheet that havent been delivered on time. Or you could create a dropdown list for your product categories so you dont have to manually type them in every time. Theres 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.
Ordoro offers everything you need to sell your products online or in person.
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:
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 didnt 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 dont have to guess at the ideal inventory levels for reordering each of your productsyou can use a formula to do precise calculations for you.
Another option? If youre 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 youve completed since your last physical inventory count.
And as weve mentioned already, there are plenty of free templates available online that would deliver these features with minimal work on your part. So if youre 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 isnt infallible. Here are our top tips and tricks to make Excel work for your business:
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.