One of the things that I still find funny is when I ask a user to select all of the values in a column (not the column itself) and then user starts from the top and drags the selection until they reach the end. Of course, for a couple hundred rows, this doesn’t take too long, but if you have thousands or tens of thousands, this can take a while. Therefore a quick tip to select a large range of non-empty cells in a column, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Hold down CTRL (or if using a Mac COMMAND) and SHIFT on the keyboard and then press the down arrow key. This will select all of the cells in the range as long as there are no empty cells in the range.

If the range does contain empty cells, instead of dragging the selection from the top to the bottom, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Scroll down to the end of range (which is usually the end of the sheet) by using the scroll bar.
  3. Press SHIFT and then select the last cell in the range.

If you know the address of the last cell, you can do the following:

  1. Select the top-most cell in the desired range.
  2. Type the address of the final cell of the range into the address box and press SHIFT + ENTER.

If you know the addresses of both the top-left and the bottom-right cells, you can enter those address delimited by a colon into the address box and then press enter. For example, entering A2:D8192 into the address box will select the cells from A2 to D8192.

There are probably other cool tricks as well, so if you have any to share with the world, you can post them in a comment. If your’s is good enough, I will definitely expand this article to include it and then give you credit. 😉

Categories: BlogMicrosoft Office

8 Comments

Ricki Keeley · June 11, 2012 at 2:37 PM

Thanks for the help, this has saved me so much time! Keep up the good work!

Kind regards,
Ricki

Mel · July 18, 2012 at 8:57 AM

I always new there was a better way of selecting data, only took me a year to get off my butt and find out what that was ! Thanks for your descriptive yet simplified explanation, just cut and pasted a huge data set that would have taken me ages and annoyed the hell out of me! 🙂

John · October 26, 2015 at 11:12 AM

I quick way to select all your data. Ctrl and Home to Go to cell A1.
Hold Down the Shifh Key press End and Home

    Joseph Keffas · January 10, 2018 at 10:09 AM

    Nice! Also, you could just press Ctrl + Shift and End after you have selected the first cell using Ctrl Home.

Mickey · July 12, 2016 at 4:19 AM

Thanks for posting this! It has been incredibly helpful (some of my datasets are 23,000+ rows)!

Sulayman · June 21, 2017 at 5:14 AM

Thanks. Regularly have to do a 1800 line table. Was such a pain

    Johnny · June 27, 2019 at 2:26 PM

    This was incredibly helfpul

Dean Roger D'Mello · November 8, 2019 at 10:47 AM

Thank you, this was very helpful!

Leave a Reply to Ricki Keeley Cancel reply

Your email address will not be published. Required fields are marked *