1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Table

Discussion in 'Ask an Excel Question' started by Karanbir Soin, Sep 13, 2018.

  1. Karanbir Soin

    Karanbir Soin New Member

    Messages:
    17
    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.

    Attached Files:

  2. pecoflyer

    pecoflyer Active Member

    Messages:
    212
    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
  3. Karanbir Soin

    Karanbir Soin New Member

    Messages:
    17
    I do not want formulas that appear on a new row in columns E and H
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    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.
  5. pecoflyer

    pecoflyer Active Member

    Messages:
    212
    As I said, change your table to a range and take a moment to learn what tables do
  6. Karanbir Soin

    Karanbir Soin New Member

    Messages:
    17
    @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: Sep 14, 2018
  7. pecoflyer

    pecoflyer Active Member

    Messages:
    212
    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
  8. Karanbir Soin

    Karanbir Soin New Member

    Messages:
    17
    I will give it a try. How do I change the table to a range.
    Last edited by a moderator: Sep 14, 2018
  9. pecoflyer

    pecoflyer Active Member

    Messages:
    212
    I guess that's somewhere in the Insert ribbon..

Share This Page