Excel is a powerful tool but has some limitations. One of the limitations of Excel is that we cannot copy cells, on a diagonal, or we could say there is no conventional way to copy cells on a diagonal of a matrix in Excel using drag and copy, but this could be made possible using Excel functions. In this article, we will learn how to copy cells on a diagonal.
Steps to Copy Cells on a Diagonal
Step 1: Given a matrix, which has 8 rows and 8 columns in it. Now, our task is to copy diagonal cells i.e. B3, C4, D5, and E6. …. The diagonal cells are highlighted with yellow color.
Step 2: As, there is no conventional way to select the cell, and then, copy it. We can use the =INDEX() function to access a particular cell and then can drag the same formula to all the required cells. We have also used the ROWS() function, to get access to a particular row.
Syntax:
=INDEX(starting cell of row: ending cell of row, ROWS($row_number:row_number))
For example, in cell K3, we have written the formula =INDEX(B3:I3, ROWS($3:3)), which gives the value of cell B3.
Step 3: Drag the cell till the end of the matrix and all your diagonal data will be copied into one single column. For example, drag the cell from K3 to K10, as there are 8 rows in the table.