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. 

dataset with sales

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.
select cell b1
  1. 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. 
  2. Release the left mouse button but still hold down the Control key. 
hold control and then select multiple columns
  1. Left-click cell H1, hold down the left mouse button and drag down the column to cell H9. 
  2. 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.

non adjacent cells 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.

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. 

dataset with sales

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.
Extend Selection on the status bar

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

  1. Press the down arrow key several times to move down the column and extend the selection to cell B9.
select column using arrow
  1. 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:
Add or Remove selection mode
  1. Use the arrow keys to move the cell selector to cell D1.
  2. Press F8 to engage the Extend Selection mode again. Notice that the words “Extend Selection” reappear on the status bar.
  3. Press the down arrow key to move down the column and extend the selection to cell D9.
non adjacent cells selected
  1. 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. 
  2. Use the arrow keys to move the cell selector to cell H1.
  3. Press F8 to re-engage the Extend Selection mode.
  4. Use the down arrow key to extend the selection to cell H9.
Use arrow key to extend the selection
  1. 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.

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.

Name Box

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

dataset with sales

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.
click on the name box
  1. Type the named cell ranges in the Name Box, separating them with commas
Enter the names of the cells that you want to select

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.

Extending the name box
  1. Press Enter.

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

non adjacent columns are selected

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.

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.

dataset with sales

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:
Find and replace dialog box
  1. Press and hold down the Control key and press the “A” key to select all the cells that were found.
all cells that are found are selected
  1. 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:

non adjacent cells are selected

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.

dataset with sales

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.
Enter the cell reference in the Go To dialog box
  1. Click OK.

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

non adjacent cells 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: