【 Make an Inventory in Excel 】 Step by Step Guide ▷ 2022

This software microsoftoffice stands out as an essential tool for most of companies and businesses today, this is because it offers wide variety of business functions that allow you to carry out some of the necessary tasks in these areas easily and quicklyas it is creating warehouse inventories.

And it is that one of the easiest ways to being able to control all incoming and outgoing items in any business is through inventoriesespecially when driving thousands of products. In addition, this tool can also be very useful in the homesat the moment of wanting control some of the products that are used there.

Fortunately, this program has Y that help you have a very good control over all the products you have available and you will not need to make an investment to obtain the dream catalog. According to this, here we are going to explain how you can do item control inventory in microsoft excel. To do this, follow in detail everything that we will explain in the post.

What to keep in mind when doing an inventory using Microsoft Excel?

Thanks to all the tools that this program offers, you will have the opportunity to make a fairly easy stock managementwithout having to resort to expensive programs for it. You can also lean on either one of those of the program or simply do it from zero. Considering that there are different methods for create inventories.

You must bear in mind that the type of inventory that is carried out will also depend on the stock you havethat is, if you have little variety and quantity of products, East item control what to do manual.

But if you have one relatively large number of products So it’s best to use a computer program such as Excel. At the time of create these inventories It is important that you take several aspects into account.

For this you must have previously some details of the products which will be necessary for the same.

Some of them include the following:

• You should with the name of the product.
• make one description of each item that you have stored
• establishes a code for each of them.
• write down the date of receipt thereof.
• write down the date of sale of the equipmentwill allow you to know the departure time from each of them.
• The price.
• And finally the available quantity of each of themit will let you know when it is depleting their existence.

The latter named point It is very important to take into account all the items that are registered and soldsince this will be what allows you know how many products remain in your warehouse. This way you will know if you should buy one. new amount of them or not.

You should also keep in mind that for greater ease when creating these inventories each of your items must contain the following details:

• order everybody items that are the same in the same place.
• Creates a label for each of the products, containers or boxthis will allow you to find them more easily.
• Everytime that you receive merchandise from your suppliers It is important that you verify that everything you received matches what you requested.
• And finally, you must make a periodic review in your warehousethis will allow you to verify that the physical stock matches the stock marked in the inventory.

Steps to take an inventory in Microsoft Excel and keep track of your products

Knowing which are the most important aspects to take into account inventories, Now all that remains is to explain how you can start creating them from Microsoft Excel. As already mentioned above There are two ways to create them. either through a template or manually.

To do this, follow each of the methods that we will teach you below in detail:

with a template

MS Excel stands out mainly for offering a large amount of that you can use for create your stocks of articles. To do this, the first thing you must do is download template what do you want to use in your computer. Keep in mind that this procedure is much easier and faster to perform than create them manually.

Therefore, to create your inventories through a template you must perform the following steps:

• One time downloaded to your computer the following will be look for it in the folder where it has been stored. There you must double click on it to open it. As you can see, a window opens asking you to select the program you want to open it within this case “Microsoft Excel” and click on “To accept”.

• Then the template will appear in a software spreadsheet. As you can see the template has all the and details necessary for the elaboration of the stock.

• In this case, all you have to do is start Replace each of the boxes with the information you want to add there. This process may seem somewhat tedious but in a few minutes your inventory It will be completely created in a very simple and easy way. As you can see, working with the help of templates will save you a lot of work.

manually

the other form of power creating stocks is manuallyPlease note that this is a quite long process so you must pay close attention to each of the steps, as this will prevent you make a mistake during the process.

Also, you don’t need be an expert to create a product inventory, because it is a prsimple but very extensive procedure.

In this way, follow each of the steps that we are going to indicate below:

Create your inventory in a spreadsheet with inputs and outputs

To start with this procedure it is necessary that do you think a document of excel, this must have three sheets available to avoid confusion later. It is also advised that each sheet has its name. The first sheet can be called “Inventory”, In this sheet you will find all the products that you have in warehouse.

As for the second sheet, you can call it “Tickets”, here will be registered the amount of products that shopping. And the third and last sheet will be called “Departures”, it must register all the products that bandages.

Once this is done, in the first line of each column you must write the following:

• Code or item key.
• Name of the product.
• Description.
• Initial stockthe number it starts with, it can be zero.
• Tickets.
• Departures.
• total stock what do you have after purchases and sales.

Depending on your needs you can implement other columns like “Category” or “Brand”.

In the input column you can set the following details:

• Invoice, name or number of the invoice.
• Date, you must include the day, month and year in which the merchandise was purchased.
• Code.
• Product.
• Quantity of items to be bought.

In the output column you can include the following details:

• Invoice, name or number of bill of sale.
• Date, you must include the day, month and year in which the merchandise was purchased.
• Code.
• Product.
• Quantity of products sold.

Once you have all this in your three tables, you must continue with the other steps:

Check that your tables have the correct format

• In order to make sure of this, you need to select your whole table completely and go to the tab “Insert”.
• There you must select the icon “Boards”.
• Now in the drop down menu Choose the option of “Table” again.

• Here a new window will appear “Create table”, In this case you must select the option “Table has headers” and then click on “To accept”.

• After this you will see how the excel table It’s going to take on a totally different look. if you have done everything correctly will appear as follows:

• The next thing will be change the name of the table.

• Now repeat these same steps with the two remaining sheets.
• Note that the table of “Inventory” should be called inventory, the table of “Entry” must be called input and the table of “Departure” should be called output.

Add the products

• When you already have all three worksheets in the correct formatthen you must locate yourself on the sheet of “Inventory”, here you must fill in the column “Code” with all the products you have in stock. Note that here you must put a code to each of themnamely, if you have 50 items then you must create 50 codes.

• The next thing will be to go to the sheet “Tickets”, there you must select all cells from the first column named “Code”, When it is selected you must go to the menu “Data”.
• Now look for the option “Data validation” and select it.

• Here a new window will appear, in the menu “Allow” you must select the option “Ready”.

• After this you will see a new section called “Source”, in it you must do click in the icon to select “Cells”.

• A new window will appear. Here you must go to the sheet “Inventory” and select the code column and press the key “Enter”.

• Now go back to the previous window and in the section of “Source” you will see all cells you have selected, click on “To accept”.

• Once this is done, every time you hover over one of the the cells of that column, you will be able to click on the drop down menu in which you can choose your product code. This will prevent you from making a mistake write product code.

• Then you must fill your table with all the product codesthis will be easier since you only have to choose them from the dropdown menu That has been created.

• The following will be to repeat these steps in the output sheet to get the same results.

Complete all other information in the other columns

• In this case you must return to the window of “Inventory”, when you have all codesyou must finish filling in all the cells of the columns of “Product”, “Description”, the “Initial Stock”, among other. In the event that still do not have any productthen the initial stock must be zero.

• Once this is done, you must go to the worksheet “Entry” in it the code column must also be completed
• in the column of “Product” you must write the following formula: =SEARCH(, INVENTORY, INVENTORY). In this way the column will be filled with all the names of your products automatically. This will prevent you from having to do it Handbook again.

• The next thing will be to fill in the rest columnsin this case it would be missing “Invoice”, “Date” and “Amount”.

• Once all this is done, the following will be repeat the same steps for the window “Departure”.

Use some formulas so that your tables are completed automatically

When all of the above has been completed, the following will be apply necessary functions so that each of the…