Save time with data tables

Quickly organize your data into tables for faster updating, while keeping references, totals and formats updated automatically.

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:

ActionShortcut
Insert a data tableCtrl + T
Keyboard shortcut to insert a data table

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.

For example

Suppose your existing data looks like this:

Data for table
Data for table

If you define it as a data table, it might look like this by default:

Data table (default)
Data table (default)

After changing the formatting, it might then look like this:

Data table (custom)
Data table (custom)

You can tell it is still a data table by the presence of the small resize handle in the lower right corner of the table.


Bonus Tip

Please log in to see the bonus tip.

No login?
Get one for free.

Don’t have a login?

Get a login for free on our standard plan, and get a new tip each week.

Get started >>

All logins also get access to the bonus tip each week.

Get even more

Get even more with one of our reasonably priced paid plans, with access to features like:

  • Quicksheet PDF summaries
  • Example files
  • VBA syntax
  • Additional expert tips each week
  • Advance publication

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.

Don’t have a login? Get one.