Spread

Function spreads a key-value pair of variables across multiple columns.

Properties

Keywords

Rows to Columns, Pivot Excel Table, Key-Value Pair to Several Columns, Pivot Table, Data Pivoting, Long Form Table to Wide

See Also

Transpose, Gather, Flip_Vertical, Flip_Horizontal

Video-tutorial

How to Pivot Excel Data

Pivoting data - rows transformation on columns is often used in both visual applications and in "data-wrangling" languages. This phenomenon also reflected the Reshape.XL add-in via the Spread function. Its character is represented in the following figure.

Spread function allows you to select from the input table two variables - Key and Value pair that will shape the new columns. The Key variable will be used as the names of the new columns (Month variable) and Value defines the individual values. In the following example, we use a simple input table that contains the Product variable and the two integer variables - Month and Sales.

To pivot these data, click the Spread button on the ribbon toolbar tab Reshape.

When you click on the added function in the Properties Panel, two configurable properties are displayed in the form of combo-boxes - Key andValue.

An example of their setting is in the following figure. In this case, we have set the Month variable as Key property and the Sales variable as Value property. As a result, Month values were used as names for new columns and Sales values were used as individual values.

Another example shows an example of how we used the Product variable as Key and Sales variable is defined as Value property. The resulting table looks fundamentally different.

Spread function is of great importance. It allows you to transform the spreadsheet data into a more readable form - Wide Format. The opposite functionality - transforming columns into rows - has the Gather function.