How to Create Inventory Database in Excel (3 Easy Methods)

In this article, we will create an inventory database in Excel. Generally, an inventory database is used to keep track of the products. Any retailer or seller needs to have an inventory database to keep the product records. We can easily create an inventory database in Excel without the help of other software. Today, we will demonstrate 3 easy methods. So, without further ado, let’s start the discussion.


Download Practice Book

Download the practice book here.


What Is an Inventory Database?

An inventory database is a list that keeps a record of products and tracks the turnover. It is a crucial part of any business. Keeping an inventory database will save a lot of time and make the whole process easier. One can ensure accurate forecasting using the inventory database. Nowadays, there is some software to perform this task. But, you can also create it in Excel by following a few steps.


3 Methods to Create Inventory Database in Excel

1. Use Excel Templates to Create Inventory Database

You can create an inventory database using templates in Excel. The process is very straightforward. In this method, we will create an inventory database for some products. In the database, we will keep the records of the product id, description, unit price, and other necessary information. Let’s pay attention to the steps below to know more.

STEPS:

  • First of all, launch the Microsoft Excel application.
  • Secondly, go to the File tab.

Use Excel Templates to Create Inventory Database

  • Thirdly, select New.

Use Excel Templates to Create Inventory Database

  • After that, go to the Search bar and type Inventory Lists.
  • Press Enter or click on the Search icon.

Use Excel Templates to Create Inventory Database

  • As a result, available templates will appear on the screen.
  • Select the template you want to work on.

Use Excel Templates to Create Inventory Database

  • After selecting the template, a message will pop up.
  • Select Create from the message box.

Use Excel Templates to Create Inventory Database

  • Instantly, the selected template will appear on a worksheet.

Use Excel Templates to Create Inventory Database

  • Next, delete the columns you don’t need in your inventory database.

Use Excel Templates to Create Inventory Database

  • Now, type the information about the products in the database.

Use Excel Templates to Create Inventory Database

  • Finally, save the database from the File tab.

Use Excel Templates to Create Inventory Database

Read More: How to Create a Simple Database in Excel VBA


2. Manually Design Inventory Database in Excel

We can also design an inventory database manually in Excel. In that case, we need to build the inventory database from Scratch. It is another easy process but consumes time. Here, we will again create the database for some products. Let’s follow the steps below to learn the method.

STEPS:

  • In the first place, you need to create the headers.
  • In this case, we have typed the headers in Row 4. We have also used bold fonts and borders.

Manually Design Inventory Database in Excel

  • After that, fill the headers with colors to highlight.
  • To do so, select the headers and fill them with colors from the Fill Color option of the Home tab.

Manually Design Inventory Database in Excel

  • After filling with color, the database will look like the picture below.

Manually Design Inventory Database in Excel

  • Now, start typing the information in the database.

Manually Design Inventory Database in Excel

  • After completing the database, apply borders to the cells. You can find different types of borders in the Font section of the Home tab.

Manually Design Inventory Database in Excel

  • To apply the filter, go to the Home tab and select ‘Sort & Filter’. A drop-down menu will occur.
  • Select Filter from the drop-down menu.

Manually Design Inventory Database in Excel

  • In the end, the inventory database will look like the screenshot below.

Manually Design Inventory Database in Excel

Read More: How to Create a Database That Updates Automatically in Excel


3. Create Excel IN and OUT Inventory Database

In the last method, we will create a simple in and out inventory database. Here, we will create the inventory list from a product list. The product list is given below. In the product list, there is information about the id, name, description, and unit price of some products.

Create Excel IN and OUT Inventory Database

Let’s follow the steps below to know the method.

STEPS:

  • Firstly, select the range B5:E12 and name it ‘List’. It is part of the product list dataset.

Create Excel IN and OUT Inventory Database

  • Next, we need to create the ‘IN and OUT’ inventory database.
  • For that, create a structure like an image below.
  • Now, select Cell J5 and type the formula:
=VLOOKUP(I5,List,2,FALSE)
  • Hit Enter to see the result. It will show an error at first. Don’t need to worry about it.

Here, we have used the VLOOKUP function to search for an item in the array named List. We will look for the id in Cell I5 in the List array. In this case, the column index should be 2. We need an exact match, so we have used FALSE in the last argument.

  • Again, select Cell K5 and type the formula:
=VLOOKUP(I5,List,3,FALSE)

Here, column index 3 represents the third column of the array named List.

  • One more time, select Cell L5 and type the formula:
=VLOOKUP(I5,List,4,FALSE)

In this case, column index 4 represents the fourth column of the array named List.

  • After that, select Cell J5, K5 & L5 and drag the Fill Handle down to copy the formula.

  • After copying the formula, you will see results like the picture below.

  • At this moment, enter the IDs in the database and the changes will update automatically.

  • In the end, your dataset will look like this. You can also follow the same process to build another database to keep records of the sales.

Read More: How to create a database in Excel (make in 8 easy steps)


Things to Remember

There are a few things you need to remember when you try to create an inventory database in Excel.

  • Save the template before exiting the application to reuse it.
  • Use the correct column index in the VLOOKUP formula.
  • To save time, use Method-1.

Conclusion

In this article, we have demonstrated 3 easy methods to Create an Inventory Database in Excel. Here, we have used practical datasets to explain the process. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo