Selecting a range of touching cells in Excel is a regular experience for any user. You may simply click and drag. There are no complications. But things become difficult when the requirement is to select several cells that are away from one another. Nevertheless, there is always a way. In this guide, we are going to discuss 5 quick methods for how to select non-adjacent cells in Excel.
An Excel user may need to select cells for many reasons. Highlighting, modifying, or deleting data requires you to select those cells first. When the cells are touching, they are easy to select, and then you can do whatever you need to do. But it is not as easy when other cells are in the way and you have to avoid them.
This is where being a sharp Excel user pays off. In this guide, you will find 5 methods that are suitable for specific situations. We are going to explain all these methods in steps. We will also attach pictures for each step during the explanation.
Let’s jump in!
5 Simple Methods for Selecting Non-Adjacent Cells in Excel
The 5 different techniques we demonstrate in this guide for you to select non-adjacent cells work uniquely in various situations. Hence, we suggest you try to learn all of them. To make your learning more effective, practice the methods as you read through the guide. Use our shared practice file if necessary.
Method 1: The Ctrl Key and Click Approach
The first method in our guide is the most straightforward approach you can think of. Even as a regular computer user, you have used this method to select multiple files and folders in your daily operation.
We are going to use the dataset shown below for our whole guide here. If you would like to use it to practice the methods, don’t hesitate to download the file from this guide.
Now let’s see how to do it.
1. Click on the first cell of your intended selection. For instance, in this demonstration, we are going to select all the cells that have “Beef” in them.
2. After selecting the first cell with your mouse click, press and hold the Ctrl button on your keyboard, and then click other cells one by one. Do not click on a cell that you don’t want to include in your selection.
You will see that the cells have been selected as you clicked on them. Release the Ctrl key when you are done.
Method 2: Exclusively Keyboard Approach
Method 1 is good enough when you need to select a few non-adjacent cells from a small dataset. However, if your dataset is big and you need to scroll through the document, that traditional process becomes error-prone.
To tackle this problem, you can use only the keyboard to select non-adjacent cells, as many as you like! This method is also a favorite among Excel users who do all of their work by using keyboards. In this case, less hand movement is needed. As a result, it ensures smooth and faster work.
Now let’s see the process below on how you can select non-adjacent cells in Excel with only your keyboard!
1. Click on or move to the first cell of your intended selection. In this demonstration, we are going to select all the cells with “Houston”.
2. Now press the Shift + F8 buttons on your keyboard. It will activate the mode to add or remove cells from your selection. You can see the status of the mode in the Status Bar of your Excel program (marked in the picture below).
3. Now move to the next cell that you want to select. Press the Shift + F8 buttons two times to include it in your selection.
Note: If you press Shift + F8 just once, it will deactivate the selection mode. The status bar will not show any Add or Remove Selection status. See the image below.
Therefore, pressing the Shift + F8 buttons two times in a row will first deactivate the selection mode but then, at the second press, it will reactivate the mode and include the cell in your selection.
4. Now move to the next cell that you want to select in the same process (press Shift + F8 twice). Keep an eye on the status bar to see if the mode is indeed active.
However, in our demonstration, we can see in the picture above that there are two adjacent cells that we needed to include in our selection. You can select your multiple adjacent cells one by one with the Shift + F8 buttons. Contrarily, if you press only F8 once when you need to include multiple adjacent cells in your selection, the mode changes to Extend Selection (as shown in the image below).
It means that you can select more than one cell now. Simply use the arrow keys to move toward the cells’ direction to include them in your selection.
5. Finally, press F8 (if you are in the Extend Selection mode) or Shift + F8 (if you are in the Add or Remove Selection mode) to get out of the selection mode. You will see that the status bar is not showing any selection mode currently, and you have selected all the non-adjacent cells you wanted to select!
We have shown the process in a small dataset. When you are working with larger data, you will realize how effortless this method is. You don’t need to hold your Ctrl button and click on cells while also scrolling through your document.
Method 3: The Go To Approach
The Go To option in Excel has so many wonderful uses. It also helps you select non-adjacent cells in Excel. And it is one of the best ways you can use to select your cells. This method also does not require you to use your mouse. You can do pretty much everything with your keyboard only.
For this technique, you need to use the Cell References to select your cells. Instead of going through all the cells to find the ones you need to select and click on them, you can simply input the cell names or references.
We are going to demonstrate the process by using the same dataset. Here, we will select all the cells that have “Doorstep” in them by using the Go To option in Excel.
Let’s see how to do it!
1. Under the Home ribbon in your Excel, find the Editing group and then click on the Find & Select button. From the drop-down menu, click on Go To…
Alternatively, you can simply press the F5 button on your keyboard to initiate the same action.
2. A mini Go To window will pop open. In the box under Reference, write the cell names or cell references. Separate cell names with commas. Finally, click OK.
For instance, in this demonstration, we wanted to select all the cells with “Doorstep” in them. So, we wrote those cells’ names under the Reference box and as the finishing step, we clicked the OK button.
3. As you press OK, the Go To window will close and you will find that the cells that have been referred to are now selected!
4. If you need to select cells from different columns, the process is the same. Just input cell names like below in the Reference box and click OK.
And you should find your named cells selected.
Pretty simple, isn’t it?
Method 4: The Name Box Approach
This method is a slight variation of the previous one. Here, we will show how to select non-adjacent cells in Excel by using the Name Box. It is faster and more direct than other methods.
If you don’t know what the Name Box is in Excel, let us show you where you can find it. It is located right below the menu ribbon and to the left of the Formula Bar. When you select a cell, the Name Box shows the name or reference of your selected cell. Hence, the Name Box.
See the picture below for a better understanding.
In Method 3, we used the Go To option to enter the cell names to select them. Here, we will directly input the cell names in the Name Box.
Let’s see the process below.
1. First, click on the Name Box. Then start writing the names of the cells that you want to select. When you are finished, hit Enter.
For instance, in the image above for our demonstration, we wanted to select some cells that are pointed to with arrows. We wrote those cells’ references in the Name Box.
2. After pressing the Enter button, you should see that the cell names that you entered in the Name Box are now selected.
And that’s it!
Method 5: The Find Approach
In the final method of this guide, we will discuss how you can use the Find option to select non-adjacent cells in Excel. When you need to select all the cells with a particular string of text, this is the method you would want to use to save time.
During the demonstration of this method, we will try to select all the cells with “Shop” text in them. See the picture below.
In other methods that we have discussed so far, you have to follow a bit of a lengthy process. If your worksheet has thousands of cells and you want to select many non-adjacent cells with a specific text, selecting those cells one by one or using the reference will take away too much of your time.
In such cases, use this method with the Find option and select all your intended cells in under 5 seconds! Follow the steps below.
1. Go to the Editing group under the Home ribbon. Click on Find & Select, and then from the drop-down menu, click Find. You may also use the shortcut Ctrl+F to open the Find option.
2. A Find and Replace window will open up. In the Find what box, you will need to write the text that is in your cells.
3. After writing the text in the Find what box, click the Find All button.
In this demonstration, we wanted to select all the cells with “Shop” in them. We wrote that word in the box and then clicked Find All.
4. The Find and Replace window will expand and you will see the result in the section down below. It will show the locations of all the cells that have your input word.
Click one of the contents in that list and then press the Ctrl+A buttons to select all of them.
5. After selecting the whole list, you will see that the cells with your input word are now highlighted and selected. Click Close.
6. The Find and Replace window will go away and you will see that all the cells with your input word are now selected!
How quick was that? We used a small dataset, but imagine doing the same thing when you have a worksheet with a hundred or more non-adjacent cells to select from.
With this method, you will save not only minutes but even hours of your time!
For selecting non-adjacent cells, an Excel user has to be quick and smart. And we have shown 5 smart methods in this guide for you. Using these techniques, you will be able to select any non-adjacent cells in your Excel worksheet.
Method 1 is the oldest and most traditional way of selecting non-adjacent cells. Method 2 shows how you can select so many different cells without having to use your mouse. It also allows you to select cells from a large dataset without too much effort.
Method 3 and Method 4 are favorable when you want to select cells by not clicking on them or moving to their locations with your keyboard. You simply enter the cells’ locations and select them all as a result. Finally, Method 5 is probably the most unconventional method for selecting cells. Yet, it is the one that will save so many seconds of your work and let you be stress-free.
We strongly recommend that you learn all the methods here as each of them serves differently. You can pick one from Method 3 and Method 4 as they are similar. Therefore, you need to learn only four methods in total that will serve you a lifetime.
Be sure to practice all the techniques a few times. Use our practice file if you don’t have a workbook to apply the methods. In less than an hour, you will find that you have successfully mastered everything from this guide!