Data Tables

Data Tables sit on top of your spreadsheet, are anchored from the upper left most cell the data table covers, and include all the data from the imported data set. They are a convenient way to view and manage large data sets within a spreadsheet for a number of reasons:

1. Live Connections - Data tables support live connections to hosted data and can be refreshed to pull in new data. Pivot tables and graphs can be created from data tables and will updated every time the data table is refreshed with the latest data.
2. Size - Data table size will not exceed the existing border even when the data set is updated with new data. Data inside the data table can be viewed by scrolling or ctrl + arrowing within inside the boundary. To view more or less off the data inside the table, click and drag the bottom border to increase or decrease the number of rows shown.
3. Read-only - Data tables are read only so data contained within cannot be fat fingered or manipulated.
4. Transformation - Data tables do support column level transformations by simply typing a formula in the spreadsheet and referencing a specific row in the data table rather than having to fill a function down for millions of rows. If working in a traditional spreadsheet mode is preferred, see Data table transformations below.

Data Table Transformations

While Data Tables are preferable for working with large datasets and performing column level transformations, there are instances where it is helpful to be able to place an entire dataset in a spreadsheet or perform cell level computations and transformations are needed. Conversely, you may want to transform data in a spreadsheet and then transform it back to a data table. The two functions below outline those capabilities.

Explode - Data table to spreadsheet

To transform a data table into spreadsheet format, right-click on the data table of interest and select 'Explode table' from the context menu. Once selected the data table will be copied into the cells of the underlying spreadsheet filling all the rows populated by the data.

Spreadsheet to data table

To convert a data set currently sitting in the cells of a spreadsheet to a data table, simply type =(range).

• range is the range of cells to be turned into a data frame.

Example data table creation in a spreadsheet:

=(A1:B9)

Import to specific cell

Data tables originate from the upper left most corner of the table. When importing a data table, the import window allows a user to select 'import to current sheet' and a specific cell.

Click and drag

Data tables can be moved within a sheet by clicking on the left, right, and top borders of the table and dragging to a new location. The hand icon will display when the mouse is hovering over a border that can be moved.

Resize

Data tables default to 15 rows and can be expanded or contracted by dragging the bottom border of the data table to the desired size.

Add New Columns or Computed Columns

Columns can be added to data tables by typing the function or transformation in the column adjacent to the data table. The function or transformation will be executed on any column selected and will produce a new column in the data table.

1. Write a function
2. Evaluate the function and the column is added to the data table.
3. Rename a computed column by right-clicking on the column and selecting "Rename column"

Delete/hide Column

Data table columns cannot be permanently deleted but they can easily be hidden by right clicking on the data table and selecting 'manage columns' and de-selecting the columns to be hidden.

Scroll

Mouse over a data table to scroll through the data. Position is indicated by a scroll bar that appears when the mouse is positioned over the data table. Ctrl+arrow up or down will take you to the top or bottom of the data table.

Data Table Interactions

Rename Column

Columns in a data table can be renamed by selecting the header cell and typing in a new name.

Edit Data

The data inside a data table cannot be edited. It can be manipulated on a column level by creating new columns or using filter and sort features.

Format Data

Data in data table columns can be formatted by selecting the column of interest and using standard spreadsheet formatting buttons or keyboard shortcuts.

Calculations

Calculations can be performed on data tables columns. For example, functions like SUM, MEDIAN, COUNTIF, and others can be run on individual columns within a data table. To execute a function, type your function in the desired cell and select the column of interest in the data table by clicking on it or arrowing over to select it. You will see the name of the column auto-fill in your function.

Filter and Sort

Filter/Sort Instructions can be found at the link here.