Top 5 reasons you should be using data tables
If you are not using data tables in Excel then you are missing out on a very powerful tool, here are the top 5 reasons you should be using data tables. On the surface data tables do not look that useful or different from standard data sets but the features listed below will change your mind.
- Quick formatting
- Streamlined data entry
- Quick totals
- Visible headers
- Pivot linkage
Background to Data tables
Data tables were an impressive addition to Excel 2007 making the analysing and managing data much easier than previously. Tables have a host of features that makes data much easier to manage and analyse and also allows data to be added and removed easily.
How to change dataset into a table
Click into the data and navigate to the Insert Ribbon and select the table icon.
Hit OK. The data is now placed within a Table and as can be seen below the has inbuilt drop down filter options.
A calcluated field can be added which will adjust for each row in the table. The benefit of calculated column is that it expands to include any added data when added.
Data tables are also helpful to ensure data structure before creating a pivot, the source data also then expands when additional data is added, eliminating the possibility of data being omitted.
Quick formatting Tables
Excel comes with 50 pre-designed table formatting options that live update when hovered over allowing you to see instantly what the final formatting will look like.
Header Rows, Total rows and banded rows can quickly be toggled using the check boxes on the tables design ribbon.
Streamlined data entry
Any data added below the table are automatically added to the table, this ensures that data is encapsulated in charts and pivot tables without the need to change the source data.
Total rows can be added to the bottom of the table, these can then be amended using the drop down box select the total type.
Any of the following totals can quickly be selected: –
Headers are always visible when the table is selected making data so much easier to track when using very large data sets.
Excel replaces the column header cells with the headers from the table.
Automatically update pivot
Any data added to the bottom of the table is automatically pulled into any pivot that is driven by the data. This avoids any embarrassing omissions catching you out when your data is reviewed.