Selecting Ranges in Excel
Have you ever had an issue using your mouse to select a large set of data in your Excel spreadsheet? Have you ever had similar issues using your mouse to select non-contiguous ranges and you have to start over when you click on the wrong cell?
This article will show you an alternate technique that may save you time and headaches when using your mouse to select ranges. Also, there is a bonus tip about using ranges that I demonstrate in the video.
Selecting Ranges Tips and Tricks
- Use your mouse to click and drag to select a range. This is the slowest way and can be tricky if your mouse is set to a very sensitive setting. I almost never use this technique except for very small ranges.
- Use your mouse in combination with the CTRL and/or Shift keys. You can leave your active cell in one area and then scroll with your mouse controls to an area you want to include.
- Using the keyboard: How-to Quickly Select a MASSIVE range in an Excel Spreadsheet – This is a previous video that demonstrates how to use the keyboard to select your range. I think that it is the quickest way and I am the most comfortable with this option.
- Use the Go To Dialog Box (Press F5 on your keyboard) – This is a great technique to use versus the keyboard technique described above. Especially if you have many breaks in your cell data as it will take more time with the keyboard to jump across the range. This technique also allows you to select a non-contiguous range. This is my new favorite. The only con to using this technique is knowing the exact range you want, but that is minor if you combine this technique with the Keyboard technique in the link above.
Using the Go To Dialog Box (F5)
Selecting Continuous Ranges
This is a very simple technique to select very large ranges in Excel and save your hand from the carpal tunnel syndrome. From your spreadsheet, press the F5 button and you will then see the Go To dialog box. Then simply type in the range you want to select with the top left-most cell address separated by a colon and then the bottom right cell address (i.e. A1:Z99999) then press the OK button.
Selecting Non-Continuous Ranges
You can also use the Go To dialog box to select non-contiguous ranges in Excel. Like before, in your spreadsheet, press your F5 button on the keyboard to see Go To dialog box. Then type in the non-contiguous ranges you want to select with the top left cell address separated by a colon and then the bottom right cell address of each range separated and each range should be separated by a comma (i.e. C7:D9,C25:E6000,A1:Z1) then press the OK button.
For the range of D6:E7,A2:C5,B9:D13
Your selected range will look like this:
That is a great way to select really large non-contiguous ranges like these 3 columns: A:A,E:E,Z:Z
More About Using Ranges
One thing that people don’t always realize that after you highlight any range, you can use the TAB button on your keyboard to navigate to the next cell (Right or Down based on your selection or current position in the range) for data entry purposes or to make the next cell the active cell.
You can also use SHIFT+TAB keys to navigate to the previous cell (Left or Up based on your selection based on your current position in the range).
Why do I have to know this? Mainly because once you have selected a range (especially very large ranges) you may want to go to the top or the bottom of the selected range without losing the range selection. After selecting a range with you active cell you will be at the bottom of the range, so to jump to the top without losing the selection, press your TAB key. If you use the F5 technique, your active cell will be the top left cell. So to get to the bottom of the selected range, press SHIFT+TAB. You can see this work in the video below.
Video Demonstration
Check out this short video that demonstrates this technique:
Other Range Tips and Tricks
Do you have any other tips or tricks about selecting and using ranges in Microsoft Excel? Put them in the comments down below.
Steve=True
