Some Useful tips for MS-Excel Table

We every one occupy, descent & run data in Excel… but converting them into an Excel Table (Empowered Dataset) has its own set of advantages….
Here is a summary of the major differences along plus a Dataset & an Excel Table…



# Dataset Excel Table
1 Dataset Formatting is manual. Excel Table formatting can be done through Table Style &Table Style Options with Light, Medium & Dark variations (available from context Menu Tab: Table Tools >>>.Design >>> Table Style / Table Style Options).
2 No Link to External Data Sources External data can be linked and imported to Excel in the form of an Excel Table and can be refreshed as and when required. (available from context Menu Tab: Table Tools >>>.Design >>> External Table Data).
3 Cannot be connect to aSharePoint List. Can be exported to a SharePoint List (with the necessary accesses…)
4 Data can be filtered using AutoFilter & Advanced Filter. In addition to AutoFilter & Advanced Filter, data can also befiltered using Slicer Operations.
Slicer Operations not possible on a dataset.
5 Can be referenced and accessed using a ‘Named Range’… which does not auto-extend / auto-resize. In addition to a ‘Named Range’, can be referenced and accessed using a ‘Table Name’… which auto-extends / auto-resizes based on addition / deletion of records.
6 No direct shortcut to select an entire data column Ctrl + Spacebar can be used to select an entire Table column (+ Table column header, using the shortcut twice).
7 No direct shortcut to select an entire record / data row Shirt + Spacebar can be use to select an entire Table Record.
8 No direct shortcut to Insert / Delete Data Rows / Columns Ctrl + Shift + + / Ctrl + Shirt + – can be used to Table Records.
9 No direct shortcut to cycle through the extreme points (cells) of a Dataset. Ctrl + . can be used to cycle through the extreme (cells) of an Excel Table.
10 Data columns needs to besummarized manually. Ctrl + Shift + T can be used to toggle the Total Row – to summarize the Table columns with the help of a summary function / equation.
11 Custom Views can be created on a workbook containing one or more datasets. Custom Views cannot be created on a workbook containing one or more Excel Tables.
12 Swapping of columnscan be done using theShift key. Swapping of columns is more intuitive and can be easily done in an Excel Table.
13 Can be summarizedusing a Pivot Table. Can be summarized using a Pivot Table which can adjustand account for any additions / deletions of Table records.
14 Calculations on a dataset can be done using Cell References / Defined Names. In addition to Cell References / Defined Names, calculations can be done using Structured Referencing in Excel Tables.
15 Can be converted to anExcel Table using Ctrl + T(Ctrl + L – Excel List – for Excel 2003) Can be converted back to a dataset.
(available from context Menu Tab: Table Tools >>>.Design >>> Tools >>> Convert to Range).
Posted in Techtips and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *