Forum Discussion
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
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.
- 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
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.
