Table of Contents
Utilizing Microsoft Excel for inventory tracking is an easy way to cut costs, save time, and organize inventory. While not a perfect fit for larger businesses — or anyone with a few hundred different items in their inventory — Excel is excellent for startups. You can track inventory, sales, orders, and more than a dozen other functions. If you know how to utilize it correctly, you can even generate a variety of valuable inventory formulas and reports.
The simplest way to use Excel as a stock management system is to organize your data based on sales quantity. This allows you to create a flexible inventory tracker that updates you when you need to order items. It also keeps your most sold items on the top for easier tracking, so you won’t be scrolling through pages of data to find what you need.
There are several ways to do this, but the easiest method is using the Sort function pre-built into Excel. You can also use the Rank function, which is slightly more sophisticated.
The Sort function only requires that all of your cells are the same size, that you select all of them, and that you can find the Sort function on the top of the page. Don’t forget, you’ll have to re-sort each time you update your numbers and use “Descending” order if you want to rank sales from highest to lowest.
You can use the same SUM function with Sort to generate income reports by adding a value for each item. Then, when you update your sold items box, Excel automatically does the math for you to track how much inventory you’ve sold. You can also expand on this by adding sections for profit margins, taxes, and smaller details like packing and shipping costs.
The Rank function allows you to take this process a step further by adding a specific rank (1, 2, 3, 4, and so on) to your items. It’s also more dynamic because it updates automatically as you adjust numbers. This means that you won’t have to manually rank your items each time you add a new sale, so you won’t forget to update your sorting and accidentally reorder the wrong items.
This formula is slightly more complicated but is still fairly straightforward:
= Rank(Cell, Cell:Cell)
The first number is the cell you want to use, and the second set of numbers are the cells you want to compare your first cells with.
This gives you a numerical ranking for most sold items that automatically updates without you having to sort. This makes it easier to track inventory effectively and see which items have sold most.
The main problem with the above function is that you have to manually enter all of your incoming and outgoing products. When you have a large number of products, several people entering values, or a busy day, errors happen.
For that reason, a barcode scanner is a useful tool for tracking inventory with Excel. Most USB and wireless barcode scanners are easy to program to integrate directly into Excel, and you can do so on separate lines or updating a single line. Programming for barcode scanners varies slightly by brand.
The standard function for a scanner is to press “Enter” or “Tab” after adding information to a cell. This means that it will add each newly scanned item to the next row or the next column. You can program it differently, usually by finding a scan code for changing the function, but we’ll go over a relatively easy way to program using the original multiple line code.
Each time you scan a code, it automatically inputs the item’s SKU into your Excel document. Keep in mind that unless you assign additional values to the barcode scanner, it won’t put in other items such as value, product description, etc. You can most likely program this fairly easily using your barcode scanner codes, but this will differ depending on your software.
You can use the SUMIF function to calculate running totals for the same item on different lines:
Translated into plainer language: =SUMIF (Cell Range (usually all the way down the page), SKU To Look For (in this case, ES25), Sales To Add Up). For products remaining, you use the same SUM function used to calculate sales in the previous model.
The best practice is to set the document up with your original SKUs on the top, with your current totals, and use the most right bars for sold and remaining items. You could put them at the bottom, but you’ll end up having to scroll anyway.
You can also use a macro function to grab your data and move it to a separate page. The barcode scanner will enter items on the bottom of the sheet, but you will immediately see running totals on the top.
If you use two different barcode scanners, you can use the same computer for both incoming and outgoing inventory; you just have to program the scanner to add to the “current stock column,” rather than the “sales column.” This allows you to quickly and easily update your running totals, whether you’re getting shipments or making sales.
One of the main limitations of Excel is that there isn't really a feasible way for multiple people to add to the same document at once. While you can solve this with multiple Excel documents, today’s technology presents another solution: the cloud.
Microsoft has its own cloud solution with Office 365, but you can also use in Google Drive or some other free online solutions. Office 365 allows for multiple users, real-time updates, live backup, and several users editing the same document at once.
A cloud inventory management system is especially useful if the inventory sheet is updated by multiple people or from multiple devices. The most valuable function is automatic synchronization. Plus, if you download a copy of Microsoft's OneDrive to your computer, you can keep a backup copy directly on your hard drive to ensure access in case of an Internet outage.
Inventory tracker reports and analysis charts are relatively easy to generate, providing you are diligent about inventory tracking. You can choose to create reports on a daily, weekly, bi-weekly, or monthly basis, as well as create quarterly and yearly reports.
Set up your basic parameters for inventory and then dragging and dropping the sheet to a new Excel workbook. This automatically copies all of your work to the new one, which gives you a clean inventory page for each day, week, month, or however long you choose to keep records.
You can also drag and drop all of your monthly records into one file to set up a yearly inventory, with individual sheets per month for easy organization.
For smaller businesses, or those with only a few large ticket items to keep track of, single inventory pages are the most convenient, because they allow you to allocate one page to each product.
For larger businesses, or those with more products, you can create a running inventory for sales, profits, and products.
The simplest example is deducting sales from your original stock figure: =SUM(D8-E8), replacing the D8 and E8 with specific cell locations that you want to subtract from each other. Create an original stock line and use that to deduct your sales from. This gives you an instant total that updates whenever you change the number in either cell. You can update the cell using either a manually entered number or using a scanner.
Don’t forget you can use Autofill to populate cells using the same formula. Select the cells that contain the data that you want to fill into adjacent cells, then drag the fill handle across the cells. This will stop you from having to repeat the formula in multiple boxes for multiple products.
If you want to keep better track of a few large ticket items, you might want to use individual sheets per item, which uses the same functions, except you set up one sheet per item and set your scanner to input different SKUs on specific sheets.
Excel can be an extremely versatile inventory tracker for startups, especially if you know how to use it to your advantage. While some of the formulas may seem a bit complex if you aren't familiar with using them, it’s relatively easy to learn and allows you a lot more flexibility when managing your inventory in Excel.
Plus, they enable you to reduce the manual labor and mind-numbing data entry by making Excel and machines like barcode scanners do most of the tedious work for you.
Although a great starting point, it isn’t a one-size-fits-all solution, especially for businesses looking to grow and scale.
As the demands and complexity of your business grow, you will need to utilize a more sophisticated inventory and order management system to ensure you are well-equipped to handle an increase in stock and sales.
© 2020 Intuit Inc. All rights reserved.
Intuit, QuickBooks, QB, TurboTax, Proconnect and Mint are registered trademarks of Intuit Inc. Terms and conditions, features, support, pricing, and service options subject to change without notice.
By accessing and using this page you agree to the Terms and Conditions. | Privacy Statement