ROT90 Function in Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_ROT90(array, [n])

Argument	Description	Example
array	An array or range of numbers or text	2
n	Number of times to rotate	1

Description

Rotating a matrix is different than using TRANSPOSE (which mirrors across the diagonal). The ROT90 function behaves like rotating a visual 2D image 90 degrees counterclockwise. The function may be needed in certain matrix transformations or for manipulating data.

The function allows you to specify the number of times to rotate, but it must be an integer. This function does not do general angular rotations. Rotating 90 degrees once is the same as reversing the order of the rows after using TRANSPOSE.

L_ROT90(array,1) = L_FLIPUD(TRANSPOSE(array))

The image below shows an example where L_ROT90 is used multiple times. Note that rotating 4 times returns the array to the same state, as you would expect.

ROT90 Function Example

Lambda Formula

This code for using L_ROT90 in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Note

For those interested in creating LAMBDA functions, L_ROT90 provides an example of how you can define a LAMBDA function within another LAMBDA function, using LET. Instead of requiring the L_ROT90 function to rely on the L_FLIP function, the code for L_ROT90 defines r9Flip as an internal function.

Normally, you would not want to redefine a function in multiple places. However, the r9Flip function is fairly simplistic, and this use case allowed us to show an example of defining a function within a function.

Code to Create Function via the Name Manager

Name: L_ROT90
Comment: Rotate an array 90 degrees counterclockwise n times.
Refers To:

=LAMBDA(array,[n],
LET(doc,"https://www.vertex42.com/lambda/rot90.html",
    r9flip,LAMBDA(arr,dim,
        IF(dim=2,
            CHOOSECOLS(arr,SEQUENCE(1,COLUMNS(arr),COLUMNS(arr),-1)),
            CHOOSEROWS(arr,SEQUENCE(ROWS(arr),1,ROWS(arr),-1))
        )
    ),
    IF(ISOMITTED(n),
        r9flip(TRANSPOSE(array),1),
        CHOOSE(1+MOD(INT(n)-1,4),
            r9flip(TRANSPOSE(array),1),
            r9flip(r9flip(array,2),1),
            r9flip(TRANSPOSE(array),2),
            array
        )
    )
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Rotate an array 90 degrees counterclockwise n times
*/
L_ROT90 = LAMBDA(array,[n],
LET(doc,"https://www.vertex42.com/lambda/rot90.html",
    r9flip,LAMBDA(arr,dim,
        IF(dim=2,
            CHOOSECOLS(arr,SEQUENCE(1,COLUMNS(arr),COLUMNS(arr),-1)),
            CHOOSEROWS(arr,SEQUENCE(ROWS(arr),1,ROWS(arr),-1))
        )
    ),
    IF(ISOMITTED(n),
        r9flip(TRANSPOSE(array),1),
        CHOOSE(1+MOD(INT(n)-1,4),
            r9flip(TRANSPOSE(array),1),
            r9flip(r9flip(array,2),1),
            r9flip(TRANSPOSE(array),2),
            array
        )
    )
));

Named Function for Google Sheets

Name: L_ROT90
Description: Rotate an array 90 degrees counterclockwise n times.
Arguments: array, n (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/rot90.html",
    r9flip,LAMBDA(arr,dim,
        IF(dim=2,
            CHOOSECOLS(arr,SEQUENCE(1,COLUMNS(arr),COLUMNS(arr),-1)),
            CHOOSEROWS(arr,SEQUENCE(ROWS(arr),1,ROWS(arr),-1))
        )
    ),
    IF(ISBLANK(n),
        r9flip(TRANSPOSE(array),1),
        CHOOSE(1+MOD(INT(n)-1,4),
            r9flip(TRANSPOSE(array),1),
            r9flip(r9flip(array,2),1),
            r9flip(TRANSPOSE(array),2),
            array
        )
    )
)

Warning

These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

L_ROT90 Examples

Example

Rotate an array counterclockwise by 90 degrees. This is different than TRANSPOSE, though it can be achieved through a combination of L_FLIP and TRANSPOSE: =L_FLIP(TRANSPOSE(array),1)

Test: Copy and Paste this LET function into a cell
=LET(
    array, {"A1","B1";"A2","B2"},
    n, 1,
    L_ROT90(array,n)
)

Result: {"B1","B2";"A1","A2"}

References & Resources

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.