Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by MartinM View Post
    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!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Nov 2010
    Location
    98281
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    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".

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Jan 2010
    Location
    Melbourne Australia
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The "cellabove" function is a very elegant, and general solution !

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •