Concept: Editing Workbooks
These notes describe how to use workbook features in Worksheets that differ from other
spreadsheet applications. For more details, see the Worksheets User Guide, which you
can access from the Worksheets user interface ().
Workbook Editing
Action | Notes |
|---|---|
Use full-screen mode | In full-screen mode, Workday hides the header to display the maximum possible number of
workbook rows. Select . Workday doesn't support full-screen mode in the Safari browser. |
Add Workday report data into a workbook | We use the term live data for Workday data that you add to a workbook. Select the cell where you want to insert the data and click
Add Live Data . The Data Wizard opens and helps you find the report and add the data. You
can select whether to add the data as:
You can add data from more than 1 Workday report into a single workbook. We recommend using
a new sheet for each set of live data. Worksheets doesn't support using the Do Not Prompt at
Runtime prompt option in a report definition. |
Refresh all live data in a workbook from 1 or more reports | You must have permission to edit the workbook and to refresh live data. When you do a
refresh, Worksheets also recalculates formulas affected by changed workbook
data. You can refresh all live data areas in the workbook and
recalculate:
When
you recalculate and refresh the live data in a workbook, Worksheets
updates all data, including data in a protected range. You can't prevent
Worksheets from refreshing and recalculating workbook content. Example:
When you update an entry area in Workday Projects, or Workday runs a
scheduled live data refresh, Worksheets updates any necessary data even
if you protected the entry area or live data range. |
Refresh live data in a single range from the associated Workday
report | In the workbook, select a cell that includes live data. In the Live Data Details panel,
click either:
|
Edit workbook live data areas (add, remove, or reorder report columns)
| Click the Edit link in the Columns section of the live data
details panel. Alternatively, you can click any of the Edit links
in the panel, or the Edit Live Data button at the bottom of the
panel.)Live data column editing applies only to live data from advanced
reports. Worksheets doesn't update data references in formulas when you edit live data columns; you
need to manually update formulas that refer to any live data area that
you edit. |
Schedule live data refreshes | (Workbook owner only) In the workbook, select . For workbooks already on a schedule, select . You can also select this option to edit or delete an
existing schedule. At the scheduled time, Workday automatically:
For any prompts in the Data Wizard where you selected the Use Report Default option,
Worksheets uses the default values from the report definition when
refreshing the data; otherwise, Worksheets uses the current prompt
values displayed in the Live Data Details panel. You can create 1 schedule per workbook. When Worksheets runs a scheduled refresh, the updated data is available to users with
shared access to the workbook. If workbook ownership changes, the schedule stops running; the new owner must create a new
schedule. If the Workday report associated with the live data refresh times out 3
times consecutively, Worksheets automatically cancels the refresh and
pauses the schedule. If Worksheets pauses a schedule, the workbook owner
receives an inbox notification that includes a link to the affected
workbook. After you fix the problem that caused the report failure, you
need to manually resume the schedule by clicking
Resume in the live data panel in the
workbook.Worksheets doesn't preserve schedules when you refresh a tenant from a tenant with a
different name. You need to create new schedules for the workbooks in
the new tenant. Example: You refresh the company_name_preview
tenant from the company_name tenant.If a worker sets up a scheduled live data refresh and then leaves the company (and is in a
terminated state), the scheduled refresh returns an error in the
workbook because Workday can't run the associated report. |
Undo or redo your changes | To undo a workbook change, select . To redo a workbook change, select . Worksheets tracks your most recent 15 changes that you can undo. When you select an action that you can't undo, Worksheets displays a confirmation message.
If you continue with the action, Worksheets resets the record of your
changes. You can't undo earlier changes, unless you previously created a
named version. Keep in mind that when another person edits the workbook, they might perform an action that
resets change tracking and prevents you from undoing a change. We recommend creating a workbook version () before doing any of these actions, which Worksheets
can't undo:
You can't undo changes to workbook comments, and reverting to a previous version
doesn't restore comment changes. |
Define or edit conditional formatting rules | Select the cell or range, then navigate to . To view the existing conditional formatting rules for a range of cells,
select the range and then select . To display all conditional formatting rules for a sheet,
select the top left cell in the workbook data area. |
Insert subtotals and a grand total | Worksheets can automatically insert subtotals for sets of related data in your workbook,
and calculate a grand total. Select the cell that you want to subtotal by, and then select . You can't add subtotals in entry areas. |
Group (outline) data | Select the rows or columns that you want to group, right-click, and then select
Group . After grouping, you can select a
group, right-click, and Ungroup a single group,
or select Ungroup All to remove associated
groupings. |
Define data validation rules | You can define rules that determine what data can go into a set of cells in your workbook.
Example: You want to make sure that users select a geographic region
from a list of valid regions, which your workbook stores in another
column.
|
Rename or copy a workbook sheet | To rename the sheet or to do other sheet actions, click the arrow on the sheet tab. Sheet
names can be up to 31 characters long. We recommend using names of 27
characters or less. When you copy a sheet, Worksheets uses 4 characters
to add a space and a numerical increment in parentheses to the sheet
name. Example: When you copy the sheet named My Sheet , Worksheets
names the copy My Sheet (2) . |
Open instance details in a new browser tab | When a workbook includes instance details, you can open the link to view them. To open
the instance page in a new browser tab, select the icon in the instance link
or use Ctrl+Click (Windows) or Command+Click (Mac). |
Collaborate |
|
Define a name | Select the cell or range, right-click, and then select Define
Name .Defined names must be unique per workbook, and can be between 4 and 255 characters
long. You can't edit a defined name. You must delete it and define a new one. |
Protect ranges | (Workbook owners only) To prevent other users from editing a cell or range, select it and then select . To protect a sheet, click the sheet menu (the down arrow on the sheet tab) and then select
Protect Sheet .When you recalculate or
refresh the live data in a workbook, Worksheets updates data even if
that data is in a protected range. |
Find content in a workbook sheet | To find values in a workbook sheet, select or press Ctrl+F (on Windows) or Cmd+F (on Mac) to open
the Find in Sheet panel. You can search for
values, but not formulas.When you search in a workbook, Worksheets displays results that contain
the characters you type, in the order you specify. The default search is
a wild card search, with an implied asterisk character (*) at the
beginning and end of the string you specify. For advanced searches, start your query statement with the ^ character. |
Break a line in a workbook cell | Double-click the cell in which you want to insert a line break. Click the
location inside the selected cell where you want to break the line.
Press Alt+Enter (Windows) or Option+Enter (Mac). If you use the workbook
formula editor, it removes line breaks, so you need to add the breaks
again. |
Delete workbook sheets | Click the sheet menu (the up arrow on the sheet tab) and then select
Delete .Worksheets can't undo deleting a sheet. |
Create pivot tables | Select the range to include in the pivot table and select . Pivot tables containing live data rely on the report column names from
the Workday report. If you change a report column name in the report,
and that data is used as a field in a pivot table, you need to replace
the pivot table field associated with the original name with the field
that's based on the new report column name. |
Refer to data in other workbooks | To add external (cross-workbook) references, you must have edit permission for the consumer
and view permission or higher for the producer workbook. A workbook that refers to (brings in) data from another
workbook is a consumer workbook. A workbook containing data
that's being referred to in another workbook is a producer
workbook. A workbook can be both a consumer and a producer.From a producer workbook, you can copy a reference, then paste it into a consumer
workbook:
From a consumer workbook, you can copy the Workday ID of a producer
workbook, then add the defined name, or sheet name and cell/range
reference manually:
Consumer workbooks display an external reference icon on the workbook toolbar. The icon
turns green if someone changed data in the producer workbook after you
opened the consumer workbook; click the icon to update the data. The
update that occurs as a result of the action is the same as Recalculate
All. After the producer workbook's data changes occur, there might be a
delay of up to 2 minutes before the consumer workbook icon becomes
green. |
Merge 2 workbooks | You can add the sheets from another workbook into the open workbook if you're the owner or
you have edit permission. Open the workbook that you want to contain all the content from both workbooks, then select . When merging workbooks, Worksheets doesn't:
|
View quick statistics on a selected range | When you select a range of cells in a workbook, such as defined names, columns, or sheets,
these formulas display to the right of the formula bar to provide quick
reference statistics about your selection:
Based on the types of data in the selected cells, Worksheets shows the relevant functions
in the drop-down list. For SUM, AVERAGE, MIN, and MAX, Worksheets
converts units in the same dimension (such as length) to determine the
result. If all selected cells contain non-numeric data, then Worksheets displays only the COUNTA
statistic. |
Create a workbook version | Workbook versions provide a checkpoint for a group of changes. Select , then type a version name in the Add Version
to Workbook dialog.Workbook comments are independent of any versioning status: the user sees in the
Comments panel all comments entered for the workbook, regardless of the
version where the comment was added. Restoring to a previous version of
the workbook doesn't undo or change the Comments panel data. |
Sorting, including advanced sorting of live data and contiguous
columns | Select the columns or rows to sort, then select and then select a sort order or select
Advanced Sort . 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 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 ). |
Paste content into a workbook | You can use Ctrl+C and Ctrl+V to copy and paste content as values from 1 Worksheets
workbook to another, or from a desktop spreadsheet application such as
Excel. You can't use the workbook menu option to paste desktop application content into a workbook;
Paste Special works only between workbooks that currently reside in
Worksheets. Import (upload and convert) the desktop spreadsheet into
Worksheets by selecting before pasting data from it that includes formulas into a
Worksheets workbook. You can copy and paste up to 9 MB of content from 1 workbook to another. |
Freeze spreadsheet cells | Locate the freeze handles in the top left corner of the sheet.  Drag a handle to freeze columns or rows. Alternatively, you can scroll until the desired column or row is in the first viewable
position in the workbook, then freeze at that position by selecting . Worksheets freezes at the location of the first visible
row in the spreadsheet, which might not be row 1. Similarly, selecting freezes at the position of the first visible column in
the workbook. To unfreeze all columns and rows, select . |
Navigate within a range of cells | If you select a range of cells and then press Tab to move from cell to cell, the
cursor stays inside the selected range. |
Insert a chart | Select the data to include in the chart, select , then select a destination cell for the chart. The chart
starts at the selected cell and displays in an area of merged cells
that's approximately 10 rows in height and 4 columns in width. If you include date information in the chart, make sure that you use standard date/time
formats; otherwise, Worksheets doesn't recognize the information as
dates. Keep in mind that if you use a pivot table as the source data for a chart, and later you
change the rows, columns, or values to include in the pivot, you need to
make sure your chart still displays correctly. |
Auto-fill a formula or value into all cells in a column | These steps provide a keyboard alternative to using drag-fill:
|
Change the font | Worksheets supports a variety of widely available fonts. The default workbook font is
Roboto. Worksheets doesn't support the Calibri font. |
Rebuild a corrupted workbook | If a problem, such as a system error or an interrupted process, causes a
workbook to be corrupted, you can return the workbook to a working state
using a keyboard shortcut:
|
Formulas
Task | Notes |
|---|---|
View reference information for all available formulas | Click the Function icon (fx) to open the Functions Library
panel. |
Enter a formula | Use one of these methods:
To treat the data in a cell as text instead of a formula, type a ' (single quote character)
before the = character. Worksheets treats everything after the ' as
plain text. The ' character doesn't display in the cell, but it displays
in the formula bar. |
Submit a formula from the formula bar or the cell containing the formula | If you expect the formula to return a single value (it's a scalar formula), press
Enter to run the formula. If you expect the formula to return multiple values (it's an unconstrained array formula),
use the Ctrl+Alt+Enter (Windows) or Command+Option+Enter (Mac) keyboard
shortcut. |
Submit a formula from the formula editor | Click Save & Close . When you open the formula editor for a formula, the editor detects if the formula is a
standard scalar (single value, nonarray) formula or unconstrained array
formula, and submits it appropriately. Worksheets doesn't support using
the formula editor for constrained array formulas. |
Enter numbers into a formula | You can enter numbers in several formats, such as:
The cell might not display the number exactly as you typed it, depending on the current
cell formatting rules and other factors, but Worksheets preserves the
value. To make Worksheets handle the format of the cell as text, type a ' (single quote character)
before the = character; Worksheets treats everything after the ' as
plain text. The ' character doesn't display in the cell but it displays
in the formula bar. Example: You can enter dates and preserve the
formatting you typed, instead of displaying it in a format such as
DD/MM/YYYY. |
Circular References
In almost all cases, a circular reference indicates an error that you must correct.
We strongly recommend not enabling iterative calculations, particularly in workbooks
that contain live data; doing so obscures data errors that can lead to poor
performance and unexpected behavior.
A red icon indicates that Worksheets didn't finish calculating; the workbook is in a state
where some formulas didn't fully run and are showing stale values. You can hover the
cursor over the icon to see a tooltip with information about the problem. There are
two common causes for a workbook to prematurely stop calculating: either the
calculation limit was reached, or one or more circular references exist in the
workbook. If the problem is caused by a circular reference, you can click the red
icon to navigate to the cell location of the reference. If you have more than one
circular reference, Worksheets navigates to the location of the first one.
In rare situations, you might want to enable circular references. Example: You have a basic
cost of $1,000,000 for a project. A consultant earns a 5 percent fee based on the
total project cost, which is $1,000,000 plus the consultant fee. Because the fee is
part of the calculation, it's a circular reference in the spreadsheet, so you need
to enable iterative calculation to occur.
A | B | C |
|---|---|---|
Basic Cost | $1,000,000.00 | |
Consultant Fee | =B3*C2 | 5% |
Total Cost | =SUM(B1:B2) |
To enable the calculation of circular references in a workbook, select , select
Enable Iterative Calculation
, and add
values for:- Maximum iterations: 100 or fewer.
- Maximum change: Enter a maximum change value. The default is 0.001. Worksheets does the circular calculation for the number of iterations that you specified, or until the result changes by less than this value.
If you open a workbook containing circular references, and your recalculation setting is
Manual
, a message displays; select to update the data.