<-- 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:
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.