From Excel 2007 onwards you can use the Table command to convert a data list or set into a formatted Excel Table. Once your data is converted into a table you can use their many features such a filtering, sorting, searching to help you view and organise your data.
- Ensure your data is organised into rows and columns. Each row should contain one specific record if data. For example one sales record or transaction.
- The data list or set should not contain any blank rows or columns. Individual cells maybe blank, but not full rows or columns. Your data should be contiguous.
- All columns should have headers. Each of your columns should have a unique short but descriptive heading, and be contained in the row above your first row of data.
- Each column should contain only one data type. This could be text, a date or currency.
- Your data list or set should be stored separately. Your data should ideally be kept on a separate worksheet if possible. If it cannot be housed in its own worksheet then ensure there is one blank row and one blank column between your data set and any other data on your worksheet.
- Each row should contain one record set. For example a sales order or transaction, which will ideally have a unique identifier such as an order reference or number.
Now we have our data optimally prepared we are ready to convert to an Excel Table.
- Select a cell in your data set
- Select the Insert Tab
- In the Tables Group, select the Table command
- In Create Table dialog box the range for your data should automatically appear as well as the My Table Has Headers option will be checked
- Hit OK to select these settings
Your data will be now be an Excel Table, and will be formatted in the default style for an Excel Table which you can change if you choose to.
- Hit the Home tab
- Select the Styles Group
- Navigate to the Format As Table
A gallery of available table formats will appear, hover over a format to see what your date set will look like. Or, you can create your very own style by clicking on the New Table Style button below the table formats.
When you have finished with your formatting, just click a cell outside of your data table to remove the Design Tab. If you need to change the formatting at a later date then just select one of your cells in your data table to re display the Design Tab.
More Excel Tips