Results 1 to 5 of 5

Thread: Excel Tables - appropriate to use?

  1. #1
    Board Regular
    Join Date
    Oct 2015
    Posts
    65
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Tables - appropriate to use?

    Hi,


    I have a workbook that is comprised of around a dozen worksheets, each containing data from a different source.


    There is also a single consolidation worksheet in which I extract the relevant data from the other spreadsheets, using VLOOKUP, SUMIFS and INDEX/MATCH.


    As a general principle, is it a good idea in most cases to convert the range in the consolidation worksheet to an Excel Table (Ctrl + T)? (it is already arranged in a Table-friendly format, with no blank cells etc)


    Are there any disadvantages or issues to be concerned with if converting the consolidation worksheet to an Excel Table?


    Thanks!

  2. #2
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Tables - appropriate to use?

    Are there any disadvantages or issues to be concerned with if converting the consolidation worksheet to an Excel Table?
    Since reluctantly taking on tables when they first came out, being a trainer, I quickly found them to be a game-changer.

    Advantages
    :
    • New data entered/pasted below the table is automatically added to it. Charts and Lookups update instantly, Pivot Tables just need a Refresh. No more Change Data Source!
    • New rows inherit formatting, formulae, data validation...
    • Intelligent formatting e.g. alternate row banding.
    • Total row allows a variety of SUBTOTAL functions, displaying filtered results.
    • Formulae copy to the entire column when entered/edited (though this can also be a disadvantage, see below)
    • Structured cell referencing e.g. [@Sales]+[@VAT]
    • Easy to select entire columns, with or without header row. Reposition by dragging.
    • From v2016, can be filtered using slicers.
    • Required when using Power Query/Power Pivot.


    But there are also a few Disadvantages​:
    • Cannot be used in shared workbooks or in any workbook where Custom Views are required.
    • Formulae copy to the entire column when entered/edited. They do leave any overtyped cells alone, but it's tricky if you have entered some "exceptions" manually and then want to edit the master formula, as it must be entered in the entire column, or Excel will retain the old formula for new rows. So you need to copy out your hard-coded values, edit the formula, then paste the hard-coded values back in again.
    • Cannot directly be used as data source for Data Validation. Solution: give the DV range an "old fashioned" range name AS WELL. This will "point to" the table column and Bob's your uncle.
    • Tricky to do partially fixed cell references because Microsoft have decreed that of you copy a formula rightwards it will reference the next column if you are using structured referencing. So if I need to do this, I usually revert to good old A1 notation with dollars.


    On the whole, I think tables are a great feature. BTW, because of their special features, I always use a prefix on my table name (usually tblWhatever). Reminds me that they are tables and not just any old named range. Also makes it easier to find if you can't remember what you called it and are e.g. in the middle of typing a VLOOKUP formula. Just start with "tbl" and you get a list of all your tables.

    Have fun!
    Last edited by ClaireS; Apr 13th, 2019 at 04:30 PM.

  3. #3
    Board Regular
    Join Date
    Oct 2015
    Posts
    65
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Tables - appropriate to use?

    Thanks very much, that's a really helpful answer!!!

  4. #4
    Board Regular
    Join Date
    Aug 2012
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Tables - appropriate to use?

    Just to add as a disadvantage:
    New rows inherit formatting, formulae, data validation...
    It won't work if you protect the worksheet.

    Have a nice day.
    Kind Regards,
    Post a smaller screen shot: MrExcel HTML Maker.
    Code tags for posting code
    Test screen shot, codes etc: here

    Excel 2010 (32-bit) and Office 365
    (32-bit)

  5. #5
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Tables - appropriate to use?

    True, but neither does anything else much!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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