January 15, 2018

# Inventory Management Using Tables in Excel

How to create an inventory management system using tables in Excel. One great way to organize our raw data is to use tables, earlier known as lists in Excel, especially if the columns or rows need to expand on a regular basis. You may not know exactly how many headers or columns you will need when you start with a project. Once a table is created you can expand the columns automatically, i.e. as soon as you add a column to your table it becomes a part of the table. Therefore you can say that the table in Excel is a dynamic object. Also when you apply a function in a row cell, it can be automatically used in the cell below it. Of course, this functionality is also available in the normal worksheet cells but you need to do an autofill. We have used the special properties of the table to create an inventory management system that includes an items list, items received, items issued and finally the stock in hand is calculated. Watch the video below:

The following formulas and functions have been used for the creation of the inventory management:

=VLOOKUP(B3,Table1,2,FALSE)

=VLOOKUP(B3,Table1,3,FALSE)

Issued

=VLOOKUP(C3,Table1,2,FALSE)

=VLOOKUP(C3,Table1,3,FALSE)

Instock
=VLOOKUP(B3,Table1,2,FALSE)

=VLOOKUP(B3,Table1,3,FALSE)

=SUMIF(Table2[Sl.No],[@[Sl.No.]],Table2[Qty])

=SUMIF(Table3[Sl. No],[@[Sl.No.]],Table3[Qty])

We have used the function below to flag the reorder of items:
=IF([Stock on site]<0,1,””)

Here’s also a screenshot for the conditional formatting to create the flag:

Overview of Excel tables

## 8 thoughts on “Inventory Management Using Tables in Excel”

1. Thank you Dinesh, for your great tutorial! I’m trying to set up a way to link this kind of inventory management system, with recipes for cosmetic products. The goal is to know how much product I need to order, to be able to make various products. Do you have a way to do this that you would recommend?

Thank you so much!

2. Thomas Turner says:

Dinesh. I would also like to thank you very much for this wonderful tutorial. I created a version for use in narcotic inventory management in Excel 2007 Spanish edition. The only thing is that there is a different procedure for data validation. Thanks again!!!

3. Toby Muir says:

I have found this to be so helpful. Much appreciated.

4. Mustafa says:

Thanks Dineshji,
I would like to add here that InStock Sheet should be auto populated with Data if any new entries in IN or OUT are entered.

5. bit sincam says:

hello sirs,
i saw this post and i consider you may help me to find out the solution,here is the case.

in excel, if cell a5=true(sheet2), then x1(sheet1)-h1(sheet2), if cell b5=true(sheet2),then (the new value of x1(sheet1) from the previous IF minus to i1(sheet2)) and so on..

hope you can help me on this.

6. Rahim says:

Dear Mr Dinesh

I have done exactly as you explained, all worked well except reaching InStock on Received Qty and Issued Qty it comes up with #SPILL!

Can you please explain me how to remove it.
Thanks
Rahim

7. Patrick Ledesma says:

Dinesh,

Thank you for your detailed explanation. I want to tweak it just a little and need your help.

In my scenario, I have populated far more information in the “item list” tab to incorporate actual stock in our warehouse.

So, when I develop the “in stock” tab, for the “stock on site” field, I want to basically make it look like this:

=SUM([@[Received Quantity]]-[@[Issued Quantity]]+’Inventory!N2), where N2 is a field with the quantity on hand without quantity changes reflected in the orders/issued fields.

This seems to work, but I want to make the “Inventory!N2” dependent not just on the “items” field, but on three fields (items, brand, model). This worked for the “Received Quantity” and “Issued Quantity” fields by doing this: