Shortcut in Excel

mustish1
mustish1 used Ask the Experts™
on
What is the shortcut to copy anything from one cell to the next 200 rows in the same column? I use shift + Page down but it takes me a while. Is there a way I can do it just in one click?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
No, you'll need several clicks. First, select the last cell (that you want to copy) and the next 200 cells down. Then from the Home tab, select Fill > Down. Alternatively, use the keyboard shortcut Ctrl+D.
Consultant
Commented:
Hi mustish1,

As mentioned above, there is no 'single click' way to do that, since there is no way for Excel to know that you want to fill down 200 cells, rather than 100 or 500 or whatever.

This is what I usually do (I'll assume your original cell to be copied is A1, and you want to copy that down to A2:A201):

Copy A1 (Ctrl-C
GoTo A201 (F5, A201, Enter)
Select A201 to A1 (Hold down Shift + Ctrl + UpArrow)
Paste (Ctrl-V)

Open in new window

This assumes all the cells below the source cell down to the end of the range you want to paste to are all empty.

I never use the mouse in Excel unless I have to - it just kills productivity.

Alan.
Roy CoxGroup Finance Manager
Commented:
Whats in the adjacent cells? If they contain data in a table format down to the 200th Row then hover the cursor over the bottom left corner of the first cell to copy and double click. The contents will copy down to the end of the data
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018
Commented:
You can use named ranges if you are working with the same ranges frequently (click on the image if gif don't play automatically)
NamedRange.gif
Rob HensonFinance Analyst
Commented:
@Roy - Bottom right, not left.
Rob HensonFinance Analyst
Commented:
As per Alan's suggestion the F5 key can be used to take the cursor to a specific cell or range but the method below will reduce the number of key strokes.

If you are in A1 and know that you need to select from A1 to A201. With the cursor in A1, press F5 and type A201 in the range box at the bottom of the pop-up window. Before pressing Enter or Clicking OK, press and hold the Shift key and then press Enter; A1 to A201 will then be selected.

Author

Commented:
Thank You
Roy CoxGroup Finance Manager

Commented:
Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial