• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Table

I have a table that is attached. When a new row is added formulae with values automatically appear in two rows where data has to be entered. I have tried my best that this does not happen but it does. In the entire columns there is no such formula that Control F could find other than on the new row.
Perhaps someone can help find this bug.
BTW I am using Excel 2016.
Excuse me if I have overlooked something very obvious.
 

Attachments

  • TableBug.xlsx
    23.5 KB · Views: 6
As far as I can see there are formulas in col K L N on every row
Change the table to a range if you do not want to extend the formula to the following rows
 
It is not a bug, but a feature of tables.
They auto extend: meaning if you add new data in the first row below the table, the table is auto-adjusted or resized to the new range, thus including the new data. Also formulae used will get filled down automatically.
To stop this behaviour you can check out the "Stop Automatically Expanding" Tables. The button appears when you add a new row.
upload_2018-9-13_17-4-3.png
Which formulae are filled down? The very first formula entered in each of the columns remains in the table definition. Even when you have overwritten some with values, and even when no longer formulae are present in the column. Which seems to be the case in the columns E and H. The formulae in columns K and N are structured ones.
That is why you see those green corners at those cells in E and H.
upload_2018-9-13_17-10-17.png

To solve it. Redefine your table: change it to a range, change back to table using CTRL+T. Simply clicking Ignore Error does not stop the behaviour. Unfortunately the structured formulae are lost, since they converted using cell reference style by changing the table to a range.
 
@pecoflyer Is it a good idea to leave out calculations in a table?
Is it possible to get calculated fiels in a Pivot table based on this table (without the calculated columns)?
 
Last edited by a moderator:
Changing a table to a range will not delete the existing calculations, but stop adding formulas when you add new rows.
As for calculated fields, all depend on what you want to do
 
Back
Top