Find end of data ranges in Excel fast

This is a great tip and I’m often surprised at how many Excel users still don’t know this – here is how you can save lots of time when navigating around a dataset in an Excel workbook…




Navigation Problems

There are plenty of occasions in Excel when you need to move the cursor or active cell around a large dataset or table.  Many of us will be familiar with trying to sum large ranges of data that scroll off the page and when you try and do this using the standard “dragging” of the cursor it can be a huge pain.  Excel works pretty fast but with datasets that run into thousands of rows or hundreds of columns dragging the cursor an take a long time before you get to the end.

The Solution

The solution is simple but its amazing how many Excel users are unaware of it.  From your starting point in the worksheet it is easy to move to the end of the range by just using the CTRL key and the directional arrows on your keyboard (the ones pointing up, down, left and right).

Imagine if you had a worksheet where you are trying to see the end of the dataset that goes off the screen:

You might find yourself using the mouse to drag along the whole range…it could take a little while!

So instead just select the starting point (Cell B4 in the above example) and press and hold down the CTRL key, then press the “Right” directional arrow, you will see the cursor moves right to the end of the data range:

That is very useful but we can add in another trick which will help when using formula.  In order to sum the data in all the columns quickly we can select cell A4 (where the total should go), type the formula = SUM( then select the starting cell, in this case B4 so your formula now looks like =SUM(B4 now press and hold the CTRL key, the SHIFT Key and then the right directional arrow, the sum formula will now include the entire range so all you need to do is close the brackets:

Then with the bracket closed hit the ENTER key and the formula is complete:

Summary

This quick tip helps you navigate around a dataset quickly.  In order to move around a data set from your starting point just hold the CTRL key and press the directional arrow that represents the way you want to move.

When using to cover a range in formula start the formula as normal and select the starting point, then hold down the CTRL and the SHIFT keys together before pressing the direction arrow that represents which direction your range is flowing.

Keep Excelling,

Leave a Comment