Select cells diagonally

Copper Contributor

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 and enter a value on all of them at the same time.

 

Thanks everyone for the help

2 Replies

@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

@Ida_Spadafora 

I don't know what the sheet looks like, but I'll put this out there as an option (and because MUNIT is rarely mentioned).

 

=MUNIT(20)

 

Patrick2788_0-1675347023932.png

(Custom cell formatting is hiding the 0s)