If you are looking for some special tricks to make a stock balance sheet in Excel, youâ€™ve come to the right place. There is one way to make a stock balance sheet in Excel. This article will discuss every step of this method to make a stock balance sheet in Excel. Letâ€™s follow the complete guide to learn all of this.

**Table of Contents**hide

## Overview of Stock Balance Sheet

In a stock balance sheet, inventory is ordered, stored, tracked, and controlled. Each of the items used to produce a businessâ€™ products or services is listed on the stock balance sheet. This includes all the items in an organizationâ€™s inventory.

Here, Stock In means the condition when new quantities of each product will be added to the stock after opening the stock. The Stock Out means the condition when quantities of each product will be sold from the stock. The Balance Stock means the condition of how many quantities of each product remain after selling out.

Here, to calculate the Total Price, we use the following formula:

**Total Price = Unit Price* Quantity**

We also use the following formula to calculate quantity in the Balance Stock condition:

**Quantity in Balance Stock = (Quantity in Opening Stock+ Quantity in Stock In) â€“ Quantity in Stock Out**

## Advantages of Keeping Stock Balance Sheet

The advantages of keeping a stock balance sheet are as follows:

- You can track the progress of a company pretty easily and faster with the help of this sheet and make many big decisions.
- You can get several financial results and progress from this sheet.
- Improves inventory order accuracy.
- Reduces costs and saves time.

## How to Make Stock Balance Sheet in Excel: Step-by-Step Procedure

In the following section, we will use one effective and tricky method to make a stock balance sheet in Excel. To create a more understandable stock balance sheet, it is necessary to make a basic outline and calculations with formulas and calculate the final stock balance. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

### Step 1: Create Primary Outline

To create a stock balance sheet, we have to follow some specified rules. At first, we want to make a dataset. To do this we have to follow the following rules.

- Firstly, write â€˜Stock Balance Sheetâ€™ in some merged cells at a larger font size, That will make the heading more attractive. Then, type your required Headline fields for your data. Click here to see a screenshot that illustrates what the fields look like.

### Step 2: Input Opening Stock Data

Now, after completing the heading part, you have to create an opening stock dataset. To do this, you have to follow the following procedures.

- First of all, in the following image, we can see the basic outlines of the stock balance data and its related dataset.
- Here, we have Serial No, Item, Quantity, and Unit Price columns in the following dataset.
- Next, in the Item column, we enter each product name.
- Then, in the Unit Price column, we type each productâ€™s unit price which is an essential part of calculating the Total Price for the Balance Stock.

**Read More: **How to Make Projected Balance Sheet in Excel

### Step 3: Input Data of Stock In

Now, we want to create a Stock In dataset. To do this, letâ€™s walk through the following steps.

- First of all, you have to enter the Serial No and Date column as shown below.

- Now, we want to convert the products into the drop-down list in the
**Item**column. To do this, go to the**Data**tab and select**Data Validation.**

- Next, when the
**Data Validation**window appears, choose**List**from the**Allow**box and select your required range of data in the**SourceÂ**box. - Then, click on
**OK**.

- As a consequence, you will get the following drop-down list in the ItemÂ column.

- Now, we will enter each product name from the drop-down list in the Item column as shown below.

- The next step is to enter how many new quantities of each product will be added to the stock.
- As a consequence, we will get the Quantity column under the Stock In.

- Next, to calculate the Total Price of each product, we have to use the following formula in cellÂ
**J6.**

`=E6*I6`

This formula will return us the total price for each product.

- Then, pressÂ
**Enter.**

- Next, drag down the
**Fill handle**icon. - As a consequence, you will get the Total Price under the Stock In as shown below.

**Read More: **How to Tally a Balance Sheet in Excel

### Step 4: Input Data of Stock Out

Now, we want to create a Stock Out Dataset. To do this, letâ€™s walk through the following steps.

- First of all, you have to enter the Date column as shown below.

- Now, we want to convert the products into the drop-down list in the
**ItemÂ**column. To do this, go to the**Data**tab and select**Data Validation.**

- Next, when the
**Data Validation**window appears, choose**List**from the**Allow**box and select your required range of data in the**Source**box. - Then, click on
**OK**.

- As a consequence, you will get the following drop-down list in the ItemÂ column.

- Now, we will enter each product name from the drop-down list in the Item column as shown below.

- The next step is to enter how many quantities of each product will be sold from the stock.
- As a consequence, we will get the Quantity column under the Stock Out.

- Next, to calculate the Total Price of each product, we have to use the following formula in cellÂ
**R6.**

`=M6*E6`

- Then, press
**Enter.**

- Next, drag down the
**Fill handle**icon. - As a consequence, you will get the Total Price under the Stock Out as shown below.

### Step 5: Calculate Balance Stock

Now, we want to create a Balance Stock data.Â To do this, you have to follow the following steps. Here we will use **the SUMIF function** to find out the quantity under the Balance Stock condition.

- First of all, you have to enter the Item column as shown below.
- In the Item column, you have to enter each product name.

- Next, to calculate the Quantity of each product, we have to use the following formula in cellÂ
**Q6.**

`=SUMIF($C$6:$C$15,P6,$D$6:$D$15)+SUMIF($H$6:$H$15,P6,$I$6:$I$15)-SUMIF($L$6:$L$15,P6,$M$6:$M$15)`

This formula will return the quantity value in the Balance Stock condition.

- Then, pressÂ
**Enter.**

- Next, drag down the
**Fill handleÂ**icon. - As a consequence, you will get the Quantity under the Balance Stock as shown below.

- Next, to calculate the Total Price of each product, we have to use the following formula in cellÂ
**R6.**

`=Q6*E6`

- Then, pressÂ
**Enter.**

** **

- Next, drag down the
**Fill handleÂ**icon. - As a consequence, you will get the Total Price under the Stock Out as shown below.

Following the above process step by step, we will finally be able to make a stock balance sheet.

**ðŸ”Ž How Does the Formula Work? **

**SUMIF($C$6:$C$15,P6,$D$6:$D$15)**

Here, **$C$6:$C$15** is the range where we will check our criteria. **P6 **means** Product A **is the condition or criteria. We check if the** Item** is **Product A** or not. Lastly, **$D$6:$D$15** is the sum range where we will perform the sum operation of the selected rows. That function will finally return us** 150**.

**SUMIF($C$6:$C$15,P6,$D$6:$D$15)+SUMIF($H$6:$H$15,P6,$I$6:$I$15)**

Here, Plus sign **(+)** is used for **OR** operation. The **SUMIF($H$6:$H$15,P6,$I$6:$I$15) **will sum up** Product Aâ€™s** quantity and return us **100. **The whole formula will sum up** Product Aâ€™s** **Opening Stock** and **Stock In** quantities and return us **250**.

**SUMIF($C$6:$C$15,P6,$D$6:$D$15)+SUMIF($H$6:$H$15,P6,$I$6:$I$15)-SUMIF($L$6:$L$15,P6,$M$6:$M$15)**

This formula adds the quantity of Product Aâ€™s in **Opening Stock** and **Stock In**, subtracts the quantity of **Stock Out**, and returns **170**.

**Read More:** How to Make Trial Balance in Excel

## ðŸ’¬ Things to Remember

âœŽ When you use the **SUMIF** function carefully give all the required parentheses. Then, you should also make the **range** and **[sum_range] **absolute cell reference, otherwise, you will not get the proper value.

âœŽ Next, you have to adjust row height after following each method.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding.

## Conclusion

Thatâ€™s the end of todayâ€™s session. I strongly believe that from now you may be able to make a stock balance sheet in Excel. If you have any queries or recommendations, please share them in the comments section below.Â Keep learning new methods and keep growing!

**Related Articles**

- How to Prepare Balance Sheet from Trial Balance in Excel
- How to Calculate Running Balance Using Excel Formula
- How to Keep a Running Balance in Excel
- Debit Credit Balance Sheet with Excel Formula
- Calculate Debit Credit Running Balance Using Excel Formula
- How to Make Profit and Loss Account and Balance Sheet in Excel

I’ve followed and read carefully, I’ve not done accounts,but I’ve used your formula and I can proudly say it’s of great help.Can I prepare it in an empty Excel sheet so that if I put the figure it will automatically balance?

Thank you for your question. Yes, you can prepare it on an empty Excel sheet so that it will balance automatically when the figure is entered.