Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Not sure how to do a balance sheet on excel

Copper Contributor

Hi everyone! Thanks for helping in advance. 

I'm trying to create an excel sheet where I am able to do a running balance of my income and expense for my small business. 

 

C: Income / Expense
D:Amount

E: Running Balance

 

Basically, I want a formula that is able to capture that I have stated that if C is income, then it'll add the amount in D to the running balance, and the opposite to happen where if C is an expense, then it'll minus the amount in D to the running balance. 
I also have a "current balance" cell at the top as a summary of my current expenditure. 

 

Please help! I have been trying to find something and also trying out different formulas but they all don't seem to work. Most of the forums show the amounts as 2 separate columns but I'd like them to be in the same column; not sure if this is possible. Please enlighten me!

7 Replies

@rainbowprickles 

This assumes you are entering the formula in cell E5 and the values in D are positive regardless of being income or expense.   

 

=IF(C5="income",IFERROR(E4+D5,D5),E4-D5)

Just copy the above formula down for every entry in your balance sheet. 

 

And for the balance summary shown in D1:

=OFFSET(E4,COUNTA(E:E)-1,0)

 

DexterG_III_0-1663686946828.png

 

If you have Excel 365, dynamic arrays can be used to avoid copying the formula down for each entry but I've stuck with the legacy approach not knowing what version you are using.  

 

Hope this helps,

Dexter

@DexterG_III 

 

Seems to be an issue.

I have categorised the Income and Expense as a Data Validation, so when I click on the cell in that column, it comes out as [@[Income / Expense]] instead of the cell number. Does that affect? 


Also, here are the exact cells. 

C: Income / Expense

G: Amount
H: Running Balance
G3: Current Balance (the summary that I was referring to.)

 

The first row, 6, is my capital amount which I have the first input of an amount.

@rainbowprickles Thanks rainbow, any chance you could share a copy representative of your current workbook with any private details removed?  

 

 

I can't seem to take a screengrab so I have made a separate document for you to view.
https://1drv.ms/x/s!Agw6dWGNwj0JnFN6xR320-KHPIZD?e=WIblem
best response confirmed by Grahmfs13 (Microsoft)
Solution

@rainbowprickles Thanks for sharing. 

 

I made a minor tweak to the formula entered in H7, because it is in a table you shouldn't have to re-enter it as new rows are added.  

=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))

Another minor tweak to the current balance:

=OFFSET(H6,COUNTA(H:H)-1,0)

 Here is a screen shot showing the ranges the running balance utilizes:

DexterG_III_0-1663741872538.png

And here is the formula in action showing both the running balance and the current balance being updated as new records are entered/modified:

2022-09-20_23-28-24.gif

 

Hope this helps.  

Let me know of any other questions.  Revised workbook is attached.  

Dexter

Thank you so much, it's working out so well now! I was just wondering if you could explain the formulas? I'd like to learn how to do it on my own for future references.

@rainbowprickles My pleasure.  

 

The if formula is very commonly used. 

=If(

condition to evaluate,

value to return if the condition is true,

value to return if the condition if false

)

 

In your case if the value in cell C7 is "income" then return the previous balance in the running total and add the amount in G7.  Otherwise, return the previous balance in the running total and subtract the amount in G7.  

 

=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))

 

The IFERROR formula is similar in nature but it's condition is already implied.  In this case, if the formula H6+G7 returns an error, then use the value in G7 instead.  The only time I expect an error is in the very first row because it is trying to add the text header "Running Balance" to a number which doesn't work too well.  So, our starting balance will always be determined by the first row.  Perhaps inserting a row above row 7 is appropriate and ensure the value in G7 is, indeed, the starting balance.

 

 The next formula is not as common, but useful in managing references to ranges which are always growing and/or shrinking, particularly if they are not inside a table object.  It actually receives up to 5 inputs, although only 3 are used in your example

=OFFSET(H6,COUNTA(H:H)-1,0)

 =OFFSET(

  1. Starting Cell Reference,
  2. Number of rows to move from the Starting Cell Reference (positive values will move down, negative values up, and a 0 will not move at all),
  3. Number of columns to offset (just like rows except left or right of the Starting Cell Reference)
  4. (not used in your situation) The height of the offset.  This changes the offset from being a reference to a single cell to being a reference to a range of cells.  Similar to step 2 you can calculate how many rows contain data in order to determine the range height.  
  5. (not used in your situation) The width of the offset.  Just like step 4 except it extends the range left or right instead of up or down

)

 

So in your case, the Starting Cell Reference was H6 or the header "Running Balance".  To determine how many rows to offset, we count the number of cells with data in column H using COUNTA(H:H).  There are 8 cells containing data and we subtract one for the header row.   Therefor the formula starts with H6 and moves 7 rows down which is row 13.   

 

But lets say we wanted to sum the values in the amount column we would use

=OFFSET(

  1. G7,
  2. 0, (we don't want to move any cells this time because G7 should be the beginning of our range)
  3. 0, (again, don't want to move cells)
  4. COUNTA(G:G), -2 this counts the cells containing data but we need to subtract 2 to account for both the header row (G6 and the value in H3)
  5. 1 (we want the range to be only 1 column wide)

)

 

This would evaluate to be cells G7:G13.  And if we wanted to sum the values in that range we would simply wrap the offset formula inside a SUM() formula.  I said particularly for ranges not within a table object because you can apply a formula to a table range and it will always grow or shrink according to how many rows are in the table by referring to Table_Name[Header_Name] instead of normal cell references.  

 

Hope this clarifies and glad to help.  

 

Dexter

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@rainbowprickles Thanks for sharing. 

 

I made a minor tweak to the formula entered in H7, because it is in a table you shouldn't have to re-enter it as new rows are added.  

=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))

Another minor tweak to the current balance:

=OFFSET(H6,COUNTA(H:H)-1,0)

 Here is a screen shot showing the ranges the running balance utilizes:

DexterG_III_0-1663741872538.png

And here is the formula in action showing both the running balance and the current balance being updated as new records are entered/modified:

2022-09-20_23-28-24.gif

 

Hope this helps.  

Let me know of any other questions.  Revised workbook is attached.  

Dexter

View solution in original post