I need to display the sums of rows vertically using ctr+drag for over 80 rows downwards in one column to replicate the formula for the remaining 80 rows, but excel only continues to modify the formala if I drag horizontally.

Here is a small table to demonstrate what I am trying to do.

In cell A12 is the sum of rows A1:F1. I want to be able to display the sums of A2:F2 in cell A13, and A3:F3 in cell A14.


At the moment I can only copy as value, and then transpose. But I don´t want to do this 80 times weekly when I receive this table.

Which formula would enable me to quickly calculate the sums of rows but display them vertically?

  • You can compute in columns, and transpose? – hjpotter92 Mar 13 '18 at 14:58
  • It's not clear to me what you're trying to do. Could you show what your result is supposed to be? – Bandersnatch Mar 13 '18 at 15:02

What you want is to enter an array formula for transposition. For this, select the destination column, e.g. A14:A:19, enter the =TRANSPOSE(A12:F12) command from the original row, and press CTRLSHIFTENTER.

What you want to achieve is thus easy in two steps: first sum into a new row A12:F12, then tranpose to a column.


You can use this formula in A12 to get the result:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.