Image source: Unsplash.com/clem-onojeghuoI often hear from my students that they take a long and frustrating time to select the information they need in large spreadsheets. They use the mouse to click hold and drag to the extremities but find that the selection area runs away to oblivion as soon as they go further that what they see on the screen when they start. How many columns does your spreadsheet have? 17,000 or so. How many rows? 1,500,000 almost. So, it’s no wonder that the mouse gets out of control. I would like to show you a neat way to select what you require with ease and control.

Here is a sample sheet. I know that it continues a fair way down the page but I have no idea how many rows it covers. Sample data

I wish to select just the surnames which are in the column “B”.

  • Select the first cell. “B4”.
  • Hold down your SHIFT key and do not let go until the end of this procedure.
  • Select the End key which is found in-between the qwerty keyboard and the number pad on your extended keyboard.
  • Look down in the left most corner of your window on the Task bar there you will find next to the word “Ready” the word “End Mode”. Sometimes people press the key for too long and this does not show as it is a toggle setting. Simply press the key again to set the “End Mode” on.
  • Select the Down Arrow key. This will take you to the bottom of the area in the column you started from. Not the whole column just up to the last cell with data in it.
  • Let go of the SHIFT key.

All the cells from the top to the bottom of the data have been selected with ease and control. You can now do whatever you need to this data.

Wasn’t that easy? Try using the same sort of controls to select across a block of data.

REMEMBER

SHIFT does the highlighting so keep it down until you reach your destination.

End Key sets the END Mode on.

Arrow keys take you in the direction you require.

To select the whole block of data you could start in cell A3. Holding down the SHIFT key then pressing End then Down arrow, then End and Right arrow. The whole data is selected no matter how deep or wide it is as long as there are no columns or rows with no data in them along the way.Whole dataYou may have needed to do this to change the formatting on the data or copy it to another sheet or file. whatever the reason you can now do it with more control and ease.

For more tips on using the keyboard or general shortcut keys read the following.

Tips – Selecting with the keyboard in Excel

Tips – Using Shortcut keys in Excel – General shortcuts

LEAVE A REPLY

Please enter your comment!
Please enter your name here