Table or Dynamic Range?

  • Been searching around and it seems folks are pretty split. Figured I would see what you all say.


    I am in the process of building out a workbook that unfortunately has to fit a pre-determined visual layout (thanks sales). As a result I will have one large database worksheet that will continue to grow with time, and multiple worksheets referencing this data. As of now that would amount to roughly 5k formulas referencing the data, and that will grow with each new year. In the hopes of minimizing inefficiencies, I was wondering what would be the best setup for the database worksheet. Just reference the data via a dynamic named range utilizing index/match, or have that database built as a table. Are the differences between the two significant enough to notice, or are they close enough either is fine? Any info is greatly appreciated!!

  • I'd suggest using the table. It's more "native" should will be faster than first having to do a formula calculation (even a simpel one) to define the table range. Also, using the strcutural references it'll be easier later to determine what all the formulas are doing (assuming good field names).

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Copy that, thank you!


    Out of curiosity are there any rules of thumbs around tables, like they are good up until X rows and Y columns of data, or is it pretty much machine dependent with newer versions of excel?

  • Between dynamic and table, I'd say the table will generally always win (assuming you don't need XL2003 and older compliancy). Beyond that, your next consideration is whether the data should be in a database like Access vs. Excel. If you start handling over 100k of rows, XL will start to struggle to keep up.

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!