Top 5 reasons you should be using data tables

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.

  1. Quick formatting
  2. Streamlined data entry
  3. Quick totals
  4. Visible headers
  5. 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.

data tables in excel

Adding data tables

Hit OK.  The data is now placed within a Table and as can be seen below the has inbuilt drop down filter options.

Excel data tables

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.

Design of data tables

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.

Quick totals

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: –

None

Average

Count

Count Numbers

Max

Min

Sun

StdDev

Var

More functions..

Data tables - Totals

Visible Headers

Headers are always visible when the table is selected making data so much easier to track when using very large data sets.

Data tables in Excel - Top 5 reasons to use

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.

Leave a Reply