Microsoft first introduced tables in the 2007 version of Excel.  They are a brilliant way of organising data in a structured way and can help to make your spreadsheets look very professional.  There are one or two drawbacks, but overall, I really like them.

What is an Excel Table?

A table is a range of cells which contain data in "fields" which are created automatically from column headings.  First you enter your headings, then click on the Insert option and select Table.  You'll then be able to see all the table tools as a new ribbon, including a multitude of design options to make the colour scheme fit with your needs.  Here's a simple example:

The great things about tables...

So what are the advantages of using tables?

• New rows are quickly and simply added just by pressing the tab key from the last cell (bottom right - you'll see a small blue mark in the corner)
• When you add a new row all formatting is automatically copied to the new row
• Any formulas you have used are also automatically applied to the new row
• Formulas refer to the FIELD (column) name making it easier to understand what the elements of the formula are; no more trying to work out column letters!
• If you use a table column in a dropdown list, this will automatically expand as your table grows
• If you use Pivot Tables, the data range expands automatically so you don't need to keep adjusting as you add more information

But...

Ah, there's always a but!

Probably the biggest issue with tables is "protection". As mentioned above, when a new row is added, the formulas are automatically copied down to the new row.  But what if you want to protect the worksheet to stop anyone accidentally changing the formulas?  If protection is switched on, you will no longer be able to add rows to the table!

This has been an issue since day 1 and believe me there has been plenty of pressure on Microsoft to resolve this, but as of today this is still a problem.  

So how to get round this? Well, if you really need to protect the worksheet, the only way is to use a bit of VBA programming.  That's a bit beyond the scope of this article, but its a standard part of the tools I create, so just get in touch if its something you want to find out more about.