Repeating the cell values

Copper Contributor

HI,

 

I tried to fill the Column A by dragging the cells. However, I have more than 7000 rows. Please advice how can I repeat the Column A values without dragging the cells down

 

Column AColumn  BColumn  CColumn  D
X11C1D1
 2C2D2
 3C3D3
 5C4D4
 6C5D5
 7C6D6
 8C7D7
 13C8D8
 15C9D9
 16C10D10
 17C11D11
X21C1D1
 2C2D2
 3C3D3
 5C4D4
 6C5D5
 7C6D6
X31C1D1
 2C2D2
 3C3D3
 5C4D4
 6C5D5
 7C6D6
 8C7D7

 

Thanks

4 Replies

@Chris525 Let's assume that "X1" is in cell A1.

1) Select A1 to A7000

2) F5, Special..., Blanks

 

then it looks like this:

Riny_van_Eekelen_0-1715579327680.png

3) With A2 as the Active cell, enter =A1 and conform with Ctrl-Enter

 

This fills the empty cells with formulas referencing the 'cell above'. Copy A1:A7000 and past back as values to get rid of the formulas.

 

@Chris525 

 

Hi,

 

You can try to double-click the bottom-right corner of the cell where X1 is. That will automatically fill down until X2. However, by default, it will try to fill series. That is, fill like: X1, X2, X3 and so on. What you want to do is:

 

1) Double-click on the bottom-right corner of the selected cell:

Martin_Angosto_0-1715579304269.png

 

2) Once filled, go to the fill options and select "Copy Cells" instead of "Fill Series":

Martin_Angosto_1-1715579375123.png

 

Do the same procedure for all X's.

 

If by any chance you cannot click on the bottom-right corner of the cell go to File > Options > Advanced > Make sure the option "Enable fill handle and cell drag-and-drop" is selected.

 

Hi,

Thank you for the quick reply.

Sorry I couldn't understand the step 2 and 3.

Thanks
HI Riny,

I understood the steps now. Its working for me. Thank you so much.

Another question: Lets say my cell A2 has values (Is not blank). How can I modify the step 2?

Column A
X1
1
2
3
5
6
7
8
13
15
16
17
X2
1
2
3
5
6
7

Thanks