22. Excel Tips- Find the last cell for non contiguous range
So, you have data in column 1, and you want to create a formula in column 2, and copy it down.
Normally, this is a very easy task:
- enter the formlula, select the small square in the bottom right corner of your cell, and double click on it to automatically populate your 2nd column with the formula.
However, what happens when the data looks like this?
The autofill will stop on line 3.
One way to complete the task is to drag down manually, but there is a much better way.
Step1: (for this example)
Select cell b2, and copy it
Press CTRL + END
This will take you to the bottom right
Press CTRL + LEFT ARROW
This will take you to the bottom left
Press CTRL + UP ARROW
This will take you to the bottom left of populated data
Press right arrow once, then CTRL + SHIFT UP ARROW
This will select the correct area to paste into
Once you get quick at shortcuts, you can do this entire action in a few seconds.
Video Training on finding the last cell: