How to Select Non-adjacent Cells in Excel?

To perform some operations in Excel requires that we first select cells.

Selecting adjacent cells is easy. While selecting non-adjacent cells is not as straightforward, with a bit of practice, you will be able to do it with ease.

This tutorial covers five simple ways you can use to select non-adjacent cells in Excel. 

Method #1: Select Non-adjacent Cells Using a Combination of Keyboard and Mouse Maneuvers

The easiest way to select non-adjacent cells in Excel is to use a combination of keyboard and mouse maneuvers.

Suppose we have the following dataset showing the tablet sales of a particular online store. 

Using the keyboard and mouse, we want to select the non-adjacent cell ranges containing the January, March, and June sales.

Below are the steps to do this:

1. Select cell B1, hold down the left mouse button, drag down the column to cell B9 and release the left mouse button.

2. Press and hold down the Contol key, left-click cell D1, hold down the left mouse button and drag down the column to cell D9. 
3. Release the left mouse button but still hold down the Control key. 

4. Left-click cell H1, hold down the left mouse button and drag down the column to cell H9. 
5. Release the left mouse button and release the Control key.

The non-adjacent cell ranges containing the January, March, and June sales are now selected.

When you hold the Control key and then make a selection in Excel, it understands that you want to keep the previous selection and add the new selection to it.

Also read: How to Select Visible Cells Only in Excel?

Method #2: Select Non-adjacent Cells Using the Keyboard Only

Most people would prefer using the previous method, but if you are a keyboard ninja, you would fancy using the keyboard only in selecting non-adjacent cells. 

We have received the following dataset showing the tablet sales of a particular online store. 

We would like to select the non-adjacent cell ranges containing the January, March, and June sales using the keyboard only.

We use the following steps:

1. Use the arrow keys to move the cell selector to cell B1. 
2. Press key F8 to enter the Extend Selection mode. Notice the words “Extend Selection” on the status bar at the bottom of the screen.

Note: The Extend Selection mode means that the current selection will be expanded to include newly selected cell ranges. 

3. Press the down arrow key several times to move down the column and extend the selection to cell B9.

4. Press and hold down the Shift key and press F8 and then release the Shift key. This disengages the Extend Selection mode and engages the Add or Remove selection mode. Notice the words “Add or Remove selection” on the status bar at the bottom of the screen:

5. Use the arrow keys to move the cell selector to cell D1.
6. Press F8 to engage the Extend Selection mode again. Notice that the words “Extend Selection” reappear on the status bar.
7. Press the down arrow key to move down the column and extend the selection to cell D9.

8. Press and hold down the Shift key and press F8 and then release the Shift key. Notice that the words “Add or Remove Selection” reappear on the status bar. 
9. Use the arrow keys to move the cell selector to cell H1.
10. Press F8 to re-engage the Extend Selection mode.
11. Use the down arrow key to extend the selection to cell H9.

12. Press F8 to come out of the Extend Selection mode.

Personally, I find this method a bit tedious, especially if you want to select multiple non-contiguous cells or ranges. But if you only want to select a few non-adjacent cells or ranges, this method can be good as it allows you to stay on your keyboard and not use the mouse/trackpad.

Also read: How to Select Every Other Row (Alternate Row) in Excel?

Method #3: Select Non-adjacent Cells Using the Name Box

We can use the Name Box on the leftmost end of the formula bar to select non-adjacent cell ranges.

We will use the following dataset that has named ranges to show how this can be done. 

Note: Range B1:B9 is named Jan_Sales, range D1:D9 is named March_Sales, and range H1:H9 is called June_Sales.

To select all the named ranges in the dataset at once we use the following steps:

1. Click in the Name Box and delete whatever is there and only the cursor remains blinking.

2. Type the named cell ranges in the Name Box, separating them with commas

Note: You can adjust the size of the Name Box accordingly by clicking the three vertical ellipses (dots) on the right of the Name Box and dragging them to the left or right.

3. Press Enter.

The three non-adjacent named ranges are selected at once:

In the above example, I named my columns and then used those names in the Name Box. You can also use the ranges if you want. For example, you can also enter B1:B10,D1:D10,H1:H10, and it will immediately select all the specified ranges.

Similarly, you can also enter the cell references to select multiple cells as well.

This method could be helpful when you want to select non-adjacent cells or ranges that are far from each other. All you need to do is enter the name or cell reference in the name box, and it will instantly select those cells. This would be a lot faster than doing it manually using the mouse and keyboard.

Also read: How to Delete Multiple Rows in Excel?

Method #4: Select Non-adjacent Cells Containing a Particular Value Using Find and Replace Feature

We can use the Find and Replace feature in Excel to select non-adjacent cells that contain a particular value. 

We have the following dataset containing the “Microsoft Surface Pro 8” in cell A2 and cell A9.

We can select those two cells using the Find and Replace feature using the steps below:

1. Select the entire dataset.
2. Press and hold down the Control key and press F to open the Find and Replace dialog box. 
3. In the Find and Replace dialog box that appears, type the name Microsoft Surface Pro 8 in the Find what box and click the Find All button:

4. Press and hold down the Control key and press the “A” key to select all the cells that were found.

5. Click the Close button to close the Find and Replace dialog box.

The two non-adjacent cells that contain the value “Microsoft Surface Pro 8” are now selected:

Also read: How to Swap Cells in Excel

Method #5: Select Non-adjacent Cells Using the Go To Feature

We can use the Go To dialog box to select non-adjacent cells in Excel.

We have the following dataset containing the “Microsoft Surface Pro 8” in cell A2 and cell A9.

We can use Go To feature  to select the two cells using the following steps:

1. Press and hold down the Control key and press the “G” key to open the Go To dialog box.
2. Enter the cell references A2 and A9, separated by commas, in the Reference box.

3. Click OK.

The two cells with the “Microsoft Surface Pro” value are selected.

You can also use the Go To method when you need to select multiple non-adjacent ranges.

This tutorial explains five techniques we can use to select non-adjacent cells in Excel.

The methods are using keyboard and mouse maneuvres, using the keyboard only, using the Go To dialog box, using the Name Box, and using the Find and Replace feature.

We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Select Rows with Specific Text in Excel
• Select Row (or Rows) in Excel (Shortcut)
• How to Select Multiple Rows in Excel (3 Easy Ways)
• How to Select Alternate Columns in Excel (or every Nth Column)
• How to Select Every Other Cell in Excel (Or Every Nth Cell)
• How to Group Rows in Excel?
• Shortcut to Select Adjacent Worksheets in Excel