How to paste down to a certain cell without going all the way down to the end of spreadsheet?

[Nina Yang]

Hi,

I have this excel question which has been asked my lots of my friends also.

If I want to drag the formula down from cell A1, how can I just drag/copy it down to a certain range- like A200, without going all the way down to the last cell of the spreadsheet?

I know I can just type sth on A200, so when i enter Ctrl+Shift+arrow down, it would only reach cell A200.

However, is there any quick way to do it???

 

[Khalid NGO]

Hi Nina and welcome to the forum Chandoo.org...

Besides all other short/alternate ways, I usually convert my data into Table, so that I doesn't need to drag formulas.

Regards,

 

[Hui]

Nina

Firstly, Welcome to the Chandoo.org Forums

Hide all the rows below the bottom Row
same with Columns, Hide all the columns to the right of the last column

Double clicking the cell handle

Will copy the value or formula down to the bottom of the adjacent Column
In this example it will copy "new data" down to E10

 

[Nina Yang]

Hi Hui,

Thanks for the answer, it is quite handy.

But i guess the shortage of this method is that if I have uneven lengths of data in one spreadsheet, I would have to hide other data also.

Cheers,
Nina

Nina

Firstly, Welcome to the Chandoo.org Forums

Hide all the rows below the bottom Row
same with Columns, Hide all the columns to the right of the last column

Double clicking the cell handle
View attachment 18591
Will copy the value or formula down to the bottom of the adjacent Column
In this example it will copy "new data" down to E10

 

[Simayan]

Copy formula from the cell.
then go to adjacent left column
Press Ctrl + down arrow
you will reach the last cell which contains data.
Press right arrow now you are in the column where data need to be pasted.
Press Ctrl+Shift+Up arrow
Paste value as formulla

 

[Nina Yang]

But then it only works if there is data in the parallel cell?

Copy formula from the cell.
then go to adjacent left column
Press Ctrl + down arrow
you will reach the last cell which contains data.
Press right arrow now you are in the column where data need to be pasted.
Press Ctrl+Shift+Up arrow
Paste value as formulla

 

[Hui]

Nina

At the end of the day there must be some way to define the bottom of the data area

It is either a preset Row, where as I suggested you can hide all the rows below

or

Existing data where you can do as Simayan suggested or double click the handle as I suggested

 

[Aby]

Hi Nina

Please let me know which is the column that decides the last row# of data?

 

[Nina Yang]

Hi Nina

Please let me know which is the column that decides the last row# of data?

Hi Aby,

In this case, all the coloumns have the same length of data - even length. So the solution proposed by Hui and Khalid are both feasible. However, if it is of huge amount of rows with uneven data from other coloumns/the coloum on the left/right to it, Simayan's proposed solution won't be optimal.

I really like the table solution as it really makes it easier to format the data.

Cheers,
Nina

 

[Hui]

Nina
Double clicking on the small black square at the lower right of a cell will copy/paste down according to the following rules

• Data on the left only, It will copy/paste down to the extent of the data on the left
• Data on the right only, It will copy/paste down to the extent of the data on the right
• Data on the left+right, It will copy/paste down to the extent of the largest set of