Concept: Live Data in Workbooks

When you add report data to a workbook and you choose to keep the workbook in sync with the original report data, that data is called live data. You can also add report data to a workbook without keeping the data in sync; we refer to this data as static data.

Option	Description
Live Data	The inserted data in the workbook maintains a connection to the Workday report, providing 1-way updates from the report to the workbook. (This setting doesn't enable writing data from the workbook to the report.) You can't manually edit the inserted live data in the workbook.
Static Values	Inserted data in the workbook doesn't maintain a connection to the original report. The data is a snapshot of the report data, as of the time you insert it. You can edit the inserted data in the workbook. The maximum number of cells that Worksheets can insert as static values is 5 million (5,000,000).

Live Data Tables and Structured References

Live data areas in Worksheets workbooks are similar to Excel tables. When Worksheets creates a live data area, it assigns a table name to the area. Optionally you can rename the table. Using a special syntax, you can create references to the data in a live data area using the table and column name; these references are called structured references
, and the range of cells is automatically updated whenever the live data is refreshed. For more details about structured references, see Concept: Structured References in Workbooks.

Formula Columns and Note Columns in Live Data

To work with formula columns:

To add a new formula column without using the data wizard, right-click the header of any live data column to add a new formula column to the right or left. Adding a formula column using this method causes a live data refresh.

To add a new formula column without using the data wizard, type the formula into the 2nd row of a column adjacent to the live data area and press Ente. Worksheets automatically adds the formula into the live data area and applies the formula to all rows in the live data area, without doing a live data refresh. (Skip a column if you don't want to add the formula into the live data as a formula column.)

Type a formula into the cell below the header and press Enter; Worksheets applies the formula to all rows in the live data area.

Double-click the column header cell to edit its name.

To work with note columns in the live data area:

You must have already set up a key column (field) using the data wizard to see this option in the menu. A key
uniquely identifies the data in each row. The key column that you select must contain unique values, such as Employee IDs. The value must not change over time, and the value must exist in only one row
of the data. Worksheets uses the key to match note rows to live data rows. Make sure your key is unique; if it isn't, notes will be lost.

Type notes as desired into the individual cells. To paste text into a cell, double-click in the cell first.

Double-click the column header cell to edit its name.

You can right-click the header of any live data column to add a new note column to the right or left. Adding a note column using this method causes a live data refresh.

Keep in mind that during a live data refresh, Worksheets doesn't preserve formatting for note columns.

If rows are added during a live data refresh, Worksheets preserves the alignment of notes with their corresponding rows. If rows are removed during a live data refresh – for example, you delete a prompt – any notes related to the deleted prompt are removed but the data is saved in Workday. If you add the prompt again later, Worksheets shows the notes again in their corresponding rows.

If you type a note into the 2nd row of a column adjacent to the live data area and press Enter, Worksheets automatically adds a note column into the live data area without doing a live data refresh. (Skip a column if you don't want to add the column as a note column.)

Filtering

When you apply a filter, it's applied to that specific live data. The filter doesn't reapply after you refresh the live data; you need to remove and re-apply the filter.

Sorting

You can sort live data areas and entry areas in a workbook. Optionally, your sort can include contiguous columns in the workbook that are outside the live data area or entry area. By default, if your workbook sheet contains live data and you select

Advanced Sort

on the

Data

menu, Worksheets selects only the live data area for the sort. If you want to include additional columns in the sort, highlight the entire range that you want to sort before selecting

Advanced Sort

When you refresh live data in a workbook, Worksheets preserves the sort order that you set in the

Order By

option in the data wizard, but doesn't preserve standard sorting within the workbook (using

Data

Sort

Formatting

In the Data Wizard

Select Columns

page, you can use the

Column Options

menu to set the data format for your live data columns (if your live data is from an Advanced report). If you set the formatting here, Worksheets preserves the data format when the live data is refreshed. Data formatting isn't available for Note columns.

We recommend that you not change any font/style settings (such as bold text) in the workbook live data. When you refresh the live data, Worksheets resets font/style formatting. If you want to change your live data's appearance, you can use the ARRAYAREA function on a different sheet to insert the data from the live data area, and format those rows and columns as desired.

Group Column Headings

If your original report definition contains Group Column Headings, those values become column headings in your workbook. If you want to change the column headings later, you can do so using the SELECT formula to copy the data into a different sheet and assign new column names.

Here's an example formula that copies data from Sheet1 into the current sheet and assigns the new column names ID and Name:

=SELECT("SELECT `~Employee~ ID` `ID`, `Employee Name` `Name` from ? ",ARRAYAREA(Sheet1!A1))

Although this example is related to Group Column Headings, you can use it to change any live data column headings.