Quick Tip

When, why and how to use data tables

Data tables can make it much easier (and faster) to work with your existing data. 

You may already have a range of cells that you are using as a table, with rows containing information and column headings above it. If you designate it as a table, Excel knows that’s what it is for and you get more options to make it easier to work with. The overall purpose remains the same. 

Let’s take a look at some of the benefits and drawbacks of designating a range of cells as a data table.

Benefits of using defined data tables

• Quick and easy to add rows without needing to update total formulas
• Quick and easy to keep formatting consistent when table size changes
• Referencing data from the table in a hierarchical fashion can be easier to follow
  (based on properties or attributes of row records, rather than cell references)
• Easily use a form to add records to existing data
• Easy to use in conjunction with defined scenario analysis

Use a data table if:

• Your data will be changed over time (such as by adding or deleting rows, or modifying existing information)
• Information in your table will be changed multiple times after you create it
• Multiple users will be changing or updating the information
• Each row has the same properties or fields as the others

Drawbacks of using defined data tables

• Can be slower to recalculate if your dataset is large
• Many users are not used to seeing data tables and how formulas reference them
• Ideal for when data rows are homogenous, but not when different rows represent completely different concepts

Avoid using a data table if:

• Your rows require formulas that are substantially different from each other in different rows
• You have a large dataset with many rows and/or columns

If you have many different formulas between rows or columns of an otherwise homogeneous table, consider whether there is a better way of designing your intended outcome. You might regret it if some future use (possibly even you) thinks things look the same and presses “fill down” after updating something…

The benefits of using a data table are not as compelling if your data is unlikely to change after its initial creation, and your spreadsheet won’t be used by other people.

Watch out for falling into the trap of imagining your new spreadsheet won’t change much or won’t be used by others. Spreadsheets have a way of evolving and living beyond your predictions (of purpose, use, users, scope and time)!

Keyboard shortcut to define a data table

If you already have a range of cells that is set out like a table (with rows of data and column headings above it), you can convert it to a data table.

You can insert a new data table, or make one from existing data, by selecting the data and using this shortcut:

Action	Shortcut
Insert a data table	Ctrl + T

If your selection already includes a header row, check the box indicating this. (Data tables must always have a header row; if yours doesn’t, a new header row will be created).

You can change the table formatting using the “Table Design” tab of the ribbon toolbar.