Lisa has attended:
Excel Intermediate course
Auto updating a formula when adding a new row above it
Hello
I have an xls with a row I need to subtotal and work out averages based on the data above it.
I am adding a new row every week with new data and at the moment having to manually update the formulas to capture the new range.
There are four rows at the top of the table I do not want included in the sums /averages subtotals.
Is there any method of having these subtotals auto update without me having to go in and change the ranges?
thanks
Lisa
RE: Auto updating a formula when adding a new row above it
Hi Lisa
Thanks for getting in touch. Yes you can create this effect, many refer to it as a "dynamic table".
First highlight your range. Then on the Home tab select Format as Table. You will have to pick a formatting style but you can remove this on the next step. Click OK to confirm.
You may have to recreate your formulas but now when a new line of data is appended to the bottom of the table, it will know to grow and include that new row. Your formulas will now update automatically.
To remove the formatting, on the Table Design menu choose the left-most style.
Dynamic tables are one of the most useful features in Excel, I just wish they wouldn't bury it under the unhelpful title "Format"!
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
