Array Formula

2 posts

Live Transpose in Excel With an Array Formula

Excel’s transpose feature is great for changing data in a vertical format to a horizontal format, and from a horizontal format to a vertical format. Excel’s transpose, though, creates a static copy of the original data. If the original data changes, the transposed copy does not change.

This may be your desired result, but if you want the transposed copy of the data to update at the same time as the original data changes, there is a way create a transposed copy that changes along with the original data. This will create a “live” transposed copy.

The way to do this is by using an array formula. Check here if you want more information on array formulas. As a quick note, you do need to use CTRL + SHIFT + ENTER when entering an array formula for it to work.

Live Transpose Example

To illustrate how a live transpose in Excel works, take a look at the sample image below. It shows the original table of data that will be the source for the live transpose copy.

excel live transpose source data

Once you decide where you want the live transposed copy of the original data to be placed, you just need to highlight a range of cells that is equal to the transposed version of the original data. Note that it is the opposite size of the original data, since it is a transposed copy.

Therefore, In this example, since the original data is 14 columns wide by 9 rows high,when the data is transposed, its shape will change to be 9 columns wide by 14 columns high.

So you need to select a range that is 9 columns wide by 14 columns high.

highlight a range for live tranpose

Once you have highlighted the range where you want the live transposed copy of the data to be located, start to type the TRANSPOSE formula. You can either click in the formula bar first, or just start typing the TRANSPOSE formula. In the example here, it would be:

=TRANSPOSE(A1:N9)

And when you type CTRL + SHIFT + ENTER after typing it, it turns into this (NOTE the curly braces that were automatically added):

{=TRANSPOSE(A1:N9)}

And this is the resulting view:

a live transpose in Excel

Anywhere you click in the range, the formula in the formula bar does not change. It stays the same.

Now, if you change any values in the original data, the transposed version will automatically update to the new value you have entered.

Summary

Hopefully, this post has shown you how to use an array formula version of the TRANSPOSE function in Excel to create a “live” transposed version of the original data.

Let me know what you think, or if you have any tips or comments about this method.

How to Use the Excel COLUMN Function

The Excel COLUMN function returns the referenced column as a number. Here’s a quick explanation of how to use the function.

COLUMN Function

COLUMN([reference])

  • reference = The cell or range of cells that you want to have the column numbered returned from. This is optional.

Here are a few notes about the function:

  1. The reference can’t refer to multiple ranges
  2. If no range is entered in the function, it assumes that you want to know the column of the cell that has the function
  3. If you use an array in the reference (by selecting the cell with the COLUMN function, pressing F2, and then CTRL + SHIFT + ENTER), the function will return the column number as a horizontal array
  4. If the reference is a range of more than one cell, and a horizontal array formula is not used, the function will return the left-most column number

Examples

Here you will see some examples of the COLUMN function including using it as a horizontal array formula. The image below shows the results of using the function.

column function
Result View of COLUMN Function

Now, here is the same spreadsheet showing the formulas:

column function
Formula View of COLUMN Function

Notice that the three formulas are all different, yet they return the same result.

=COLUMN() returns 1, since the function is located in column 1

=COLUMN(A1) also returns 1, since it is referring to cell A1, which is in column 1

=COLUMN(A1:B1) also returns 1 even though the range is over two columns. This is because it always returns the left-most column.

Now, you will see the difference by using horizontal array formulas.

In the image below, the results of the COLUMN function is shown. There are three more formulas added to help show the comparison.

column function
Result View of COLUMN Functions Including Horizontal Array Formulas

Here is the formula view of the above spreadsheet. Note that array formulas will not show the curly brace brackets in the cells, but only in the formula bar. As a result, the formulas in cells A4, B4, A5 and B5 all look identical. However, the formulas in A5 and B5 are both entered as horizontal array formulas.

column function
Formula View of COLUMN Functions Including Horizontal Array Formulas

The image below shows how the formula in cell B5 appears in the formula bar with the curly braces to indicate it is a horizontal array formula.

column function
Horizontal Array Formula