I earlier versions of Excel there were some pretty horrid autoformats you could use to quickly format your tables. These are gone from Excel 2007 – thankfully!
Instead, Excel 2007 offers sophisticated formatting options, but they can be applied only if you’re prepared to convert your range to a table. However, this isn’t always a desirable outcome but luckily there is a work around.
Start by selecting your data and click Home tab > Format as Table and select a table format. You will notice that Excel applies an AutoFilter to your data which is typically the most obvious part of the conversion that you don’t want. Other things come with this table format too – if you have data alongside the table – but not part of it – and you delete a row from a table or insert a row into a table, the data alongside remains in place.
So, if you want to retain the nice formatting but not the table behaviour, click in the table and choose Table Tools > Design tab and click the Convert to Range option and click Yes when prompted to convert the table to a normal range. The range will go back to behaving like a typical Excel range and the formatting will be retained.