Forum Discussion

Ida_Spadafora's avatar
Ida_Spadafora
Copper Contributor
Feb 02, 2023

Select cells diagonally

If I have a table with 20 rows and 20 columns, how can I insert the value '1' across the entire diagonal of the table quickly? I would like to know if there is a way to select the cells diagonally a...
BI & Data Analysis
excel
training
JosWoolley's avatar
JosWoolley
Iron Contributor
Feb 02, 2023

Ida_Spadafora

Not sure about 'quickly', though you could try the following workaround:


Enter this formula into a random cell somewhere:

 

=LET(
    ζ, MyRange,
    ξ, SEQUENCE(ROWS(ζ)),
    TEXTJOIN(",",,INDEX(ADDRESS(ROW(ζ),COLUMN(ζ),4),ξ,ξ))
)

 

Amend MyRange as required.

 

Now copy the cell containing this formula, open the Go To menu (F5) and paste into the Reference box. This will highlight the required diagonal, after which you can type a 1 followed by pressing CTRL+ENTER.

 

If your table is very large then limitations on the number of characters passed to the TEXTJOIN function mean that the above will not work for you.

 

Regards

Resources