Excel Tables - appropriate to use?

[Graemea]

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!

 

[ClaireS]

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!

 

[Graemea]

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

 

[KeepTrying]

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,

 

[ClaireS]

True, but neither does anything else much!