Concept: Data Analysis with Worksheets Functions

Workday User Guide

Data analysis is an iterative process of collecting, cleaning, and shaping your data, followed by aggregation and analysis. Worksheets helps to make your workflow more efficient by enabling you to work with live Workday data, and to use Worksheets-unique functions that improve the analysis workflow.

As you do each of these steps, remember to keep your original data as a backup by creating new workbooks for the manipulated data.

Collect

Compile your data, gathering it from resources both outside and within Workday. Make sure that your raw data is complete. Here's a summary of actions that you might do during data collection:

Select

+New

Upload

from Drive to create workbooks from data that exists outside Workday.

Select

Data

Add Live Data

to add live data from Workday reports into a workbook. The Data Wizard helps you identify the subset of data that you want to insert to the workbook.

Keep your data current by creating a schedule to refresh the live data.

Use the ARRAYAREA function to copy data from one unconstrained array to another, creating an organized set of raw data to manipulate in the Clean step. ARRAYAREA returns the containing range of the array formula, based on the cell address you specify. The array can originate either in a Workday report or an array formula.

Clean

Reduce your data to only the information you need by removing duplication, trimming empty workbook values, and more.

Keep these Worksheets-unique functions in mind:

Function	Notes
DISTINCTROWS	Combines a set of ranges into a single range while removing any rows that are duplicates. DISTINCTROWS evaluates text and instance values as not distinct from each other. When the supplied range contains both an instance value and a text string that are the same, the function returns 1 row, the instance value.
REMOVECOLUMNS	Removes one or more columns from the referenced area. The function removes the number_of_columns, starting at and including start_column.
REMOVEROWS	Removes one or more rows from the referenced area. The function removes the number_of_rows following start_row , starting at and including start_row . Use REMOVEROWS without specifying any rows in order to remove the first (heading) row.
TRIMCOLUMNS TRIMROWS	Removes trailing blank columns and rows from a range, when the data is a result of an unconstrained array formula.
TRUNCATEMATRIX	Removes rows, columns, or both, from a matrix.
UNIQUE	Returns a matrix whose rows are unique according to the specified keys. The function returns only unique rows, based on the values in the specified columns. This function is similar to DISTINCTROWS(), but UNIQUE() takes a single range and a set of columns.

Shape

Arrange, convert, and organize your data to create consistency:

Standardize your columns and create new ones if needed.

Give each column a unique and descriptive header.

Format each column consistently.

Double-check for duplicate or missing rows.

Make sure the wording and formatting for text data is consistent.

Make sure that no cells are blank.

Convert data values to the same unit where needed.

When shaping data, there are 2 main types of data manipulation:

Value-specific: The action you want to take depends on the value in the cell.

Data arrangement: The manipulation is value-agnostic and you're moving cells, columns, and rows to different locations.

For value-specific manipulation, these Worksheets-unique functions can be helpful:

Function	Notes
CONVERT	Converts a number from 1 unit of measurement to another.
DATESBETWEEN	Returns an array of dates that starts and ends on a particular date, with a step interval between each date.
DATESFROM	Returns an array of dates that starts on a particular date and continues for the number of dates you specify, with a step interval between each date.
IN	Determines whether a value or list of values that you specify is in another list of values. If so, returns True; otherwise, returns False.
MATCHCOMPOSITE	A common use case for MATCHCOMPOSITE is to consolidate column data from 2 sheets into 1, where you have data on a sheet, along with notes about that same data in a column on a different sheet. MATCHCOMPOSITE copies values in one or more columns from the location to the right of a source array, and returns values to the right of a destination array. You use a composite key of columns to match copied data to the correct rows in the destination.
MATCHEXACT	Looks up an exact match for the value in the sorted list (one-dimensional array) you specify, and returns the position of the value. You can use this function to match logical values, numeric values, or text strings. This function is similar to MATCH, but MATCHEXACT: Always searches for an exact match; it returns an #N/A error if it doesn't find the value. Doesn't allow wildcard characters such as * or ?. Uses a binary search for better performance.
MHLOOKUP	We recommend this function as a replacement for HLOOKUP. MHLOOKUP performs a horizontal (row) lookup on a table and returns all matches. MHLOOKUP is similar to HLOOKUP, but: HLOOKUP scans only the top row for matches; in MHLOOKUP you specify the row to search. HLOOKUP stops after finding 1 match, and returns a single cell value; MHLOOKUP scans the entire lookup row for matches. Each match in that row returns a new column in the output. If you specify 4 return_row_index values, then each resulting column will have 4 rows.
MVLOOKUP	We recommend this function as a replacement for VLOOKUP, especially when you're working with live data. MVLOOKUP performs a vertical (column) lookup on a table and returns all matches. MVLOOKUP is similar to VLOOKUP, but: VLOOKUP scans only the left column for matches; in MVLOOKUP you specify the column to search. VLOOKUP stops after finding 1 match, and returns a single cell value; MVLOOKUP scans the entire lookup column for matches. Each match in that column returns a new row in the output. If you specify 4 return_column_index values, then each resulting row will have 4 columns.
REGEXFIND	Returns the position of the first character of a substring that matches the regular expression pattern. The position value is zero-based.
REGEXPARSE	Extracts parts of a string by matching to a pattern.
SETUNITS	Converts a number from its current unit of measurement to another. This function is similar to CONVERT(), but in CONVERT() you must specify both the original and the new unit values.
SELECT	Valuable for value-based manipulation as well as data arrangement. The SELECT function is similar to an SQL SELECT statement. The basic format is: SELECT column1, [column2], ... FROM table where column is the data to return and table is the data source to select from. In the FROM clause you can specify, for example: Defined name. Column, row, or area range. Parameter that you specify as an argument.

For data arrangement, these Worksheets-unique functions can be helpful:

Function	Notes
WD.ARRANGECOLUMNS WD.ARRANGEROWS	Creates a new range from an existing range, with the columns or rows ordered according to the specified indexes. With WD.ARRANGECOLUMNS you can add an empty column by including a null index value. Example: =WD.ARRANGECOLUMNS([range],1,2,3,,4) inserts a blank column between the 3rd index value and the 4th index value.
CORRELATE	Creates a new matrix by combining rows from the ranges you specify. This function is similar to a database join.
FLATTEN	Returns an expanded range of data based on the hierarchical data that you specify. Typically you use this function to expand an organization's manager and employee information so that it displays all levels of the hierarchy.
JOIN	Performs an inner left join on 2 ranges.
MERGECOLUMNS	Merges columns by placing them side by side into a new range.
MERGEROWS	Merges rows by placing them 1 below the other into a new range.
MINUS	Returns all rows from a first range that don't appear in any of the other supplied ranges.
SORT SORT2 SORT3	Sorts an existing matrix and returns a new matrix. SORT accepts 1 sort direction and sorts all columns you specified based on that direction. SORT2 accepts pairs of parameters, which you use to specify the referenced column and the sort direction for that column. SORT3 assumes that the first row of the array to be sorted is a header and returns that row at the top of the results.
VALUEAT	Returns the value at the intersection of a column header and row label.
SELECT	Valuable for value-based manipulation as well as data arrangement. The SELECT function is similar to an SQL SELECT statement. The basic format is: SELECT column1, [column2], ... FROM table where column is the data to return and table is the data source to select from. In the FROM clause you can specify, for example: Defined name. Column, row, or area range. Parameter that you specify as an argument.

Analyze

Now you're ready to aggregate and analyze the data you've prepared.

The most commonly used analysis tool is the pivot table, which enables you to summarize and analyze large amounts of data. The Pivot Table Wizard and details panel enable you to create and edit pivot tables interactively. You can also create charts to visually demonstrate data relationships.

Keep these Worksheets-unique data analysis functions in mind:

Function	Notes
CAPPEDVALUES	Typically used for 401(k) deductions, ESPP deductions, or tax payments that have a regular value per period, but drop to zero (0) when the payment reaches the cap. Returns an array of values over a set of periods from an array of values that you provide, over the same periods, limited by a provided cap over the whole duration.
FORECAST.WD.SEASONAL	Returns a predicted sequence of values using patterns in the historical linear and nonlinear data that you specify.
GROUPBY	GROUPBY is a powerful function that can often replace COUNTIF(S), AVERAGEIF(S), and SUMIF(S). GROUPBY aggregates data, and orders the results based on the order that you specify. The grouping is based on a key that you can predefine in the table, or you can define it using columns in the workbook. The result looks similar to a sorted pivot table. This function is often useful as part of headcount planning.
SELECT	The SELECT function is similar to an SQL SELECT statement. The basic format is: SELECT column1, [column2], ... FROM table where column is the data to return and table is the data source to select from. In the FROM clause you can specify, for example: Defined name. Column, row, or area range. Parameter that you specify as an argument.

Other Notable Worksheets-Unique Functions

These functions aren't specific to data analysis but are very useful in all steps:

Function	Notes
NOTIFYIF NOTIFYIFS	Sends notifications if a condition is met. You can send a notification to a user whether or not they have access to the workbook.
ONCE	Calculates a formula exactly 1 time. Worksheets never re-evaluates the formula even if you request re-calculation using Data Recalculate ; however, you can manually resubmit the formula. Example: Use this function when the volatile function NOW() places a timestamp in a workbook, and this timestamp must never change.