In order to copy a cell down in Excel, there is a nifty shortcut that will only copy down as far as the last active cell it sees (so it doesn’t go all the way to row 1 000 000). However, it is not as easy if you want to copy from left to right.
To copy from top to bottom, you can simply hover your mouse over the bottom right corner (like you do if you want to copy it), but you can double click and Excel will only go as far as the last active row (row 33 in the image below).
YouTube Copy from left to right but stop
Can you copy from left to right in Excel in the same way?
Simple answer is no. Which means you either need to manually drag the copy across or come up with other ways to do it.
This is where some shortcuts come in handy. To see how they work watch the video clip on navigating spreadsheet cells and read on below.
In the example below we want to (easily) copy the formula in D4 from left to right.
If you copy the cell and use CTRL + → (right arrow) it will jump all the way to the end of the spreadsheet (column XFD or a jump of 16384 columns). Similarly if you use the CTRL + END + → it will jump right to the end of the spreadsheet.
A couple of ways we copy cells in Excel from left to right.
Use a similar row to find the end and work backwards
In the above example, we have already set up the correct number of columns in rows 1,2 and 3. If you copy cell D4, then
- go to say cell D3, click CTRL + →, which will stop at the end of the active cells in row 3.
- Go one cell down and
- click CTRL + SHIFT + ← which will jump all the way back to D4 and highlight everything it jumps over.
- You can now paste the cell and you have fairly quickly copied from left to right in excel.
Create a border to stop your cell jump
If you are going to be regularly copying from left to right, it may be useful to create a border into which you will bump when you use one of these shortcuts.
WARNING: Don’t think you achieve the same thing by hiding all the columns after your active area. It often results in you having 16384 hidden formula.
As shown below in column AC we have just created a column of X’s.
Now when we go to cell D4, we can:
- Copy it
- Hit CTRL + SHIFT + → which will jump and highlight all the cell till AC (including the X)
- Release CTRL and, while holding SHIFT down click the left arrow twice (this moves the highlighted area back two cells)
- Paste the formula.
Try out our free Excel Tips and Tricks course to learn some other Excel shortcuts and tricks.
Related
Copy a cell to the left in Excel
Double click Excel cell to follow formula not working
Quick cell navigation and highlights blocks of data
Viewing multiple worksheets (in the same spreadsheet) at the same time