(Note: This guide on how to select non adjacent cells in Excel is suitable for all Excel versions including Office 365)
In Excel, we need to select cells to perform certain operations or to use functions. In those cases, we either select whole rows, columns, individual cells, or a group of cells.
There are a variety of methods to select adjacent cells. You can either select them by clicking and dragging, or by selecting adjacent cells by holding the Shift Key. But, selecting cells using these methods only selects the adjacent cells. What would you do if the cells are far from each other and you need to select them?
In this article, I will show you 5 ways to select non adjacent cells in Excel.
Selecting multiple cells that are not next to each other in Excel is easy once you know the simple keyboard tricks. Whether you need to operate on scattered data or make selections from different parts of a sheet, mastering non-adjacent selection expands your abilities. In this guide, I’ll show you the quick ways to select non-contiguous cells and ranges in Excel.
The Basics of Selecting Cells in Excel
Before learning about non-adjacent selection let’s review the basics
-
To select a single cell, click on it.
-
To select a range of adjacent cells click on the first cell hold down the left mouse button, and drag to extend the selection.
-
You can also select a range using the keyboard. Click on the first cell, then hold Shift and use the arrow keys to extend the selection.
These methods only allow selecting contiguous blocks of cells. But you can also make non-contiguous selections with a simple trick.
How to Select Non-Adjacent Cells
To select multiple non-adjacent cells or cell ranges:
-
Click on the first cell or range you want to select.
-
Hold the Ctrl key and click on each additional cell or range you want to add to the selection.
That’s all there is to it! The key is holding Ctrl while you make additional selections. Excel will keep adding them to the selection.
Some examples:
-
Click A1, Ctrl + click B2, Ctrl + click C3 to select 3 non-adjacent cells.
-
Click A1:B2, Ctrl + click C3:D4 to select 2 separate ranges.
Excel provides visual feedback when making non-adjacent selections. The dotted border will expand to enclose all selected cells.
![GIF showing selecting multiple non-adjacent cells in Excel]
This trick works for any kind of selection:
- Individual cells
- Entire rows or columns
- Standard ranges
- Large multi-cell ranges
You can use it to select non-adjacent cells on the same sheet or across multiple sheets in a workbook.
Keyboard Shortcut to Select Non-Adjacent Cells
Here is a keyboard-only method for non-adjacent selection:
-
Select the first cell or range normally.
-
Hold the Ctrl key.
-
While holding Ctrl, use the arrow keys to extend the selection to additional cells/ranges.
For example:
-
Click cell A1.
-
Hold Ctrl and press the Down arrow 3 times to add cells A4, A5, and A6 to the selection.
-
Still holding Ctrl, press Right arrow twice to also select C1.
Again, Ctrl is the key that lets you make multiple selections at once.
Why Select Non-Adjacent Cells in Excel?
Why would you need to select non-contiguous cells and ranges? Here are some examples:
-
Applying formatting: Make cells stand out by applying formatting only to certain scattered selections.
-
Adding chart data: Build a chart using data from different parts of the sheet or workbook.
-
Copying non-adjacent ranges: Collect and copy various bits of data to a new consolidated range.
-
Calculations: Calculate metrics or summaries based on totals from different locations.
-
Data validation: Set up validation that allows only certain cell groups.
-
Filters: Filter pivot tables or ranges to show sparsely related cells.
Mastering non-adjacent selection greatly expands your ability to work with data in Excel. It enables you to operate on specific cell groups, even if they are not side by side.
Tips for Selecting Non-Adjacent Cells
Here are a few pointers for effective non-adjacent selection in Excel:
-
Use the Go To command (Ctrl + G) to quickly select specific scattered cells.
-
Add ranges together by selecting the first, holding Shift to select the second, then Ctrl + click for additional ranges.
-
View the selection on the formula bar to confirm you have the correct cells.
-
Start by selecting entire columns or rows before adding individual cells.
-
On a touchscreen, hold your finger on the first cell, then tap additional cells to add them to the selection.
A bit of practice makes non-adjacent selection easy and intuitive in Excel. It’s a versatile technique for wrangling scattered data.
Selecting non-adjacent cells in Excel is simple with the Ctrl key or Ctrl + arrow keys. Click the first cell, hold Ctrl, then click or arrow key to more cells. This allows operating on scattered ranges across one or more sheets. Use non-adjacent selection when you need to work with disjointed data in Excel.
With this easy trick, you can format, copy, calculate, validate, and analyze data from anywhere in your workbook. Non-adjacent selection is a fundamental skill that unlocks the full power and flexibility of Excel.
Using the Ctrl Key
Holding the Ctrl key and selecting the cells is one of the best ways to select non adjacent cells in Excel. This approach is pretty straightforward.
To select non-adjacent cells, first, select a cell. Once you click on it, this becomes the active cell. In this example, we have selected the cell E5, so it becomes the active cell.
By pressing and holding the Ctrl key, select the cells you want to select. You can either click on each cell or use the mouse and drag to select a range of cells. Once you have selected all the necessary cells, leave the Ctrl key.
Using the Function Keys
In some cases, you might have to use only the keyboard and perform certain operations. When confronted with that situation, you can use the function keys to select the non-adjacent cells.
To select the non-adjacent using function keys, click on any cell to act as an active cell. In this case, we’ll consider C5 as the active cell.
Now, press the function key F8. This enables the Extended Selection mode. You can see the Extended Selection mode enabled from the bottom-left of the spreadsheet near the Accessibility status.
Since the sheet is in Extended Selection mode, you can use the arrow keys to select the adjacent cells. If you don’t want to select any adjacent cells, you can just skip to the next step.
To select the non-adjacent cells, press Shift+F8. This, in turn, enables the Add or Remove Selection mode.
Now, use the arrow keys to move the selection and select the cells you want. In this case, we move to cell J10 using the arrow keys.
Once you have moved to another non-adjacent cell, press F8 again to enable the Extended Selection mode and select the cell or group of cells.
After you have selected the non-adjacent cells, press F8 to get back to Normal mode.
Also Read:
Selecting Non-Adjacent Cells in Excel
How to select non-adjacent cells in Excel?
The easiest and most efficient way to select non-adjacent cells or cell ranges in Excel is to use your mouse and keyboard. Hold down the Ctrl (PC) or ⌘ Cmd (Mac) key. If you hold the Ctrl key (on Windows) or the Cmd key (on Mac), you can select non-adjacent cells. In contrast, holding down Shift only lets you select a range.
How to select adjacent cells in Excel?
Hold the Shift key and press the F8 key if you want to move to the cells you want to select. Press the F8 key and use the arrow keys to select adjacent cells. This may require your careful attention. But with constant practice, you’ll get the hang of it. You’ll be selecting non-adjacent cells with the use of your keyboard like a PRO
How do I select non-adjacent rows or columns in Excel?
To select non-adjacent rows or columns, hold Ctrl and select the row or column numbers. To select the entire worksheet, click the Select All button at the top left corner. Note: In some cases, selecting a cell may result in the selection of multiple adjacent cells as well.
How do I select a group of cells adjacent to each other?
To quickly select a group of cells that are adjacent to each other, you can utilize the Shift key. Here’s how: Click on the cell from where you want to start the selection. Hold down the Shift key. Click on the cell where you want the selection to end.
