Copying cells on a diagonal refers to extracting values that lie along a diagonal line within a matrix or table in Excel. Since Excel does not provide a direct method to select diagonal cells, you can use functions like INDEX combined with ROWS to access and copy diagonal data into a single column for easier analysis or formatting.
Steps to Copy Cells on a Diagonal
Assume you have a matrix with 8 rows and 8 columns (e.g., cells B3:110), and you want to copy the diagonal cells (B3, C4, D5, E6, F7, G8, H9, I10) into a single column.
Step 1: Identify Diagonal Cells
Given a matrix that has 8 rows and 8 columns. 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: Use INDEX Function
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: Copy to a Single Column
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.
