Mursalin Ibne Salehin
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.
- Thirdly, select New.
- After that, go to the Search bar and type Inventory Lists.
- Press Enter or click on the Search icon.
- As a result, available templates will appear on the screen.
- Select the template you want to work on.
- After selecting the template, a message will pop up.
- Select Create from the message box.
- Instantly, the selected template will appear on a worksheet.
- Next, delete the columns you don’t need in your inventory database.
- Now, type the information about the products in the database.
- Finally, save the database from the File tab.
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.
- 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.
- After filling with color, the database will look like the picture below.
- Now, start typing the information in the database.
- After completing the database, apply borders to the cells. You can find different types of borders in the Font section of the Home tab.
- 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.
- In the end, the inventory database will look like the screenshot below.
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.
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.
- 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.
