Fully Automated Stock and Inventory Management System in Excel [No VBA] PART 1

This tutorial is everything about creating a fully automated Stock and Inventory Management system that will help you keep track of the stock of items in the inventory with a click of a button!

It’s time to digitalize and automate your business processes and save time and money. Put that extra effort into creating an innovative product!

This guide collectively teaches you to maintain sale and purchase records, create a real-time stock management database integrated with these records that helps in keeping track of the current stock of items in the inventory.

You will also learn to create a form to fill sale or purchase entries that get updated in the records and the database automatically. The guide is split up into multiple parts to help you understand the concepts smoothly.

The best part is you’re going to create all of that without writing codes in VBA!

Creating a stock and inventory management system in Excel

So, let’s get started with this incredible topic!

1. Creating your first billing interface

Let’s get started with creating the billing interface of the stock and inventory management system in Excel. Here, the user will enter the details of an item sold or bought and has two sections to pass the information to Sale Record and Purchase Record.

If the user clicks on the Sales button, the item information is stored in the Sales record in a different sheet and so for the Purchase button. Let begin now.

• First, create three additional sheets in a new workbook.
• Rename the total four sheets to Billing, Purchase, Sale, and Stock.
• Go to the Billing sheet.
• Type Entry type in a cell at the top and Last Bill no. in the cell below it.
• Leave a few cells below the two cells and enter all the required field names for the system.

• Now we will create a dropdown list for the Entry type field for the user to choose if an entry is a Purchase or Sale.
• Click on the blank cell beside the Entry type field.
• Go to the Data tab.
• Click on Data Validation.
• Choose List from the dropdown options
• Enter Purchase, Sale in the source field below.
• Click OK.

• You can apply some formatting to the dropdown list by clicking on it and selecting a font or cell color from the home tab.

The Last Bill No. field will track the receipt number of the previous bill created. We will discuss that later in this tutorial.

Let’s create the two main buttons of the interface now. The Purchase and Sale buttons. Here’s how.

• Go to the Insert tab.
• Pull down on Illustrations.
• Click on Shapes.
• Choose a rectangle shape and draw two button-sized shapes on the sheet.
• Double-click on the shapes to name them. Name them as Purchase and Sales.

• Press and hold CTRL and select both the buttons and apply text and button colors from the home tab.
• We will learn to interact with these buttons after creating the sales and purchase records in different sheets.

It’s time to create the purchase and sales record in a new sheet.

• Go to the sheet named Purchase that we already created in the beginning.
• Create a table in this sheet with the header names identical to the field names in the billing interface.
• Select the headings and a few cells below them and press CTRL+T.
• Check My table has headers and click OK.

• Merge and center the top cells and type Purchase Records in them.
• Repeat the same steps for the sales sheet.

Now, go to the stock sheet to create a stock record.

• Type Item, Purchase Qty, Sale Qty, and Stock as heading of the table.
• Select the headings and a few rows below them and press CTRL+T on your keyboard.
• Format the table as you like by clicking on a cell in the table and going to the Design tab.
• You can choose if you want to remove the bands by unchecking Banded rows or columns.
• Select a style that is suitable to you or add custom colors to the table by clicking New Table Style below.
• Merge and center and few cells above the table and put the title as Stock or Stock Summary.

All three sheets are prepared for us to transfer item information from the Billing interface to their respective records. So, let’s get started with transferring a purchase transaction information in the purchase record.

• Go to the billing interface.
• Enter an item information that is bill number, quantity purchased, date, and price of the item.

Now we will start recording a macro to assign it with the purchase button later.

Follow the steps carefully with attention.

Watch this video on activating the Purchase button or read the steps below.

• Once you have put a random entry in the form, go to the View tab.
• Click Macro and press Start Recording.
• Name the macro as Purchase and click OK. The recording has begun.
• Make sure to store the macro in the same workbook.
• Copy all the item information you entered in the billing form.
• Go to the Purchase sheet.
• Click on the first empty field below the table header under Bill No.
• Press ALT+H+V+T and the values will be pasted transposed. Don’t click anywhere else yet.
• Right-click on the entry, pull on Insert, and press Table rows above.
• The entry will shift downward. Click away in a cell to unselect the selection.

• Go back to the billing interface and delete all the selected entries by pressing Delete on your keyboard.
• Lastly, click on the Entry type field and stop the macro from the status bar or the View tab.

We will now assign the macro to the Purchase button.

• Right-click on the Purchase button.
• Click on Assign macro.
• Select Macros in This Workbook below.
• Click on the macro you created.
• Click OK.

That’s it! The purchase record is ready to use. Delete the sample entry from the purchase record and try to fill a new form using the Purchase button to transfer the data.

Repeat the same steps for the Sales button, and then both buttons will become responsive!

The further steps are everything about automating the Stock record in this workbook. All sales and purchase records will impact the stock of items in the Stock summary sheet.

Fully Automated Stock and Inventory Management System in Excel [No VBA] PART 2

Conclusion

This article was Part 1 guide to creating a fully automated stock and inventory management system in Excel. We learned to create a form called the billing interface to fill sale or purchase entries that get updated in the sales and purchase records appropriately.

Part 2 is about integrating the Stock Summary with the purchase and sale records. The item stock status will update based on the latest purchase or sale transaction made. There is a lot we will learn in this guide as a whole, so stay tuned!