A pretty useful trick in Excel is being able to transpose data. I wrote a quick guide below on how to use the transpose function in Excel.

If you’re unfamiliar with this feature, Excel allows users to take information that is in a row, copy it, and paste that data into a column. This can also be done the other way around i.e. from column to row.

Here is a quick step-by-step on how to do this:

• Select the data you want to transpose
• Copy it (CTRL + C)
• Go to the cell where you want to past the information and transpose it.
• Use the following short-cut key combination ALT + E + S + V. The will bring up the following screen:

• Using the TAB key cycle through the options until you get to “Transpose” and press the Spacebar to select it.
• TAB one more time to “OK” and press Enter.

This is a useful workflow to have in you Excel toolbox. It saves a lot of time, especially if you use the short-cut keys.

Transpose: Advanced Technique

While working through a new financial modelling course I came across another useful technique which uses the transpose function. On a side note, that is the one benefit of doing new courses. Each course always has some new approach, short-cut key or different way of thinking that you can learn from.

In this particular example, the presenter shows you how to transpose information but keep it linked to the original source. That way, if an update is made to the assumptions, then the information only needs to be changed in one place and it will automatically flow through the financial model.

That context for this technique was modelling a depreciation schedule. The capex assumptions were provided for each year. In the diagram below you can see the capex assumptions for years 2020 to 2023. Note that these are in blue implying that they are hard-coded numbers (a best practice in financial modelling).

Below the capex assumptions is the depreciation schedule calculation. The aim of this tutorial is to transpose the capex assumptions per year into the section within the red box. In order to do this we have the following options:

• Transpose the information using the technique described at the beginning of this post. The downside to this is the capex assumptions in the depreciation schedule will be hard-coded i.e. they are not linked to the original assumptions. If the original assumptions change we will need to update the depreciation schedule manually.
• An alternative is to link each cell with the corresponding capex assumption. The benefit of this is that the capex assumptions in the depreciation schedule will update automatically if there is a change to the original assumptions. The downside of this approach is that it is manual and slow.
• The final option, which we will demonstrate below, is to use the “transpose” function.

Excel Transpose Function

In this technique we transpose the information and keep it linked to the original source. This ensures that any adjustments to the original capex assumption will update our depreciation schedule automatically and will flow through the financial model.

This is the step-by-step guide on how to do this:

• The first step is to highlight the cells that you want to transpose the data to. In the image above you would highlight all the cells in red box.
• Next type in the following formula =TRANSPOSE(
• Then select all the cells with the capex assumptions. In this example this is the information 44,000 to 47,000.
• Close the bracket in the TRANSPOSE function and then press the following: CTRL + SHIFT + ENTER
• The original capex assumptions will be transposed into the depreciation schedule and linked to the original source.

The final outcome will look something like this:

You will notice in the formula bar that the 44,000 is linked to the original capex assumptions using the transpose function. If I changed any of the original capex numbers they would automatically update in the depreciation schedule below.

This is a useful Excel tip that I will most certainly be incorporating into future financial models.