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

## 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.

## 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 take many big decisions.
- You can get several financial results and progress from this sheet.
- Improves inventory order accuracy.
- Reduces costs and saves time.

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

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.

**Read More: ****Balance Sheet Format in Excel with Formulas (Create with Easy Steps)**

### 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 to calculate the**Total Price**for the**Balance Stock**.

**Read More: ****How to Make Balance Sheet in Excel (2 Useful Examples)**

**Similar Readings**

**Income and Expenditure Account and Balance Sheet Format in Excel****Net Worth Formula Balance Sheet in Excel (2 Suitable Examples)****Balance Sheet Format of a Company in Excel (Download Free Template)**

### 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**.

## 💬 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.

## 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.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!