<-- Return to Excel Statistics Guide

Excel Statistics Functions Guide

A Tool for Students

Keith Greiner Ed.D.

Updated August 8, 2020

The =Transpose_Active_Range(…) Function

The Transpose_Active_Range(…) function may be used to transpose a range of cells and have the transposed range active. That is, the transposed range changes each time the source range is updated.

Template:

= Transpose_Active_Range(Source_Range)

The Excel spreadsheet program includes a copy and paste tool and that includes an option to paste as a transposed array. Unfortunately, the pasted transposed array looses all connection to the source array. The spreadsheet program also includes an =TRANSPOSE(Range) array function that does provide recalculated updates, but does not allow customization of the transposed array. The function described here solves both of those problems in an Excel VBA function. To use it, you will need to ensure that your Excel program can use VBA. Then save the file as an .xlsm file. Enter the code into a VBA module and then enter it into a spreadsheet.

This function operates as an array function, which means that it returns an array. See the section of this guide that describes array functions. The basic steps are as follows:

1. Highlight the range that will receive the output ie: range G6:N9. I often provide a range that is larger than needed
2. Keep the range highlighted
3. Click on the formula editing area next to the fx text above the spreadsheet
4. Type in the code that calls the function to be used: ie: =Transpose_Active_Ranges(C3:E10)
5. Press Shift+Control+Enter
6. If everything is correct you should see the results shown in the cells you highlighted in step 1. Remember that if you do not specify enough cells in step 1, then only the cells you specify will have results in them. That's why I often specify a range that is larger than needed. Un-needed cells will be filled with an #N/A error, and that is ok.

The example in Excel Image 1. transposes the tan range, C3:E10 into the green range, G6:N9. If a change is made in the range C3:E10, it will be reflected in the range G6:N9.

The entry into cell G6 is =Transpose_Active_Range(C4:E11). After making that entry, follow the steps shown above and in the instructions that are elsewhere in this Excel Statistics Guide

Now that you can see the VBA code that accomplishes the transposition, you can also modify the transposition as needed to customize your calculation.

Following is the VBA code for the Transpose_Active_Range(Range1) function.

<-- Return to Excel Statistics Guide