# How to Create a Stock Balance Sheet in Excel – 5 Quick Steps

### Step 1: Create a Primary Outline

Create a dataset 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.

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

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

## Related Articles

<< Go Back To How to Make Balance Sheet in Excel |Excel For Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

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

2. 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?

Mahfuza Anika Era Feb 27, 2024 at 11:44 AM

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
Niloy
ExcelDemy

Advanced Excel Exercises with Solutions PDF