Save time with data tables
Quickly organize your data into tables for faster updating, while keeping references, totals and formats updated automatically.
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.
For example
Suppose your existing data looks like this:
If you define it as a data table, it might look like this by default:
After changing the formatting, it might then look like this:
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.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
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
View our plans to see what else you are missing, or join for free on our Standard plan.