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

 

Thanks everyone for the help

BI & Data Analysis
excel
training
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 02, 2023

    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)

     

    (Custom cell formatting is hiding the 0s)

  • 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