Excel Function for Private Equity: TRANSPOSE

One of the most important aspects of working with large data sets is organization. The way you organize the data is the way that others will interpret it. With the help of the Transpose function in Excel, you can rearrange complicated data in a matter of clicks.

What is the Transpose Function?

The Transpose function in Excel is a function that transposes or moves data from one location in the sheet to another. The main benefit of this function is that it allows you to quickly change data that is organized in columns into data organized in rows and vice versa. The Transpose Excel function is unique in that it is only used in array formulas An array formula is a formula that gets applied to more than one cell.

=TRANSPOSE(array)

Array: array is the range of values that you wish to transpose into another location. When selecting the values you want to highlight all the values you wish to transpose, even headers! The first row of the array will become the first column of the transposition and vice versa. Array is the only parameter of the transpose function and is required.

Transpose vs. Copy and Paste

Copy and paste is the traditional method of transferring data from one place to another. To use copy and paste as a way to transpose data in Excel, simply select the data and choose “Copy and Paste Special” and then click “Transpose”. However, this method is not advisable when working with large sets of data for one major reason. The copy and paste method creates duplicate data that is not updated when the original data is updated. This duplicate data could get jumbled with your original data and confused anyone working with the spreadsheet. With the Transpose function, the transposed data will automatically be updated with the original.

Example:

When operating the Transpose function in Excel, there are two methods of executing the command. The first method is the simplest, and typically will be the most applicable to large sets of data.

In the first method, we only have to select the array you would like to transpose. Above we have a series of regions with monthly revenues related to each region. The data is organized so that we have months as column headers and regions as row headers, but what if we wanted to flip that? Reorganizing all this data by hand would be time-consuming, so we can use the transpose function to alter this array. First, select the first cell where you want your transposed array to begin and begin the type the function. Next, select the range of cells where your original array is located. In this example it is B2:N13.

Finally, hit enter and you will see your large data set transposed into a new location and organization.

Second Method:

The second method differs from the first in two ways, and allows you to choose where you would like your data. To begin, first select the range of cells you wish to transpose your data to. If you want to transpose all of your data, you have to be sure that you select the same amount of cells that your data is in. However, this method can be useful if you only want to transpose some of your data.

First, we highlight half of the amount of cells our original data is in and began to type our function. Next, we select the entire array that we would like to transpose. Finally, we hit control + shift + enter (instead of just hitting enter in the first method). This ensures that the array being transposed is only transposed into the selected cells.

Transpose in Real Estate

The Transpose function in Excel can help save time and effort when reorganizing large sets of data, especially when pulling from databases or other spreadsheets. For instance, if you are pulling financial statements from multiple years, or multiple companies, it can be helpful to use transpose to reorganize the data for different analysis. At Top Shelf Models, we use the Transpose function to manage the reorganization of large data sets.

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions regarding discounted cashflows or if he can help you with your modeling needs.