How do I add cells to a balance sheet which will include new sum?

[Karen]

I am trying to create a balance sheet for expenses, and there are only 12
rows allowed. I need an endless number of rows and the total to continually
be added up at the end of each row. When I add more rows, it seems that the
sum function does not work, and I will have to end up adding them myself! Is
there any way to extend the number of rows and still keep the function of
adding the sum? Please help with any tips, I am new to Excel....Thanks!

 

[Dave O]

Hi, Karen-
Do you mind restating your question? You say "only 12 rows allowed"
and then you talk about difficulties encountered when you add more
rows.

If I can guess, you're using a pre-designed "form" that has 12 summary
lines. You need an unlimited number of rows to that summarize into
those 12 lines. You have a SUM() function in place but it doesn't
always work when you add rows.

Did I get any of that right?

If yes, let's say your 12 summary lines are for expense types A, B,
C... L. You can create a spot on your spreadsheet that says "Enter
type A expenses here" and specify (with the use of borders or some
other visual cue) that the user should enter type A expenses in rows 25
- 50. Your formula might read =SUM(A24:A51) Note the summed range is
actually GREATER than the "allowed" range your user knows about. This
way if additional rows are added between 25 and 50, the SUM function
will expand to include them. Then your summary line for expense A will
always be correct.

 

[Karen]

Dave O,
Thank you so much for your response! I am so confused, it is my first
attempt at using Excel...You are right, my question doesn't make any sense!
So I am in the template "Balance Sheet" and I can add rows by using the
Insert Rows function, but when I do, those new rows do not add up the sum of
the numbers I have input in the previous 12. I do not know about formulas
and am not sure how to do what you said....Can you take me step-by-step
through creating the range that is bigger than 12? I need an unlimited
number, as the list will continue to grow longer.
Thank you so much for your help, I look forward to your response.
--Karen

 

[Dave O]

Hi, Karen- Sorry for the delayed response.

Have to clarify again: you have a template with 12 rows that add up to
a sum, and when you add new rows the numbers in the new rows are not
included in the sum. Is that correct?

Assuming I have that right, the reason this happens is because you're
adding rows outside the range included in the SUM formula. Say your 12
rows are rows 1 thru 12 and your SUM formula is in row 13. If you
highlight row 13 and insert a row, the row with the formula in it will
drop down a row but it will still only reference rows 1 thru 12, not 1
thru 13.

You can have the SUM formula automatically adjust by inserting the new
rows inside the range of 2 (not 1) thru 12. In the example above if
you highlight row 5 (or any row from 2 - 12) and insert, the SUM
formula will automatically adjust.