# Continuing the pattern of a formula to adjacent cells

This is a discussion on Continuing the pattern of a formula to adjacent cells within the Excel Questions forums, part of the Question Forums category; Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be ...

1. ## Continuing the pattern of a formula to adjacent cells

Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be copied to cells C5 to C15. I used Excell 2 years ago (could have been excel 2000 or 2003). Now 2 years ago, all I had to do was take cell C5 and put in the formula box "=A1". Then for cell C6, I would put "=A2" and for cell C7, "=A3". Then, i could select cells C5 to C7 and fill (I think that's the word. I mean pressing on the little black square on the bottom right corner and extending it to other cells) the cells up to C15. Excel would figure out the pattern of the formula in the first 3 cells and extend it to the next cells. Now, I have excel 2007 and if I try this, it will not work. Now, when I use the little black square, it just copies the formula to the other cells, it doesn't continue the pattern of the formula.
Is this an option I can turn on somewere? I can't do anything without this function.

2. ## Re: Continuing the pattern of a formula to adjacent cells

Originally Posted by SilenTScopE
Ok so let's say I have numbers in cells A1 to A10. Now I want all those numbers to be copied to cells C5 to C15. I used Excell 2 years ago (could have been excel 2000 or 2003). Now 2 years ago, all I had to do was take cell C5 and put in the formula box "=A1". Then for cell C6, I would put "=A2" and for cell C7, "=A3". Then, i could select cells C5 to C7 and fill (I think that's the word. I mean pressing on the little black square on the bottom right corner and extending it to other cells) the cells up to C15. Excel would figure out the pattern of the formula in the first 3 cells and extend it to the next cells. Now, I have excel 2007 and if I try this, it will not work. Now, when I use the little black square, it just copies the formula to the other cells, it doesn't continue the pattern of the formula.
Is this an option I can turn on somewere? I can't do anything without this function.
Welcome to the MrExcel board!

I am not exactly sure what you are saying goes wrong, but this works exactly the same for me in Excel 2007 as in previous versions. Try just putting =A1 into cell C5 then click and drag the "Fill Handle" (the little black square you described).

If this doesn't work, please tell us what is in cells A1:A10, what formulas end up in C5:C14 after you perform the above process, and what results appear in C5:C14 after the process?

3. ## Re: Continuing the pattern of a formula to adjacent cells

Sorry, instead of C5 to C15, I meant from C5 to G15. So yes, it works if I do it from C5 to C15.
So let's say Cell A1 has a value of 2. A2=3, A3=4, A4=5, A5=6
Now in cell C5, I put the formula "=A1" and I get a value of 2. That's great.
Now I drag the "fill handle" of cell C5 up to cell G5, and what I would want is that cell D5 gets the value of cell A2, E5=A3, F5=A4 and G5=A5. This does not happen. Instead, E5 gets the formula "=B1" etc.
So it seems to only work when you are doing it parallel. If I want to do this perpendicularly (from a column to a row) it does not work. That's what I meant. Is there a way around this?

4. ## Re: Continuing the pattern of a formula to adjacent cells

Originally Posted by SilenTScopE
Sorry, instead of C5 to C15, I meant from C5 to G15. So yes, it works if I do it from C5 to C15.
So let's say Cell A1 has a value of 2. A2=3, A3=4, A4=5, A5=6
Now in cell C5, I put the formula "=A1" and I get a value of 2. That's great.
Now I drag the "fill handle" of cell C5 up to cell G5, and what I would want is that cell D5 gets the value of cell A2, E5=A3, F5=A4 and G5=A5. This does not happen. Instead, E5 gets the formula "=B1" etc.
So it seems to only work when you are doing it parallel. If I want to do this perpendicularly (from a column to a row) it does not work. That's what I meant. Is there a way around this?
Yes, the Fill Handle only works 'parallel' when using such a simple formula like your example. By the way, it has always worked like that so it is nothing to do with Excel 2007.

However, by using an alternative formula, you can achieve what you want. Put the formula below in C5 and drag the Fill Handle across.

Transpose

 A B C D E F G 1 2 2 3 3 4 4 5 5 6 2 3 4 5 6

 Cell Formula C5 =INDEX(\$A\$1:\$A\$10,COLUMNS(\$C5:C5))

Excel tables to the web >> Excel Jeanie HTML 4

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•