ROT_90 Function in Excel

=ROT_90(array, [n])

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

Download the Template

In the template file, navigate to the Arrays worksheet to see the ROT_90 function in action.

Description

Rotating a matrix is different than using TRANSPOSE (which mirrors across the diagonal). The ROT_90 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.

ROT_90(array,1) = FLIPUD(TRANSPOSE(array))

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

ROT_90 Function Example

Lambda Formula

This code for using ROT_90 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, ROT_90 provides an example of how you can define a LAMBDA function within another LAMBDA function, using LET. Instead of requiring the ROT_90 function to rely on the FLIP function, the code for ROT_90 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: ROT_90
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
*/
ROT_90 = 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: ROT_90
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.

ROT_90 Examples

Example

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

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

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

Revision History

12/11/2024: Changed name from L_ROT90 to ROT_90 (cannot use ROT90 because that is a cell address)

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.

ROT_90