+ Reply to Thread
Results 1 to 9 of 9

How can autosum of column auto-update if new rows are inserted

  1. #1
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    How can autosum of column auto-update if new rows are inserted

    I'm making a spreadsheet of my daily expenses. My rows are populated with items I purchase such as gas, food, etc. My columns are each day of the month.

    I autosum each row, and that total gets autosummed down the column to arrive at total expenses for the month.

    Occasionally I insert a new row because I think of a new item I want to include. However, this causes a gap at the end of the row where the autosum usually is. It's not the biggest deal, but I then have to manually drag the bottom right corner of the cell above it to copy the autosum formula down to the new row inserted.

    Is there a way where this can dynamically update, and I don't have to keep manually fixing the autosum for each new row inserted?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: How can autosum of column auto-update if new rows are inserted

    After the last row of data, insert two empty rows. Make sure your sum includes these two rows. When you add a new item, highlight the first of these two rows and insert a new row. Use this new row for your new item. Its values will automatically be included in the sum.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: How can autosum of column auto-update if new rows are inserted

    Take a look at converting your "table" to an excel Table (Insert/Table). You could then just add rows at the bottom and the column calc will get copied automatically
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: How can autosum of column auto-update if new rows are inserted

    Thanks for responding guys. The only problem with your answers is I want to insert these rows in the middle of my spreadsheet and not at the bottom.

    I have my first column categorized by food and other household items, so I can't just add new items to the bottom as they won't be in the proper category.

    What I'm looking to do is insert a row in, say, row 15 of a 40 row spreadsheet and have the autosum formula include this automatically. Is there a way to do this in the middle of other rows, or is only possible if it's appended to the bottom after all the other rows?

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: How can autosum of column auto-update if new rows are inserted

    If you insert a row in the middle of a range of data, then the auto sum should include that information.

  6. #6
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: How can autosum of column auto-update if new rows are inserted

    That's exactly my problem: it's not including it, and the cell where there should be a value which excel dynamically updated is merely blank.

    So this is not excel's normal behavior?

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2405 Win 11 Home 64 Bit
    Posts
    23,882

    Re: How can autosum of column auto-update if new rows are inserted

    If you have data in column A in rows 1 through 10 and the formula in row 11 is =sum(A1:A10), then if you insert a row at row 5, the formula in row 12 will now read =sum(A1:A11).

    Is your calculation mode set to manual or automatic. If manual, then after entering data, you will need to click on the F9 key or change the calculation mode to automatic.

  8. #8
    Forum Contributor
    Join Date
    12-07-2013
    Location
    Staten Island, New York
    MS-Off Ver
    Excel 2010
    Posts
    106

    Re: How can autosum of column auto-update if new rows are inserted

    I'm sorry, I didn't totally describe this correctly. Yes, when I insert a row the autosum at the bottom of the column updates to include this row.

    What I meant is the autosum at the end of the row doesn't automatically add itself there. So let's say I have each row of columns A-F of expenses and I have column G in each row autosumming each row.

    Is there a way to have the autosum in column G dynamically add itself once I insert a new row? Each time I insert a new row I have to manually add the autosum in at the end of it (in column G). Is there a way for it to automatically put itself there?

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: How can autosum of column auto-update if new rows are inserted

    If your data is in an Excel Table, right click in the row below where you want to insert a row and click on Insert then click on Table Rows Above. All formulae will be properly inserted ready for your addition of data.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sum of columns to right - update when new column inserted
    By miro2021 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-21-2013, 11:00 AM
  2. auto update formula if rows inserted
    By ktchegi in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 04-20-2007, 07:51 AM
  3. Replies: 2
    Last Post: 05-30-2006, 01:55 PM
  4. Formula doesn't update when rows are inserted
    By arreferee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-12-2006, 11:55 AM
  5. AUTO INSERT A COLUMN'S FORMULA IN MANUALLY INSERTED ROWS
    By JLah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-20-2006, 11:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1