Repeating the cell values

Copper Contributor

Repeating the cell values

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 A Column  B Column  C Column  D X1 1 C1 D1 2 C2 D2 3 C3 D3 5 C4 D4 6 C5 D5 7 C6 D6 8 C7 D7 13 C8 D8 15 C9 D9 16 C10 D10 17 C11 D11 X2 1 C1 D1 2 C2 D2 3 C3 D3 5 C4 D4 6 C5 D5 7 C6 D6 X3 1 C1 D1 2 C2 D2 3 C3 D3 5 C4 D4 6 C5 D5 7 C6 D6 8 C7 D7

Thanks

4 Replies

Re: Repeating the cell values

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

1) Select A1 to A7000

2) F5, Special..., Blanks

then it looks like this:

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.

Re: Repeating the cell values

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:

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

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.

Re: Repeating the cell values

Hi,

Thank you for the quick reply.

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

Thanks

Re: Repeating the cell values

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