It used to be the case that users would have to add data to a spread sheet and then manually format the data into a table. This manual process is made far more efficient with the automatic table tool, which can turn the data into a functionality filled and beautifully designed table. Using this option gives the user freedom to customise the styles of the tables rather than using the standard default style when you insert a table manually. This article will examine how to use this functionality.

Let's begin by creating a list in Excel with some basic values. In this example we have a sales spread sheet with a list of sales including the date, the person who made the sale and the sale value. Highlight the data and go to the Data tab on the ribbon. In the styles section is a 'Format as Table' option. Selecting this option presents a range of automatic tables in a variety of formats. In its simplest form, all the user needs to do is pick a style and the data is automatically put into a table. There is an option to choose if the table contains headers so they can be formatted slightly differently in order to distinguish them from the data.

Rather than having to completely reformat the table each time the user wants to try a different style, hover the mouse over a new style and the live data will show the preview style. The data will only take on the new format if you specifically click on the style. The automatic table that is created has an additional feature besides the formatting. Each column heading has a filter so the user can easily search for records or filter out records within a table. This does not delete any records, but merely removes them from the current view.

A table can be copied. If only the values need to be pasted then paste and select 'Paste Values' from the options list that appears. The data should be returned to list format. If a new column needs to be added or further rows included then simply dragging the columns will drag the table format with it. Simply adding text to a blank cell next to a column will automatically add that column to the table when you click out of the cell. If the additional text should not be part of the table format then there is an option available to remove the automatic formatting. Click the lightening symbol and choose 'Undo Table Auto Expansion.'

It may be that custom table styles are required. Rather than manually formatting each table the style can be added as a table template and used in the same way as any other table style. It will appear at the top of the list in the Custom section. Click the 'Format as Table' button and select 'New Table Style' which is located underneath all the template styles. Each time the user does this, a new table style will be added. Different sections of the table can be formatted individually such as the total row, alternate rows, the headers etc. This customisation is particularly useful if company branding is required. Specific company colours and styles can be set on the template and applied to all tables at the touch of a button. The exact colours can be used using the custom colour pickers on the fill tab. To delete the custom table styles simply right click on them in the list and choose 'Delete.' You can also modify these individual table designs.

The pivot table option is slightly different because a Pivot table will contain totals of the data when a particular section is chosen in the filter. Rather than simply removing the rows from the table view, instead it will sum up the totals automatically. In our example all sales relating to a particular sales person will appear in a total in one row whereas in an ordinary table all the rows will appear separately for that sales person.

Once a table has been created in this way, a separate table formatting tab on the ribbon called 'Table Tools' will appear. This gives the user the chance for more detailed formatting. This tab has some useful functionality worth exploring such as removing the duplicates automatically. A separate summary pivot table can be quickly created from the existing data table.