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 2 down vote favorite

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ...

=ARRAYFORMULA(ROW(A3:A6))

... into cell C1 creates ...

   C
1  3
2  4
3  5
4  6

The contents of cells C2 through C4 are the formula "CONTINUE."

In Excel, entering {=ROW(A3:A6)} [CTRL] [SHIFT] [ENTER] creates ...

   C
1  3
2  
3  
4

Is there a way to get Excel to populate the remaining rows?

The desired end result is a formula which given ...

   A
1  Fred
2  Wilma
3  Barney
4  Betty

... will generate ...

   B
1  Fred Wilma
2  Barney Betty
3  
4

... without the user having to copy-paste.

share|improve this question
Did you use shift-enter when entering the formula in excel? – Lance Roberts Nov 1 '10 at 22:39
@Lance Roberts, I used ctrl-shift-enter after entering the formula. Does shift-enter do something different? – Thomas L Holaday Nov 2 '10 at 5:19
1  
nah, I think I just forgot the ctrl. – Lance Roberts Nov 2 '10 at 5:48

1 Answer

up vote 3 down vote accepted

All you have to do is exactly what you did, except before you enter the formula in excel, select the entire range, A1 through A4. Then type the formula in and hit [ctrl][shift][enter].

share|improve this answer

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.