How to Better Manage Your Inventory in Excel or Spreadsheets (Download a Free Inventory Template)

When running, or working in, a small business with an inventory, it is essential that you have control over your product movements and stock if you want to succeed.

You need to be able to answer the following questions, should somebody wake you up in the middle of the night, if your business is going to take off:

What is in stock, how many items have been ordered, which items are associated with which orders and where are the items located?

If you lose track of your inventory movements, you make orders based on guesswork, resulting in delayed orders and increased inventory costs.
This leads to you losing money, time and customers.

Fortunately, there is a cost-effective way to start organizing your inventory and order flow: By using spreadsheets like Excel.

Even though spreadsheets won’t automate your inventory data as a dedicated inventory management system would, they provide a good starting point since you’re likely familiar with them. Spreadsheets then serve as a bridge until you’re ready to transition to an inventory system.

They allow you to keep track of the inventory value and the order process, which will help improve customer satisfaction and business growth.

But how do you get started?

That’s exactly what you’ll get in this article – along with an inventory template you can start using right away!

3 Simple Steps for Managing Your Inventory on the Spreadsheet

1. Create a New Spreadsheet with Three Tabs called “Inventory List,” Inventory Picking Lists” and “Bin Lookup”

Start by opening, for example, Excel, and go to “Menu.” Choose “New.” Click on the blank page to start fresh – alternatively, you can use the shortcut by pressing CTRL + N on PC or Command + Shift + N on a Mac.

Now, add three tabs with the titles “Inventory List” “Inventory Picking Lists,” and “Bin Lookup.”

2. Fill in the Three Tabs

Of course, you can best decide what information you want to include for your inventory, picking lists and bin lookup yourself. However, it’s a good idea to divide the three tabs into several columns to include the most important details in your sheets:

For the inventory tab, we recommend creating the following columns: SKU numbers, item names, bin number, locations, unit of storage – for example, pallet, box, piece, quantity of items in stock, quantity of items reordered, cost price, selling price and inventory value. This way, you always have control over the available items, costs, profit, storage situation and more.

For the picking list tab, start by creating a column for order numbers so you can link specific items to specific orders and keep track of them. Then, create columns for product names, SKU numbers, quantity to be picked for each order, stock availability for each item, unit, bin number and location.

For the bin lookup tab, create the following columns: One for the bin number – for example, T305, a description of the storage space – whether it’s large, medium or small, location and the measurements of the storage space: width, height and length.

3. Remember to Update Your Inventory Changes Regularly

Before you get your template, here’s a crucial piece of advice. Since spreadsheets are static programs, you must remember to update them yourself. Ideally, every time there is a movement of goods to and from your inventory.

You would want to avoid finding yourself in a situation where you order too few or too many items because you forgot to update your sheets. Therefore, incorporating the update of your three tabs into your daily routine is important.

But now…

Free Excel Template for Your Inventory

We have filled out the template for you, but you can always go in and make changes to fit your specific inventory needs.

Enjoy:

Since you’re already on the verge of improving your inventory management, here are 4 tips to optimize your warehouse organization. That way, everything is ready to roll as you start entering data into your new sheet.