Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

Say I have a data series going from A1 to E1. I would like to make a second copy of these cells going vertically, for instance from G1-G5. I can achieve what I want just by typing =A1 into G1, =B1 into G2 and so on. However, I have in mind a situation in which this needs to be done with a larger data series. I'm keen to economize on keystrokes if I can.

Is there a way to use Fill or some other command to achieve this?

share|improve this question

2 Answers

up vote 1 down vote accepted

You can use a formula, e.g. in G1 copied down to G5

=INDEX(A$1:E$1,ROWS(G$1:G1))

share|improve this answer
1  
This is my preferred technique. But as with all formulas like this, be aware that CUTTING rows or columns within the noted ranges may affect the remaining results, watch for that and reenter the formulas to reset them if you do much of that. – Jerry Beaucaire Feb 27 '12 at 14:43
Thanks for the advice about cutting rows/columns. – SwanWhisperer Feb 28 '12 at 12:12
up vote 1 down vote

Paste Special-Transpose should work for you.

Here is an example of what this does (which is exactly what you are looking for)

Edit

I think you are looking for this trick -

  1. Select G1-G5.
  2. In the formula bar (you know the one that shows you the formula instead of the value), type in =Transpose(A1:E1) and use ctrl+shift+enter to complete your edit.

Cheers.

share|improve this answer
Thanks. I thought of that, but when I paste special transpose (even if I choose the 'Formulas' option) I get the value from A1 in G1, the value of B2 in G2, and so on. What I really want is for the formula =A1 to be in G1. This is because later on the value in A1 will change. – SwanWhisperer Feb 27 '12 at 9:01
1  
I edited my answer. let me know if that works for you. – Ben Jones Feb 28 '12 at 0:37
Yes, it works. Thank you! – SwanWhisperer Feb 28 '12 at 12:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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