### Step 1: Create a Primary Outline

Create a dataset like as shown below.

### Step 2: Enter Opening Stock Data

- In the Item column, enter the name of the product.
- In the Unit Price column, enter the unit price.

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

### Step 3: Input Data of Stock In

- To create a Stock In, enter the Serial No and Date columns as shown below.

- To convert the products into a drop-down list in the
**Item**column, go to the**Data**tab and select**Data Validation.**

- In
**Allow**, choose**List.** - In
**Source**, select your data range. - Click
**OK**.

- This is the output.

- Enter the product name from the drop-down list in the Item column.

- Enter the quantity of each product that will be added to the stock.

- To calculate the Total Price of each product, use the following formula in
**J6.**

`=E6*I6`

- Press
**Enter.**

- Drag down the
**Fill handle**. - This is the output.

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

### Step 4: Input Data of Stock Out

To create a Stock Out dataset, enter the Date column as shown below.

- To convert the products into a drop-down list in the
**Item**column, go to the**Data**tab and select**Data Validation.**

- In
**Allow**, choose**List.** - In
**Source**, select your data range. - Click
**OK**.

- This is the output.

- Enter each product name from the drop-down list in the Item column as shown below.

- Enter the quantity that will be sold.
- This is the output.

- To calculate the Total Price of each product, use the following formula in
**R6.**

`=M6*E6`

- Press
**Enter.**

- Drag down the
**Fill handle**. - This is the output.

### Step 5: Calculate Balance Stock

**The SUMIF function** will be used to find out the quantity under the Balance Stock condition.

- Enter the Item column.
- In the Item column, enter the product name.

- To calculate the Quantity of each product, use the following formula in
**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)`

- Press
**Enter.**

- Drag down the
**Fill handle**. - This is the output.

- To calculate the Total Price of each product, use the following formula in
**R6.**

`=Q6*E6`

- Press
**Enter.**

** **

- Drag down the
**Fill handle**. - This is the output.

**Formula Breakdown**

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

Here, **$C$6:$C$15** is the range in which you will check your criteria. **P6 **means** Product A **and is the condition. **$D$6:$D$15** is the sum range of the selected rows. The function will return **150**.

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

The plus sign **(+)** is used for the **OR** operation. **SUMIF($H$6:$H$15,P6,$I$6:$I$15) **will sum the quantity of** Product A **and return **100. **The whole formula will sum the **Opening Stock **of **Product A** and the **Stock In** quantities and return **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 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

In the **SUMIF** function enter all parentheses. Make the **range** and **[sum_range] **an absolute cell reference, and adjust row height after each method.

**Download Practice Workbook**

Download this practice workbook to exercise.

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

**<< Go Back To How to Make Balance Sheet in Excel** **|****Excel For Finance** **| Learn 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.

I’ve never done this before and I love the fact that everything was so simple to understand.

So, does this mean now the Balance Stock becomes The Opening Stock?

Hi

Gabolete Fane,Glad this was helpful to you. “Balance stock” can technically become “opening stock”.

The balance stock refers to the inventory at the end of the period while the opening stock is the inventory at the beginning of a period. So, if your next period starts right after your current period, the balance stock becomes the opening stock. It depends on the time periods you are considering.

Regards

NiloyExcelDemy