Excel Tables are more than just fancy formatting.  The beauty of these tables is not the instant formatting but the power it gives you in being able to analyse the data. 

Since Excel 2007, you can use the Table command to convert a set of data into a formatted Excel table.  Easily convert lists of data into Excel tables to easily sort, filter and view your data. 

Preparing your data

Before you convert the data into a table, the data needs to be organised.  Follow these guidelines to ensure your data will convert well.

1. The data should be organised into rows and columns, with each row containing information about one record, such as a client, order, or expense item.
2. The first row of the list should contain a short, descriptive and unique heading for each column.
3. Each column in the list should contain one type of data, such as dates, currency, or text.
4. Each row in the list should contain the details for one record only, like as a sales order. If possible, include a unique identifier for each row, such as an order number.
5. The list should have no blank rows, and no completely blank columns.
6. The list should be separated from any other data on the worksheet by at least one blank row and one blank column between the list and any other data.

When your data has been organised, you can create the formatted table.  There are two ways to do this.

Convert a list into a Table

Create a table with the default table styles

1. Select a single cell in the list of prepared data.
2. From the Ribbon, select the Insert menu and then the Table command in the Tables grouping.
3. In the Create Table dialog box, the range of your list should already be populated. Ensure that the My table has headers option is checked and adjust the range if necessary.
4. Click OK to accept these settings.

You will see that the list is now automatically displayed in the default table style. 

Create a table with a custom table style

1. Select a single cell in the list of prepared data.
2. From the Ribbon select the Format as Table button from the Home menu and then select the table style you would like to apply.
3. In the Format As Table dialog box, ensure that the My table has headers option is checked and adjust the range if necessary.
4. Click OK to accept these settings.

You will see that the list is now automatically displayed in the table style you selected.  I like to format my tables in different colours for different lists of data and choose a sheet tab colour to match. 

Let’s have a look and see what we can do now apart from admiring the pretty colours.

Rename the Table

When it is created, an Excel table is given a default name, such as Table1.  I always recommend changing the name to something meaningful, so you can easily identify what the data refers to later when you are constructing formulas using this table.

1. Select any cell in the table.
2. From the Ribbon, select the Table Tools menu and then the Design tab. Note that the Table Tools menu only appears when a cell in the table is selected.
3. At the far left of the menu, in the Properties grouping, click in the Table name box, to select the existing name.
4. Type a new name without spaces, such as tblClients, and press the Enter key.

Filtering the Data

Column Filters have been automatically applied.  This means you can click the dropdown arrow to the right of the column heading and apply sorting and filtering options.  The option I use the most is the Search option.  When I need to quickly find a record or group of records relating to a particular client or site, I type a text or number string into the search box. I can then see the related records and choose which ones I’d like to see.

Automatic totals

After you create an Excel table, it's easy to show the total for a column, or for multiple columns, using a built-in Table feature.

To show a total:

1. Select any cell in the table.
2. From the Ribbon, select the Table Tools menu and then the Design tab. Note that the Table Tools menu only appears when a cell in the table is selected.
3. In the Table Style Options grouping, add a check the Total Row option.
4. A totals row will be added at the bottom of the table, and one or more column of numbers may show a total.

To change or show other functions:

1. Select the totals cell for any particular column.
2. Click the dropdown arrow to the right of the selected cell.
3. Select any of the listed functions or the More Functions option at the bottom of the list and proceed to add the arguments of your chosen function.

Formula Construction

You can create formulas as you would have in an ordinary list or range but you should notice that rather than a standard cell address reference, the formula references the column with structured references utilising square brackets, an @ sign, and the column name making reading your formulas much easier.  For example =[@End Time]-[@Start Time]

The main benefit of a table is that formulas are automatically copied from the column when you add a new record.  This helps maintain a high level of data integrity by reducing errors.  You can add a new record either by inserting a new row manually or pressing the Tab key when you are in the last cell of the table (the last cell above the totals row).

When utilising totals from your table in dashboard format, you can easily read the formula because it references the table and table column names.  For example =tblTasks[[#Totals],[Task Time]].  Don’t worry, if you select the cell/s you want, the names and format of the name is automatically populated, so you don’t have to type the names in.

Automatic list expansion

When you want to add a column of data to the table, all you have to do is type the new column heading in the empty cell to the right of the last column heading and press Enter.

Also read Colour your worksheet tabs