Inserting rows in Excel 2003

Topic

New Reply

I am experiencing two issues when inserting rows in Excel 2003:

1. Subtotals
When I have a row comprising subtotals of the rows above, and insert a row directly above it, the subtotals do not expand to include the new row. Is there a way automatically to include the new row in the subtotal in these circumstances ?
If I insert the new row between two rows which are included in the subtotal, then the subtotal does expand to include the new row

2. Copying formulae
This is just a minor annoyance – when I insert a row, as above, sometimes formulae in the row above the inserton are copied to the new row, sometimes not. I cannot see any rhyme or reason for which are and which aren’t – and, as its inconsistent, I have to check them all. What’s going on here ?

Thanks

Martin

Reply | Quote

Replies

I am experiencing two issues when inserting rows in Excel 2003:

1. Subtotals
When I have a row comprising subtotals of the rows above, and insert a row directly above it, the subtotals do not expand to include the new row. Is there a way automatically to include the new row in the subtotal in these circumstances ?
If I insert the new row between two rows which are included in the subtotal, then the subtotal does expand to include the new row.

Martin,

This is standard Excel behavior. If you are using the SubTotal feature just remove them before inserting rows then re-apply after you’ve done your edits. If you have placed the SubTotal formula yourself what I do is always leave a blank row above Totals/SubTotals and include that blank row in the Range. Then when I need to insert I insert above the blank row, that way the insertion is considered to be within the range and the formula adjust accordingly. FYI: I also always place my Total/Sub total line on the Top of the Total/SubTotal line so it moves with Total/SubTotal line if I forget and insert above it!.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

When adding rows/cols to the end of a range, copy the outermost row/col and insert the copy(s) between it and the next row/col, then erase/repopulate the contents. Formulae referencing the range will adjust properly.

Reply | Quote

A good control tip.

Use a Black Fill Format at the top of a subtotal and/or the Left or Right Columns for Column Subtotals. If anyone trys to inset an new row at the top of the numbers the new row will have the Black Fill which should serve a a very good reminder (putting extra data will not be in Subtotals).

One step furhter is Balck Fill with a very small row and/or column height/width. Same idea but now you need to correct two items to use the newly inserted row or column.

Regards,

TD

Reply | Quote

Thanks for all three replies – I was already using the “blank row” trick, and don’t feel so stupid now.

I’m going to have to write a Function that makes this (obviously common) annoying behaviour work “properly”.

Reply | Quote

You can also define a name called, say, CellAbove and in the refersto box enter:
=INDIRECT(“R[-1]C”,0)

then in your formulas use:
=subtotal(109,a1:cellabove)
for example.

Reply | Quote

No need to feel stupid. I have noticed this quirk, but just got used to it. It also exits in EXCEL 2007. My solution was to get into the habit of highlight the cell containing the formula to see the cell range. It is easy to drag the highlighted range box to include the new cell, or just modify the range manually.

Reply | Quote

The “cellabove” function is a very elegant, and general solution !

Thank you

Reply | Quote