How to create a table out of a data range for data analysis

When you create a table, you can manage and analyze the data in that table, independently of data outside the table. For example, you can filter table columns, add a row for totals, apply table formatting, and publish a table to a server that is running Windows SharePoint Services 3.0 or Microsoft SharePoint Foundation 2010.

If you no longer want to work with your data in a table, you can convert the table to a regular range  of data while keeping any table style formatting that you applied. When you no longer need a table and the data that it contains, you can delete it.

Note: Download the sample workbook to practice this exercise.

Applies to: Microsoft Excel 2007, 2010 and 2013

The screen shot below will be used for this example.








1.  Select any cell within the data range A1:F21.

2.  Select the Insert tab and then Table as per screen shot below.





3.  Input the location of your data, tick the ‘My table has headers’ box and select OK.






4.  To remove duplicate records from the table, select the Design tab and Remove Duplicates.

Records 004,0010 and 0017 have duplicate values.


Ensure that you select a cell within the table for the design contextual tab to be activated.

5.     Select OK.

6.     The final table will look like the table below.